AVG() Errors

[rhnewfie]

I seem to have run into a problem with averaging on a report. I am using DAVG to calculate averages on fields that contain zeros but sometimes when the grouping changes, from one subgroup to another where a new avg is calculated, the avg doesn't show up??? I end up with averages for the first few groups and then none for a few, and then they come back again!! Has anyone seen this before??

 

[JoeMiller]

What does the DAvg statement you made look like, what is the field it's trying to average, what query/table is it trying to average in, and what is the criteria for the records to be averaged? Joe Miller
joe.miller@flotech.net

 

[rhnewfie]

Sorry, here it is:

DAVG([columnname],&quot;tablename&quot;,[columnname]<>0)
it works as is for most of the groups that have a lot of records, it only seems to mess up when there are groups with a lot of records but most are zero, and thus not included. ie: if I have 0-0-0-0-0-0-0-79-0-0-0 then the davg should be 79, but nothing comes up at all.

Thanks for your help.

 

[JoeMiller]

Are there any nulls in the column? This might mess up the DAvg.. you could try using:

DAvg(&quot;nz([columnname]&quot;,&quot;tablename&quot;,&quot;nz([columnname])<>0&quot

HTH Joe Miller
joe.miller@flotech.net

 

[rhnewfie]

What does the &quot;nz&quot; do? Is that an extra &quot;(&quot; at the beginnig?

Thanks again.

 

[JoeMiller]

Whoops.. you're right there is an extra because I missed adding one! Nz is the NullToZero function, look it up in the online help for a more indepth explanation. It basically tests for null, and if it is null converts it to a 0 (or any other thing you'd like if you put in an argument). Here's the corrected function


DAvg(&quot;nz([columnname])&quot;,&quot;tablename&quot;,&quot;nz([columnname])<>0&quot Joe Miller
joe.miller@flotech.net

 

[rhnewfie]

It doesn't seem to help but thanks anyway.

RHNewfie

 

[SidCharming]

rhnewfie,

Did you get your problem figured out? I noticed your statement:

[tt]DAVG([columnname],&quot;tablename&quot;,[columnname]<>0)[/tt]

is missing some quotes... here is what I would use

[tt]DAVG([columnname],&quot;tablename&quot;,&quot;[columnname]<>0&quot;)[/tt]



Sid from Minnesota

 

[dhookom]

All domain aggregate functions have very little use in reports. If you want to average the column based on a criteria, your expression &quot;DAVG([columnname],&quot;tablename&quot;,[columnname]<>0)&quot; has several issues:
1) the column name must be inside quotes
2) the where clause must be inside quotes
3) if the report is opened with a where clause that limits the report to specific records, the DAvg() will return the average of all records in tablename rather than just those in the report.

Try this expression:
=IIf( Sum(Abs([ColumnName]<>0))=0,0,Sum([ColumnName])/Sum(Abs([ColumnName]<>0)) )

Sum(Abs([ColumnName]<>0)) will count the number of non-zero values.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SidCharming]

Duane,

BTW, that is my middle & my Father's first name... Nice name!

So you recommend performing the full mathematical string instead of using the 'avg' function? or are you saying only for this example?

My reports house many of the averages, Counts, Standard Deviations, Minimum and Maximum commands.



Sid from Minnesota

 

[dhookom]

I don't know how the avg() function would work on a field since you want to filter out specific records. Count(), Avg(), Min(), and Max() will work as I suggested. I'm not sure how you would calculate standard deviation on filtered records.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SidCharming]

I found through a different user that this command works perfect for my report.

=avg(iif([report]=1,[ColumnName]))

This gives me the average of all [ColumnName] values that have a [report] value of 1. Now my calculation is generated from the report dataset, not the domain set which as indicated above is Slow and not the current dataset/filter.

Thanks



Sid from Minnesota