averaging a field value if field value is greater then 0 access report

  • Thread starter Thread starter BRC
  • Start date Start date
B

BRC

I have a report that is grouped by date. I have a Textbox in the
heading that calculates the average of a field1 in the report. the
formula in the field is "=Round(Avg([field1]),2)". This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated. Thanks in advance for any
suggestions
 
I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions
Click to expand...

I should have included the info; this is a mdb file and I am using
access 2010. I have also tried the sytax
=Avg(IIf(field1 <> 0, field1, Null)) which looks like it should work
but when i type this into the control source I get "the expression you
have entered has invalid syntax" error message. thanks again for any
suggestions.
 
I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions
Click to expand...

what you need is to make 0 records NULL not 0

in computer math null means I don't know or care, 0 means I know and
there was none

You use average() when you want the average of some field that you
only check every 5 records ie the other 4 are null ie you didn't
check. average() ignores NULL

If you check a value every record and the fact that there is 0 items
means bad then the field is needs to default to 0 or you need to count
the records = N then sum the fileds then divide by N

if you use the built in Microsoft function it will ignore Null fields
ie
average (2,2,2,null,2) = 2
average (2,2,2,0,2) = 1.6
or
if you want to to get a true average you need to sum(fields) / count
(records) = 8/5 = 1.6.
Hope this helps
 
I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions
Click to expand...

I should have included the info; this is a mdb file and I am using
access 2010.  I have also tried the sytax
=Avg(IIf(field1 <> 0, field1, Null)) which looks like it should work
but when i type this into the control source I get "the expression you
have entered has invalid syntax" error message. thanks again for any
suggestions.
Click to expand...
assuming you are using a query then add where is is not null to your
where class and forget the NZ
select round(average(field3)) as avg from table where field3 <> 0

field3 <> 0 if it might be negative

field3 > 0 will filter out NULL ,0 and negative if it not possible to
be negative. I know that null is less then 1

Maybe need field3 <> 0 and isnull(field) = false to be sure that it
is not null and not 0 but can be > 0 and <0
 
I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions
Click to expand...

what you need is to make 0 records NULL not 0

in computer math null means I don't know or care,  0 means I know and
there was none

You use average() when you want the average of some field that you
only check every 5 records ie the other 4 are null ie you didn't
check. average() ignores NULL

If you check a value every record and the fact that there is 0 items
means bad then the field is needs to default to 0 or you need to count
the records = N then sum the fileds then divide by N

if you use the built in Microsoft function it will ignore Null fields
ie
average (2,2,2,null,2) = 2
average (2,2,2,0,2) = 1.6
or
 if you want to to get a true average you need to sum(fields) / count
(records) = 8/5 = 1.6.
 Hope this helps
Click to expand...

Derek
Thanks for the response. I learned that my problem was being caused
by another issue but this will help me when i get the other issue
resolved. Thanks again BRC
 
Back
Top