Averages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert - 26 Sep 2005 21:50 GMT

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.

Reply to this Message

Rick B - 26 Sep 2005 21:59 GMT

One post per question, please.

Signature

Rick B

> I need to create a query that will give me averages on a list of numbers.
>
[quoted text clipped - 15 lines]
>
> Any help would be appreciated.

Reply to this Message

S.Clark [Access MVP] - 26 Sep 2005 22:01 GMT

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

> I need to create a query that will give me averages on a list of numbers.
>
[quoted text clipped - 15 lines]
>
> Any help would be appreciated.

Reply to this Message

John Spencer (MVP) - 26 Sep 2005 23:35 GMT

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

> I need to create a query that will give me averages on a list of numbers.
>
[quoted text clipped - 15 lines]
>
> Any help would be appreciated.

Reply to this Message

Jeff Boyce - 27 Sep 2005 00:59 GMT

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>

>I need to create a query that will give me averages on a list of numbers.
>
[quoted text clipped - 16 lines]
>
> Any help would be appreciated.

Reply to this Message

Michel Walsh - 27 Sep 2005 11:23 GMT

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

>I need to create a query that will give me averages on a list of numbers.
>
[quoted text clipped - 16 lines]
>
> Any help would be appreciated.

Reply to this Message