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

[neelsfer]

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. :

Avg_value: ((DSum("[Jan]+[Feb]+[Mar]+[Apr]+May]","Table1","[Jan-May]>=0"))/(DCount("[Jan]+[Feb]+[Mar]+[Apr]+May]","Table1","[Jan-May]>=0")),2)
 

I first tried to eliminate the non-numerics for all the fields (Jan1-May1) Apr will then be blank. I used ie

Jan1: IIf(IsNumeric([quantity1])=True,[quantity1])

And then tried the avg on these 5 fields

Calc: Avg(Nz([jan1])+Nz([feb1])+Nz([mar1]+Nz([apr1])+Nz([may1]),0))

No success as it gives me a funny answer. Please advise

[jforbes]

You might want to try something like this:

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:

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

[PhilOfWalton]

I think this code is less restrictive. It ignores field names and doesn't care how many months you define.

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

[neelsfer]

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.

[jforbes]

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

[neelsfer]

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

[neelsfer]

JForbes, I got it working with the Sql added to the query. Thx for the trouble. You are a Star!