Average of records in subform (1 Viewer)

[rhonda6373]

Hello, I am using the following function:

=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))

I have a subform with a total of 18 records. 2 records meet the criteria above. This function is dividing by 18 (total number of records) instead of 2 to get the average. How can I get it to divide by the right number of records?

 

[plog]

Instead of Average you will need to use two Sum functions, one dividing the other.

Sum(If(YourCriteria), Value, 0) / Sum(If(YourCriteria), 1,0)

Top one produces the total sum, the bottom one counts how many apply. Actually, just to be safe, you'll need an Iif as well to to test the bottom one for 0. Because if it becomes 0 you are getting a divide by 0 error.

 

[rhonda6373]

Thank you so much! I would have never gotten that. I will try it.

 

[rhonda6373]

I am getting an invalid syntax. Can you help?

=Sum(If([Final]="TST-TST OK RTN"), Nz[Billed], 0) / Sum(If([Final]="TST-TST OK RTN"), 1,0)

 

[jdraw]

You can check syntax and examples at http://www.techonthenet.com

Sum
iif
nz