How to calculate the average of 5 columns on a database (access 2007)

[Wiredboy]

Hi, i'm pretty new in this and i hope somebody can help me with this:

i have a db with 5 columns with different values and i want to calculate the average of them. I've tried to create a formula directly on the query but is not working ( ex. MONTOTAL : Avg([MON1] + [MON2] + [MON3] + [MON4] +[MON5]) )

al it does is giving me the sum of the columns and not the average. Maybe i have the formula all wrong, that is why i need some help!!!!!!

Thanks!!!

[NeoPa]

Avg() is an aggregate function which returns the average value. Not of the one parameter you pass it (You add the columns to form a sum before passing this to Avg() in your code.), but of the various records within a group that are processed. This would typically be called in a GROUP BY query.

What you need is simpler even than that.

MonTotal: ([Mon1]+[Mon2]+[Mon3]+[Mon4]+[Mon5])/5

[Wiredboy]

Thanks NeoPa but i've already tried that and is not working for me since not all of the times all of the columns have a value attached to it. for example maybe it has to calculate the Avg of only 3 columns instead of 5??!!!

[NeoPa]

It seems it would have been a good idea to mention that originally then. Save us both some time ;)

If I understand you correctly, try instead :

MonTotal: (Nz([Mon1])+Nz([Mon2])+Nz([Mon3])+Nz([Mon4])+Nz([Mon5]))/5

[nico5038]

MonTotal: (Nz([Mon1])+Nz([Mon2])+Nz([Mon3])+Nz([Mon4])+Nz([Mon5]))/5

Small addition, when 4 fields are filled, I assume the average needs to be divided by 4, 3 fields filled than divided by 3, etc.
When that's the case additional code is needed to replace the 5 by:

/ (IIF(isnull(Mon1),0,1)+IIF(isnull(Mon2),0,1)+IIF(isnull(Mon3),0,1)+IIF(isnull(Mon4),0,1)+IIF(isnull(Mon5),0,1))

Finally when all can be Null, you should protect against zero divide....

Nic;o)

[NeoPa]

Good spot Nico. I overlooked that. Both points are valid and important.

It may be that you know your data can never possibly have no values, but the formula will never work, even with reduced values, unless you incorporate Nico's changes.

[Wiredboy]

Thank you guys! It's really working now!!!

[NeoPa]

Excellent :) Thanks for updating us.