CODEWITHSUNDEEP
excelvba
sayth
sayth

Reputation: 7028

WorksheetFunction.Average returns incorrect answer

Given the data in B2:D10 I wanted to find the average in each row use VBA array formula. Following a book example VBA Queue by Bill Jelen.

Worksheet data

Name    A   B   C   Answer  Manual Avg
A2      4   34  59  31.5    32.33333333
A3      45  83  74  59.5    67.33333333
A4      98  40  17  57.5    51.66666667
A5      95  8   83  89  62
A6      57  55  98  77.5    70
A7      14  21  17  15.5    17.33333333
A8      55  23  4   29.5    27.33333333
A9      80  15  77  78.5    57.33333333
A10     11  24  40  25.5    25

Answer in Column E is from this formula

Sub QuickFillAverage()
Dim myArray As Variant
Dim myCount As Integer

myArray = Worksheets("Sheet1").Range("B2:D10")

For myCount = LBound(myArray) To UBound(myArray)
    Worksheets("Sheet1").Cells(myCount + 1, 5).Value = _
    WorksheetFunction.Average(myArray(myCount, 1), myArray(myCount, 3))
Next myCount

End Sub

The answer in Column F is from

(B2+C2+D2) / 3

Done for each row to find the average. As you can see the answers are different. Why?

Upvotes: 2

Views: 341

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

The reason that the code is producing unexpected results is due to you only averaging two cells: myArray(myCount, 1) and myArray(myCount, 3). You may be thinking of the Range() function that will return all cells between the two range values - but this isn't the case when you use an array.

You will need to include all three values in your average worksheet function:

WorksheetFunction.Average(myArray(myCount, 1), myArray(myCount, 2), myArray(myCount, 3))

Here is a function I created that will accept an array and average the array based on any row number provided (which will be your myCount number):

Function averageArrayRows(arr As Variant, ByVal myRow As Long) As Double

    Dim i As Long, mySum As Double

    For i = LBound(arr, 2) To UBound(arr, 2)
        mySum = mySum + arr(myRow, i)
    Next

    averageArrayRows = mySum / (UBound(arr, 2) - LBound(arr, 2) + 1)

End Function

Which you can use by:

Worksheets("Sheet1").Cells(myCount + 1, 5).Value = averageArrayRows(myArray, myCount)

Upvotes: 4

Related Questions