[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

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

Posted on 2000-05-15
Medium Priority
356 Views
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
0
Question by:susantrider
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 2

Expert Comment

ID: 2811590
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

LVL 10

Accepted Solution

ID: 2811628
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

Author Comment

ID: 2811722
0

LVL 10

Expert Comment

ID: 2811753

Best regards,
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
###### Suggested Courses
Course of the Month14 days, 13 hours left to enroll