Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003

    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

    as suggested by another thread, but it doesn't work. Can someone please help?

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    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:;en-us;209857

  3. #3
    Join Date
    Oct 2003
    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
    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