# AVG() or SUM() in different fields with nulls

I have used Avg() to calculate the average of valus in a single field that may contain null values. Now I want to calculate the average of 5 values that are in different fields. It is possible that some of these values can be null and I will not want to include these in the average. In this case when I try to add the 5 values when one of them is null I come up with a null value. Can anyone help me wiht this? Thanks in advance.

Susan
###### Who is Participating?

Commented:
Hello Susan

I think you need own function to do the calculation. Then use it with your query. The function could look like this (add this into your global Module):

Public Function MyAVG(field1, field2, field3, field4, field5) As Variant
Dim AvgCount As Byte

' assume all fields contain value
AvgCount = 5

' minus 1 if field1..5 is null
AvgCount = AvgCount + IsNull(field1)
AvgCount = AvgCount + IsNull(field2)
AvgCount = AvgCount + IsNull(field3)
AvgCount = AvgCount + IsNull(field4)
AvgCount = AvgCount + IsNull(field5)

If AvgCount > 0 Then
MyAVG = (Nz(field1, 0) + Nz(field2, 0) + Nz(field3, 0) + Nz(field4, 0) + Nz(field5, 0)) / AvgCount
End If

End Function

Test results using Debug Window:
? MyAVG(null,null,null,2,4)
3

? MyAVG(5,5,5,5,null)
5

Usage example with query:

SELECT MyAVG(Number1, Number2, Number3, Number4, Number5) AS Average
FROM tblNumbers;

Hope this helps,
Usage with query:
0

Commented:
u can use IsNull(FIELD) to include it or not in the AVG(), something like:

For each field in fields
if not IsNull(field)
SUM = SUM + field
intNbrValues = intNbrValues + 1
end if
next

AVG = SUM/intNbrValues
0

Author Commented:
0

Commented:

Best regards,