Calulate Avg in report footer

[cneill]

Do you know how to correct the following
I have the following code =Avg([Total]) in a report footer
This calulates the avg of 8 records but if one of the records is Zero which I do not what to include in the Avg. How can I change the code to not include the Zero records?
Thanks

 

[randy700]

One way...

Create a separate query that performs your calculation, then use the DLookup function in your report footer.


Randy

 

[ProgramError]

hey, why not have the reports' (shouldn't this be in the reports forum?) query not include zero values (isnot(0)) in the field then the avg will work as normal.

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[PHV]

What about this ?
=Avg(IIf([Total]=0,Null,[Total]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Or this ?
=Sum([Total]/Sum(IIf([Total]=0,0,1))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cneill]

Hi Everyone,

thanks for your replies
this =Avg(IIf([Total]=0,Null,[Total])) worked a treat
Thanks for your help