By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,686 Online
Bytes IT Community
+ Ask a Question
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))
No success as it gives me a funny answer. Please advise
Attached Files
File Type: zip MonthlyAverage.zip (61.5 KB, 31 views)
Nov 30 '16 #1
Share this Question
Share on Google+
6 Replies


jforbes
Expert 100+
P: 1,107
You might want to try something like this:
Expand|Select|Wrap|Line Numbers
  1. 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
  1. SELECT 
  2.   Table1.Jan
  3. , Table1.Feb
  4. , Table1.Mar
  5. , Table1.Apr
  6. , Table1.May
  7. , IIf([Divisor]>0,(Val([Jan])+Val([Feb])+Val([Mar])+Val([Apr])+Val([May]))/[Divisor],0) AS AvgValue
  8. , [IncludeJan]+[IncludeFeb]+[IncludeMar]+[IncludeApr]+[IncludeMay] AS Divisor
  9. , Abs(IsNumeric([Jan])) AS IncludeJan
  10. , Abs(IsNumeric([Feb])) AS IncludeFeb
  11. , Abs(IsNumeric([Mar])) AS IncludeMar
  12. , Abs(IsNumeric([Apr])) AS IncludeApr
  13. , Abs(IsNumeric([May])) AS IncludeMay
  14. FROM Table1
Nov 30 '16 #2

PhilOfWalton
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
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function Average() As Currency
  5.  
  6.     Dim MyDb As Database
  7.     Dim Rst As Recordset
  8.     Dim SQLStg As String
  9.     Dim Fld As Field
  10.     Dim Tot As Currency
  11.     Dim i As Integer
  12.  
  13.     SQLStg = "SELECT Table1.* FROM Table1"
  14.  
  15.     Set MyDb = CurrentDb
  16.     Set Rst = MyDb.OpenRecordset(SQLStg)
  17.  
  18.     With Rst
  19.         For Each Fld In .Fields
  20.             If IsNumeric(Fld) Then
  21.                 Tot = Tot + Fld
  22.                 i = i + 1
  23.             End If
  24.         Next Fld
  25.         .Close
  26.         Set Rst = Nothing
  27.     End With
  28.  
  29.     Average = Tot / i
  30.  
  31. End Function
  32.  
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

jforbes
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
File Type: zip 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

Post your reply

Sign in to post your reply or Sign up for a free account.