Average Columns

[bsdixon]

I have a query where I want to average columns 1, 2, 3, and 4. I want the average to show up in column 5. There may not always be values in all of the columns so I cannot just divide by 4. How do I set up this formula?

Thanks for the help!

 

[Jon K]

(Nz([field1])+Nz([field2])+Nz([field3])+Nz([field4]))/4

 

[bsdixon]

I do not want it to divide by 4 if there is only data in 3 columns.

There is an average function in Excel that does this. I was wondering if there is a similiar function in Access that I can use for the query.

 

[Jon K]

Access doesn't have functions that work horizontally across columns.

(Nz([Field1])+Nz([Field2])+Nz([Field3])+Nz([Field4]))/Abs((Not IsNull([Field1]))+(Not IsNull([Field2]))+(Not IsNull([Field3]))+(Not IsNull([Field4])))

The expression finds how many columns are not empty. If there are records where all four fields are empty, you can build a function, instead of using this expression, to avoid division by 0.

 

[bsdixon]

That worked!! Thanks!

I can't believe that it is such a long formula!

 

[KKilfoil]

That worked!! Thanks!

I can't believe that it is such a long formula!

If you have data in four fields in the same record that can be sensibly averaged together, then you may have a 'normalization' design problem with your table(s).

Access has well designed aggregate functions that can operate on ONE value from SEVERAL records. However, IT IS NOT A SPREADSHEET.

For example, if a teacher has a class that will take 3 tests, and wants to find the test average, the spreadsheet solution would look like

Student Test1 Test2 Test3 Avg
Adam 100 90 80 90
Barbara 80 84 85 83
Christine 100 100 97 99

The Access way to do it is to make 2 tables like:

[tblStudents]:
StudentID - Intger
StudentName - Text
etc

[tblTestResults]
StudentID - Integer
TestNumber - Integer
Result - Number

with data like:

[tblStudents]
1 Adam
2 Barbara
3 Christine

[tblTestResults]
1 1 100
2 1 80
3 1 100
2 1 90
2 2 84
2 3 100
3 1 80
3 2 85
3 3 87

and then a query is constructed to group the tblTestResults records by students and find the average of Results.


You see the difference?