AVG formula

R

Risikio

I want to determine the AVG of a field without including any "0" fields. I
can not leave these fields blank, because in another area I use the AVG with
the "0" fields.

My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
correct because all of the non "0" values are positive, and the value
returned is -0.4.

Any help would be greatly appreciated.

Thanks.
 
J

John Vinson

I want to determine the AVG of a field without including any "0" fields. I
can not leave these fields blank, because in another area I use the AVG with
the "0" fields.

My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
correct because all of the non "0" values are positive, and the value
returned is -0.4.

Any help would be greatly appreciated.

Thanks.
Click to expand...

Well, zero is a number - if you were to want to average (-2, -1, 0, 1,
2) you would want to get zero as the result!

If you're calculating the two types of averages in different queries,
perhaps you could simply use a criterion of
0
Click to expand...

on one of the queries to exclude zero values from the query
altogether. If that's not feasible (i.e. you want to see those records
but just not include them in the average), you'll need to do something
snarky like:

=DSum("[fieldname]", "[tablename]", "<criteria>") / DCount("*",
"[tablename]", "<criteria> AND [fieldname] > 0")

where the <criteria> select that subset of records which you wish to
average.

John W. Vinson[MVP]
 
R

Risikio

John,

This is in a subreport. The subreport will show both averages. Averages
with the "0" is shown for all of the records. Averages without the "0" is
shown for only those records that reported a value greater than "0". Since
this is in a subreport, I am guessing I would need to run the second query
like you suggested, however that would create a subreport within a subreport.
Is that possible, to put a subreport in another subreport?

John Vinson said:
I want to determine the AVG of a field without including any "0" fields. I
can not leave these fields blank, because in another area I use the AVG with
the "0" fields.

My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
correct because all of the non "0" values are positive, and the value
returned is -0.4.

Any help would be greatly appreciated.

Thanks.
Click to expand...

Well, zero is a number - if you were to want to average (-2, -1, 0, 1,
2) you would want to get zero as the result!

If you're calculating the two types of averages in different queries,
perhaps you could simply use a criterion of
0
Click to expand...

on one of the queries to exclude zero values from the query
altogether. If that's not feasible (i.e. you want to see those records
but just not include them in the average), you'll need to do something
snarky like:

=DSum("[fieldname]", "[tablename]", "<criteria>") / DCount("*",
"[tablename]", "<criteria> AND [fieldname] > 0")

where the <criteria> select that subset of records which you wish to
average.

John W. Vinson[MVP]
Click to expand...
 
R

Risikio

Karl,

Unfortunately, this returns the same -0.4 value. The value with the "0"
fields is 0.77, so the value without the "0" fields should be higher than
that. I have even tried the following formula
=Sum([fieldA]<>0)/Count([fieldA]<>0). Any other suggestions?

Thanks.

KARL DEWEY said:
Use <>0

Risikio said:
I want to determine the AVG of a field without including any "0" fields. I
can not leave these fields blank, because in another area I use the AVG with
the "0" fields.

My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
correct because all of the non "0" values are positive, and the value
returned is -0.4.

Any help would be greatly appreciated.

Thanks.
Click to expand...
Click to expand...
 
R

Risikio

The following formula seems to work for my situation.

=Sum([fieldA])/Sum(IIF([fieldA]>0,1,0))

Thanks for your help.

John Vinson said:
I want to determine the AVG of a field without including any "0" fields. I
can not leave these fields blank, because in another area I use the AVG with
the "0" fields.

My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
correct because all of the non "0" values are positive, and the value
returned is -0.4.

Any help would be greatly appreciated.

Thanks.
Click to expand...

Well, zero is a number - if you were to want to average (-2, -1, 0, 1,
2) you would want to get zero as the result!

If you're calculating the two types of averages in different queries,
perhaps you could simply use a criterion of
0
Click to expand...

on one of the queries to exclude zero values from the query
altogether. If that's not feasible (i.e. you want to see those records
but just not include them in the average), you'll need to do something
snarky like:

=DSum("[fieldname]", "[tablename]", "<criteria>") / DCount("*",
"[tablename]", "<criteria> AND [fieldname] > 0")

where the <criteria> select that subset of records which you wish to
average.

John W. Vinson[MVP]
Click to expand...
 
Top