Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Microsoft Access 2003 calculations?
I am constructing a database in which each record will include several numeric fields. I want to create a report that shows the average of those numeric fields. Some of the fields may be null, so I anticipate using the AVG function to calculate the average since that function ignores null fields. The expression that I have tried to incorporate into the 'control source' in the rerport is "=Avg([Score 1]+[Score 2]+[Score 3]+[Score 4])" but that isn't working - it doesn't return a value. What am I doing wrong?
1 Answer
- ArmchairPilotLv 61 decade agoFavorite Answer
AVG works on a column of values, not across fields
Use a calculated field in a query:
AverageScore: (Iif(IsNull([Score1],0,[Score1])+Iif(IsNull([Score2],0,[Score2])
+Iif(IsNull([Score3],0,[Score3])+Iif(IsNull([Score4],0,[Score4])/4
If you need to adjust the divisor because of null entries, then you will have to use a VBA function:
AverageScore: Avrge([Score1],[Score2],[Score3],[Score4])
Where
Function Avrge(A, B, C,D)
Dim N As Long, Sum As Long
Dim X(3) As Variant
N = 0
Sum = 0
X(0) = A
X(1) = B
X(2) = C
X(2) = D
For i = 0 To 3
If Not IsNull(X(i)) Then
N = N + 1
Sum = Sum + X(i)
End If
Next i
Avrge = Sum / 4
End Function
