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","[JanMay]>=0"))/(DCount("[Jan]+[Feb]+[Mar]+[Apr]+May]","Table1","[JanMay]>=0")),2)

I first tried to eliminate the nonnumerics for all the fields (Jan1May1) 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