Unanswered: Calculating Averages Across Multiple Fields
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
as suggested by another thread, but it doesn't work. Can someone please help?
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:
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.
'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
If intElementCount > 0 Then
fGetMeanAverage = dblSum / intElementCount
'At least one number in the group of values
fGetMeanAverage = Null 'No number in the group of values