Average of multiples fields, ignoring zeros

Topic

New Reply

31563-ccr

I’ve got a number of responses to our comment card forms entered into a db and I’ve begun to analyse the data. Each department has multiple fields/categories they are scored on. One thing I’d like to do is get an average for the department, while not counting the zero results (indicates no response). I’ve calculated the averages for each category

Code:

=Avg(IIf([BookingExperience]=0,Null,[BookingExperience]))

but if I try get a department average using

Code:

=(Avg(IIf([BookingExperience]=0,Null,[BookingExperience]))+Avg(IIf([Friendly]=0,Null,[Friendly]))+Avg(IIf([Helpful]=0,Null,[Helpful]))+Avg(IIf([Knowledgeable]=0,Null,[Knowledgeable])))/4

I get incorrectly weighted results (not shown in screenshot), or errors if there are null values (ie: Guest Rooms, week 18).

Eventually I’m going to want quarterly averages, overall averages, etc, and will probably want to present them in other places than just this report, so should I be doing my calculations elsewhere (currently just in the field of the report) so that they can be called for charts, other reports, etc?

Reply | Quote

Replies

Look up Access help on DAvg and use the criteria “FieldName Not Is Null”

Reply | Quote