Re: averages containging null values

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

How about using the following expression

=Avg(IIF([SomeField=0,Null,SomeField))

Avg ignore nulls so any field the is null won't be used to calculate the
average. And you are forcing zeroes to Null, so they won't be used in the
calculation either.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"greg" <greg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3360683C-8B8A-4666-958B-ABFC63CB30B9@xxxxxxxxxxxxxxxx
I have a grouped field in a report that returns some percentage values
ie
90%
90%
0%
50%
null
i want to calculate the average percentage of this field excluding 0 and
null and put it in the group footer
ie
230/3 = 76.66%


.

Relevant Pages

  • Re: Can someone explain why there isnt an error?
    ... Stefan Berglund wrote: ... >> Avg to get the average for a week, or month, or other periods. ... > NULLS, but I got so tired of all the code workarounds that I now ... when the workaround to avoid NULL is not so simple. ...
    (microsoft.public.vb.general.discussion)
  • Re: Can someone explain why there isnt an error?
    ... >Avg to get the average for a week, or month, or other periods. ... NULLS, but I got so tired of all the code workarounds that I now ... signified nothing is there but I will argue that data integrity ... is more assured by excluding NULLS. ...
    (microsoft.public.vb.general.discussion)
  • Re: AVG (zero shall not included)
    ... Avg ignores nulls. ... "mroks via AccessMonster.com" wrote in message ... i would like not to affect the result by zero. ...
    (microsoft.public.access.formscoding)