Average when some fields are null

[NB]

I need to average scores from a test. Usually it is across 3 sections for
each record, but sometimes a field is null. In Excel, the Avg command
automatically adjusts when a field is null. Can Access?

I want to see all records on one report, so I don't want to do it in
separate queries.

Example:

RECORD 1
Score A= 5
Score B= 7
Score C= 5
Average = (5+7+5)/3 = 5.67

RECORD 2
Score A= 5
Score B= null
Score C= 7
Average = (5+7)/2=6

This group is absolutely great for information, so I have no doubt that you
know the answer to this! Thank you for your help!

NB

 

[Ofer Cohen]

Mybe there is a better way, but that can do the trick

=Sum(Nz([Field Name]),0) / Sum(IIf([FieldName] Is Null,0,1))

 

[James A. Fortune]

Mybe there is a better way, but that can do the trick

=Sum(Nz([Field Name]),0) / Sum(IIf([FieldName] Is Null,0,1))

I have good news, bad news and good news . The Avg function in
Access doesn't count Nulls as part of the average. That's good news.
In a query, the Avg function works down columns rather than in rows.
That's bad news. Ofer's solution can work within a row as a calculated
expression with minimal changes. That's good news. Post back if you
need more help.

James A. Fortune
(e-mail address removed)

 

[Ron2006]

On your table design, you may want to have the default value for
numeric fields be 0, and if you need to deferrentiate between a
non-entry and a actual 0 then use some other field/criteria. But it all
depends on your data and your organization and whether you want to
count 0s some of the times or all of the times or none of the times.

Ron.

 

[Michael H]

Hi NB.

Here are two methods. For each, I assume that the name of your Table is
"Scores", and the names of your fields are "ScoreA", "ScoreB", and "ScoreC".
Please make changes where appropriate using your own Table and Field names.

Method 1 uses only SQL. It is a bit clumsy, and will give an error for the
Average field if all three Score fields are Null, but otherwise it works (the
error would not affect the query results, but you would probably want to hide
the Average field in a report):
SELECT Scores.ScoreA, Scores.ScoreB, Scores.ScoreC,
(nz([ScoreA])+nz([ScoreB])+nz([ScoreC]))/(3+IsNull([ScoreA])+IsNull([ScoreB])+IsNull([ScoreC])) AS Average
FROM Scores;


Method 2 uses SQL and a custom function, fAverage. Paste the following code
into a module (not a class module or one attached to a form):

Public Function fAverage(ParamArray aMembers() As Variant)
Dim i As Integer
Dim lngCount As Long
Dim lngSum As Long
For i = 0 To UBound(aMembers)
If aMembers(i) <> 0 Then
lngCount = lngCount + 1
End If
lngSum = lngSum + Nz(aMembers(i))
Next i
If lngCount <> 0 Then
fAverage = lngSum / lngCount
End If
End Function

Then, use the following SQL:
SELECT Scores.ScoreA, Scores.ScoreB, Scores.ScoreC,
fAverage([ScoreA],[ScoreB],[ScoreC]) AS Average
FROM Scores;

Using Method 2, no error will be generated in the query if all three Score
fields are Null. Additionally, an arbitrary number of parameters can be
passed to fAverage. So, if for example a ScoreD field was added in the
future, the only change necessary to Method 2 would be to add [ScoreD] to the
list of parameters passed to fAverage.


Hope this helps.

-Michael