Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    UK
    Posts
    21

    Unanswered: Weighted Average - Ignore Nulls

    I want to get the average of 1 column (x) weighted by another (y) in the group footer of a report.

    Obviously sum(x*y)/sum(y) would work if x was never null, but in the case that x IS null the average should only be calculated from the rows where x is NOT null.

    I'm guessing clever use of nz() or isnull() is the key, but can anyone save me a wade through the textbooks?

    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Weighted Average - Ignore Nulls

    Originally posted by Conchur
    I want to get the average of 1 column (x) weighted by another (y) in the group footer of a report.

    Obviously sum(x*y)/sum(y) would work if x was never null, but in the case that x IS null the average should only be calculated from the rows where x is NOT null.

    I'm guessing clever use of nz() or isnull() is the key, but can anyone save me a wade through the textbooks?

    Thanks.
    id try an iif statment
    in the data source of the text box in the footer put
    =iif(sum(x) is not null, sum(x*y)/sum(y) ,null)

    Jim

  3. #3
    Join Date
    Oct 2002
    Location
    UK
    Posts
    21
    No, that's not what I'm after - I think I need to give you a better example:
    Code:
    Name   Count   Percent
    Bob      17        90%
    Jim      10        NULL
    Harry    12        23%
    ---------------------------
    Average            62%
    Where the weighted average is calculated as:
    Sum(Count * Percent) / Sum(Count)
    Ignoring any rows with Percent = NULL

    Any more ideas?
    Last edited by Conchur; 12-19-02 at 13:00.

  4. #4
    Join Date
    Oct 2002
    Location
    UK
    Posts
    21

    A solution, but not perfect

    I've fallen back on a query technique, adding a new field:
    Code:
    IIF(NOT ISNULL(Percent), Count, NULL) As CountForPercent
    Then in the report the Weighted Average is:
    Code:
    SUM(Percent * Count) / SUM(CountForPercent)
    Which gives the correct answer.

    I'm still interested in a Report code-based solution if anyone knows of a more elegant approach that doesn't involve changing the underlying query?
    Last edited by Conchur; 12-19-02 at 13:01.

Posting Permissions

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