Finding the average of a series including nulls (2000)

Topic

New Reply

I have a database recordng the marks awarded to match officials by assessors for their control of a sports event. At the end of the season, I need to present these showing various averages. One of these is to show the average of the highest mark awarded by the group of assessors and also the average of the lowest mark ; thus, if I had 5 assessors whose highest marks were 9 – 9 – 8 – 7 – 7 respectively, the average would be 8 ; if their low marks were 4 – 4 – 5 – 6 – 6 the low average would be 5. With me so far ?

The problem arises when certain of the assessors have only covered 1 match. In such cases only a high mark is recorded, the low mark becoming a null.

I’ve worked out that I can take the total of the high marks and divide it by the number of assessors to get the high average but how do I get Access to calculate the low average (which may contain nulls) for me ?

This was previously done by someone else on an Excel spreadsheet where they could simply put the low values in a column and use the @average(A1…A5) formula. Is there a way to automate this in Access ?

(As you may understand, it’s a lot more than 5 or 6 assessors I have to calculate for – otherwise I’d just dump it into Excel !)

Many thanks

Reply | Quote

Replies

Use the Nz function to substitute a zero for null field values. Just remember that you can’t divide by zero, so you need to test the count as well.

Reply | Quote

Charlotte’s reply will work if you want the average of 4 – 4 – 5 – 6 – (missing) to be 19 / 5 = 3.8. But maybe you want it to be 19 / 4 = 4.75. If so, the best way to compute it depends on how your data are stored.
If you have a query where each mark is in a separate record (row), you can use the built-in Avg or DAvg functions – these ignore null values. For instance, you could create a totals query that has Group By on the Official field and Avg on the LowMark field.
If the marks for an official are in separate fields (columns), it’s a bit more complicated. You’d have to check for fields being non-null when counting them.

Reply | Quote