Exclude zero values from avg

I have an access report column that I am doing an avg of

some of the values in the column are zero and I don't want to include them in the average

any ideas how to do this?

johnnyg123Asked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
Change the zero values to null.  Nulls do not affect an average.
0
 
rockiroadsCommented:
U would need to add that to the filter in your sql

or depending how how u use AVG, u could make use of domain aggregate functions in your query and add a filter in there
0
 
rockiroadsCommented:
select ....
from ...
where somefield <> 0

kinda thing
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
johnnyg123Author Commented:
that was my initial thought

however, for reasons unknown to me....

one of the folks in a different department wants to display the zero values in the column but not include them for purposes of avg


Tried to explain this is really a true avg but.....still wanted to know what the options are


0
 
rockiroadsCommented:
so what if u used DAVG in your query?

0
 
rockiroadsConnect With a Mentor Commented:
e.,g

f1 is some field that identifies a record
f3 is the field's average u want


select f1, f2, f3, davg("f3","mytable","f1=" & a.f1 & " and f3 <> 0") as f3avg
from mytable as a



remember to use table aliase so u can join the right criteria
0
 
Eric ShermanAccountant/DeveloperCommented:
johnnyq123 ....

That being said ....

>>>>>>>>>one of the folks in a different department wants to display the zero values in the column but not include them for purposes of avg
Tried to explain this is really a true avg but.....still wanted to know what the options are<<<<<<<<

1.) On your report (detail section) you could create two invisible text controls to sum the value of your field and count to get the aggregate based on the value not being zero.  Then use those in the report's footer (or group footer) to perform the average.

Field1, set the control source to =[YourFieldName] and set the Running Sum to OverAll or OverGroup.

Field2, set the control source to IIF([YourFieldName]=0, 0, 1) and set the Running Sum to OverAll or OverGroup.

In the Report's Footer or the Group Footer create a text control and set the control source to ...
=Field1/Field2

ET



0
 
Eric ShermanAccountant/DeveloperCommented:
>>>Change the zero values to null.  Nulls do not affect an average.<<<<

Good catch GRayL ....  That being the case, he could do the Avg in a Query (grouped by his field) then join that Query to the main report's Query.  All he would have to do is display the Avg on the report since it will have been calculated in the underlying record source's query.

ET

0
 
GRayLCommented:
If you have gone through some trouble to 'make sure' zero's appear in the report instead of blank, that is your problem. Remove the nz() function from the troublesome control.
0
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
Yep ....   ITA GRayL.  The zero values in the underlyig table should be NULL.  Then just use the Avg Function in the report and you can format the report field IIF IsNull then show 0.

I have some clients like that where they have to see the 0's on the report when the field is empty.

ET
0
 
GRayLCommented:
It's what I call an incongruity.  Zero is a value and affects an average.  Null is nothing and doesn't.  The customer who has to see a zero but doesn't want it to affect the average is incongruous - or nuts;-)
0
 
johnnyg123Author Commented:
Appreciate all the posts!

I don't disagree one bit with GRayL comment that "The customer who has to see a zero but doesn't want it to affect the average is incongruous - or nuts;-)"

Believe me....I have expressed that to the customer in no uncertain terms

I will split the points between the 3 folks that were kind enough to respond.
0
 
GRayLCommented:
Thanks, glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.