Compute Average (Aggregate Function) with Null Value in Query (Part II

[Tamrak]

Hello Again,

Thank you for your response on the first post. This one is very similar.

Let's say, I have 5 fields, all numeric. Some of the fields contains Zero value and some contains Null value. How can I find a precise average of these 5 fields combined?

Let's say, the field names are: MonthA, MonthB, MonthC, MonthD and MonthE. The data are: $500, $0, null, $300 and $200, respectively. The correct average of these data should be $250. The result comes from (500+0+300+200) and divided by 4, not 5, because I do not count the null value.

Apparently, I tried to use an NZ function and it gave me different results. I might do something wrong in the formula.

The query is just a select query and does not being grouped. I would like to perform this calculation, one record at a time.

All of your helps will be very much appreciated. Thanks.

 

[BitTwiddler]

Here is one possible way. In a blank column in the query grid put:

Intermediate1: iif(isnull([MonthA]),0,[MonthA]) + iif(isnull([MonthB]),0,[MonthB]) + iif(isnull([MonthC]),0,[MonthC]) + iif(isnull([MonthD]),0,[MonthD]) + iif(isnull([MonthE]),0,[MonthE])

in another blank column put:

Intermediate2: iif(isnull([MonthA]),0,1) + iif(isnull([MonthB]),0,1) + iif(isnull([MonthC]),0,1) + iif(isnull([MonthD]),0,1) + iif(isnull([MonthE]),0,1)

in another blank column put:

YourColumnName: Intermediate1 / Intermediate2

I tested this and it worked...