Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    Washington, District Of Columbia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging Across Columns (XP)

    To Interested Loungers:

    If we have three columns of numeric data, what would be the easiest way to correctly compute the average of the three columns given some records would contain null values?

    Reminder:
    If A = 1, B = 2, C = null, D = 0, then

    avg(A, B, C) = 1.5
    avg(A, B, D) = 1

    Gratefully yours,

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Averaging Across Columns (XP)

    You will probably need a custom function for this. Example:

    Function GetAvg(ParamArray varArgs()) As Variant

    Dim dblSum As Double
    Dim lngCount As Long
    Dim varArg As Variant

    For Each varArg In varArgs
    If IsNumeric(varArg) Then
    dblSum = dblSum + varArg
    lngCount = lngCount + 1
    End If
    Next

    If lngCount > 0 Then
    GetAvg= dblSum / lngCount
    Else
    GetAvg= Null
    End If

    End Function

    For more examples using similar method, see MSKB 209839:

    ACC2000: How to Calculate Row-Level Statistics

    HTH

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Location
    Washington, District Of Columbia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Across Columns (XP)

    Mark,

    Thank you for the guidance. Needless to say, it worked. Also, we found the reference document you provided useful.

    Best Regards,

Posting Permissions

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