AVG (zero shall not included)

[mroks via AccessMonster.com]

i want to average the below data.

July - 98
August -85
Sept- 0
October- 80
November -75

How could i calculate it, i would like not to affect the result by zero.

The formula should turn like this. avg(98+85+80+75)3

thanks.

 

[Paul Shapiro]

Avg ignores nulls. So a function like avg(iif([yourFieldName]=0, null,
([yourFieldName]) should do what you want.

 

[Douglas J. Steele]

John and Paul have both told you how to do it correctly.

However, I have this nagging suspicion that your table is denormalized: that
you've got twelve fields named January, February, March and so on in it, and
that you're trying to create an average for each row, like you would in a
spreadsheet. (That's definitely not how it should be done in Access!)

If that's the case, you need to use the following ungainly calculation:

IIf([January] + [February] + [March] + ... + [December] = 0, 0, ([January] +
[February] + [March] + ... + [December]) / (IIf([January] = 0, 0, 1) +
IIf([February] = 0, 0, 1) + IIf([March] = 0, 0, 1) + ... + IIf([December] =
0, 0, 1)))