434,984 Members | 2,686 Online
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

# Calculate the average from multiple text fields and ignore if value is not numeric

100+
P: 547
I have a tricky average calculation using multiple text fields and need to ignore non numeric values but include zero in avg calculation.
If fields are all Text format
Jan = 10
Feb = 0
Mar = 15
Apr = X
May = 15

The avg here is 10+0+15+15/4 = 10
We need to use dsum to add up the IsNumeric values and us DCount to divide by IsNumeric values
It should be doing the following but i cannot get it right. :
Expand|Select|Wrap|Line Numbers
1. Avg_value: ((DSum("[Jan]+[Feb]+[Mar]+[Apr]+May]","Table1","[Jan-May]>=0"))/(DCount("[Jan]+[Feb]+[Mar]+[Apr]+May]","Table1","[Jan-May]>=0")),2)
2.
I first tried to eliminate the non-numerics for all the fields (Jan1-May1) Apr will then be blank. I used ie
Expand|Select|Wrap|Line Numbers
1. Jan1: IIf(IsNumeric([quantity1])=True,[quantity1])
And then tried the avg on these 5 fields
Expand|Select|Wrap|Line Numbers
1. Calc: Avg(Nz([jan1])+Nz([feb1])+Nz([mar1]+Nz([apr1])+Nz([may1]),0))
Attached Files
 MonthlyAverage.zip (61.5 KB, 31 views)
Nov 30 '16 #1
6 Replies

 Expert 100+ P: 1,107 You might want to try something like this: Expand|Select|Wrap|Line Numbers AvgValue: (Val([Jan])+Val([Feb])+Val([Mar])+Val([Apr])+Val([May]))/5 If you need to completely remove non-numerics from the Average, you might want to structure things like this: Expand|Select|Wrap|Line Numbers SELECT    Table1.Jan , Table1.Feb , Table1.Mar , Table1.Apr , Table1.May , IIf([Divisor]>0,(Val([Jan])+Val([Feb])+Val([Mar])+Val([Apr])+Val([May]))/[Divisor],0) AS AvgValue , [IncludeJan]+[IncludeFeb]+[IncludeMar]+[IncludeApr]+[IncludeMay] AS Divisor , Abs(IsNumeric([Jan])) AS IncludeJan , Abs(IsNumeric([Feb])) AS IncludeFeb , Abs(IsNumeric([Mar])) AS IncludeMar , Abs(IsNumeric([Apr])) AS IncludeApr , Abs(IsNumeric([May])) AS IncludeMay FROM Table1 Nov 30 '16 #2

 Expert 100+ P: 1,430 I think this code is less restrictive. It ignores field names and doesn't care how many months you define. Expand|Select|Wrap|Line Numbers Option Compare Database Option Explicit   Function Average() As Currency       Dim MyDb As Database     Dim Rst As Recordset     Dim SQLStg As String     Dim Fld As Field     Dim Tot As Currency     Dim i As Integer       SQLStg = "SELECT Table1.* FROM Table1"       Set MyDb = CurrentDb     Set Rst = MyDb.OpenRecordset(SQLStg)       With Rst         For Each Fld In .Fields             If IsNumeric(Fld) Then                 Tot = Tot + Fld                 i = i + 1             End If         Next Fld         .Close         Set Rst = Nothing     End With       Average = Tot / i   End Function   Phil Nov 30 '16 #3

 100+ P: 547 Thx I will give it a try. The "X" is added for those months the stock is unobtainable from the supplier, and thats why i need to be able to remove it from the avg calculation, as it will skew the overall averages for the other months. Otherwise , it would be straightforward just to divide by the number of months. Nov 30 '16 #4

Expert 100+
P: 1,107
This problem would go away if you were to normalize your data: Database Normalization and Table Structures

If you were to make a Table with Date column for the Month and Numeric column for the Quantity, you could write a standard Query and everything will work as expected. I modified you sample database and attached it: https://bytes.com/attachment.php?att...1&d=1480530687
Attached Files
 MonthlyAverage.zip (56.0 KB, 30 views)
Nov 30 '16 #5

 100+ P: 547 Thx for advice JForbes.I agree thats the way i would have preferred to go, but the about 550 items here are populated onto a screen alphabetically from top to bottom, and the data is filled in from left to right, for ease of capturing as the users battle to find correct items if having to lookup by names.So i am extracting some of this info from left to right for different reports. Thx for prompt reply Nov 30 '16 #6

 100+ P: 547 JForbes, I got it working with the Sql added to the query. Thx for the trouble. You are a Star! Nov 30 '16 #7