# Thread: Calculating Averages Across Multiple Fields

1. Registered User
Join Date
Oct 2003
Posts
19

## Unanswered: Calculating Averages Across Multiple Fields

Hi there,

I'm not too familiar with Visual Basic and usually use queries/macros/expressions to get any calculations I need, so please excuse my ignorance here... but I need to calculate averages over multiple fields the same way that excel would if you used the Average function across a row of fields (automatically adjusting the denominator to account for null values).

I've tried building a module by copy/pasting:

---
Function RowAvg(ParamArray Stuff() As Variant) As Variant Dim c As Integer: c = 0 Dim
s As Variant Dim i As Integer For i = LBound(Stuff) To UBound(Stuff) If
IsMissing(Stuff(i)) Then ' skip it ElseIf IsNull(Stuff(i)) Then ' skip it too Else s
= s + Stuff(i) c = c + 1 End If Next If c > 0 Then RowAvg = s / c Else RowAvg = Null
End Function
---

2. Registered User
Join Date
May 2005
Location
Posts
2,888
To be honest, wanting an average across fields hints at a design problem. Typically values to be summed/averaged would be in records, not fields. In any case, one of these could be tweaked to do what you want:

http://support.microsoft.com/default...b;en-us;209857

3. Registered User
Join Date
Oct 2003
Posts
19
Yes, I'm aware now that I should have set up the ratings as records and not fields.

I actually found the full code by John Spencer and it worked! (thank you for your reply though).

Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of
'a group of values passed to it.
'Sample call:
'myAvg = GetMeanAverage("1","TEST","2","3",4,5,6,0)
' returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fGetMeanAverage = dblSum / intElementCount
'At least one number in the group of values
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•