Solved

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

Posted on 2000-05-15
4
353 Views
Last Modified: 2012-06-21
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
Comment
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
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Expert Comment

by:Simonac
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

by:
paasky earned 100 total points
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,
Paasky
Usage with query:
0
 

Author Comment

by:susantrider
ID: 2811722
Paasky, you have helped me agian. Thanks. The other answer was also helpful. Thank you!
0
 
LVL 10

Expert Comment

by:paasky
ID: 2811753
Always happy to help you Susan. :o)

Best regards,
Paasky
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question