# Thread: Weighted Average - Ignore Nulls

1. Member
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. Registered User
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. Member
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. Member
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)`