Ignore Zeros in an Average 2

[TammyT]

I have a report that is taking data from a table (duh!) and calculating an average of the scores for each appropriate field. I've always been under the impression that Access ignores zeros when doing averages; however, this is not true by the results I'm getting! It's simple math - I'd like to do it from the Control Source of the field, if possible. Currently the formula is =Avg([Promptness])*20 (we're taking scores of 1-5 & turning them into a 'grade' by doing the *20). Is there a relatively easy way to get Access to ignore the zeros? I have confirmed that all of the fields containing the data are numeric fields. Thanks!

 

[SgtJarrow]

use a query as the record source for your report and set the criteria proprty of the [promptness] field to:

<> 0

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[TammyT]

I tried this - now I get #Error as the response for all the fields that calculate averages! Next idea? =)

 

[dhookom]

Try something like:
=(Sum([Promptness])/Abs(Sum([Promptness]<>0)))*20

Duane
MS Access MVP

 

[BobJacksonNCI]

Wow, Duane!

Very impressive!!

Tammy, if the 0 fields were null you'd get the average you're looking for. Of course null could cause problems, too.

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[TammyT]

Wow is right! Now the averages are correct. Bob - I think you're right; the fields are zero, not null, which may be way your idea gave an error.

Okay, new problem: when the result is legitimately zero (no responses for that survey question, which creates the zero in the first place!), the report now displays #Number. Can I do something to have it display 0 ?

(ps - y'all are great!)

 

[dhookom]

Are you suggesting there are no records where Promptness <> 0? If so, you have to add a check for this:
=IIf(Sum([Promptness]<>0)=0,0,(Sum([Promptness])/Abs(Sum([Promptness]<>0))))*20

I think I got all the ()s correct.

Duane
MS Access MVP