Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Southampton, U.K.
    Posts
    30
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Finding the average of a series including nulls (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the average of a series including nulls (2000)

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding the average of a series including nulls (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •