# Thread: Averaging Across Columns (XP)

1. ## 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. ## 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. ## 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
•