Averages

[Robert]

I need to create a query that will give me averages on a list of numbers.

How can I calculate the averages, but ignore 0 or null values. Addtionally
if I am ignoreing the 0 value I don't want the record field to factored in
the average count.

e.g.

5
0
2
4

In this senario I would want the averages to be be caluated on the 3 records
with values in excess of 0., 5+2+4=11 11/3 =3.6

If I took the 0 value I would end up with an average of 11/4=2.75

Any help would be appreciated.

 

[Rick B]

One post per question, please.

 

[S.Clark [Access MVP]]

You could do two queries. One to exclude what you don't want in the calc,
then the second to perform the calc.

Steve Clark, Access MVP
FMS, Inc

 

[John Spencer (MVP)]

MyAverage: Avg(IIF(TheField=0,Null,TheField))

 

[Jeff Boyce]

Robert

Are all your values in the same field, multiple rows, or are you trying to
average like you might a spreadsheet (multiple columns of numbers in the
same row)?

Regards

Jeff Boyce
<Access MVP>

 

[Michel Walsh]

Hi,



SELECT AVG(fieldName)
FROM tableName
WHERE fieldName <> 0


will return 3.6. The WHERE clause is applied before the aggregations
occurred. If you need a criteria to be applied AFTER the aggregation
occurred, you use a HAVING clause. Most aggregate also remove the NULL
(unknown) values from consideration.


Hoping it may help,
Vanderghast, Access MVP