Average values excluding nulls and zeros

[niuginikiwi]

Hi,
I have a report that is based on a query. On the report footer I have a
txtAvg (=Avg([HDays]) which is a calculated field in the query that is on
the report. This calculated field is based on two date fields (ie number of
days between two dates). If one date isn't entered there is no value.
What I want to do is have the average of HDays calculated excluding all
values that are equal to zero or null.
I know this will be easy but I just can't figure it out.

Thanks for any help I may get.

 

[Allen Browne]

Avg() ignores nulls, so perhaps you could convert your zeros to nulls before
averaging:
=Avg(IIf([HDays]=0, Null, [HDays]))

 

[niuginikiwi]

Thanks Allen,
That not only worked but clarified my understanding a little about nulls and
zeros.
Once again, thanks so much.