Average from Multiple Columns

[rsmonkey]

lmao i just got into work and saw the 19 responses! I will get onit and work through peoples responses and write back who is teh winner.. deffo sum intresting stuff posted though so ta to every1

 

[rsmonkey]

MyColumnD:Avg(Nz([Score1],0)+Nz([Score2],0)+Nz([Score3],0))/3 worked perfectly so you were all pretty much right on the money oh and ajeTrumpet your littel nightmare of a query also worked

many thanks,

 

[KenHigg]

Not to beat a dead horse - but...

Avg(Nz([Score1],0)+Nz([Score2],0)+Nz([Score3],0))/3

is the same as:

(avg([Col1] + avg([col2]) + avg([col3]))/3


ken

 

[rsmonkey]

oh and 1 other thing ajeTrumpet, although you method worked it did round up to the nearest number! instead of to 1 decimal place, I think this might be something to do with the Expression & Avg variables (Avg will to it to 1 decimal place) when using Totals in a query..

 

[rsmonkey]

and Ken if you look at my 4:51am post you will that i said:

you were all pretty much right on the money

lol cheers

 

[KenHigg]

.
.
.
Ok.

 

[ajetrumpet]

If you're still reading this monkey, look at numeric data types...memorizing what conv. functions do to those is a nightmare itself. I guess this was suitable instead...

SELECT Round(avg([column1]), 1)...

 

[homage]

if you want a null to cause an average of null

(nz([col1],0)+nz([col2],0)+nz([col3],0))/(3-iif(isnull([col1]),1,0)-iif(isnull([col2]),1,0)-iif(isnull([col3]),1,0))

This works really good. But, I have a slight problem when all three columns are null then I am getting #Error. Is there a way to tweak either this query or a second one? I would actually prefer the result to show 0 so I think that may be easier than having it show null. But I will take whatever I can get.