Average function not calculating correctly

[rhonda6373]

Hello,

I am trying to compute the average of Cost of New based on the criteria below. However, for example if there are 18 records and there are only 2 that meet the criteria it is dividing by 18 instead of 2. How can I get this to work correctly? Thanks in advance for the help.

=Avg(IIf([Final]="TST-TST OK RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Nz([Cost Of New],0),0))

[Rabbit]

It's working correctly as you have written it. You haven't filtered to number of records to 2, you're just changing the values of the other 16 records to 0. They're still there, so they're still used to calculate the average.

Rather than changing the values of the other 16 records, you need to filter out the other 16 records.

[rhonda6373]

Thanks Rabbit. I got it before I saw your reply by combining some other functions I had. I will try it your way as well so that I can understand.

Here is what works:

=Sum(IIf([Final]="TST-TST OK RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Nz([Cost of New],0),0))/Sum([Final]="TST-TST OK RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST")*-1

[Rabbit]

That's a very convoluted way of getting to what you need. It's better to put the criteria in the where clause, then all you need is Avg(Nz([Cost of New], 0))

[rhonda6373]

Thank you. I will try that.