Averages

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.

One post per question, please.

--
Rick B



"Robert" wrote in message
...
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.

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

"Robert" wrote:

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.

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

Robert wrote:

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.

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

"Robert" wrote in message
...
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.

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


"Robert" wrote in message
...
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.