Result of VBA code Worksheetfunction.Average differs from the result got by Excel +SUM(range) function.
I've put up the VBA script as below in order to calculate the occurences of specific values in a range, named "tart", and then to calculate the relevant average, maximum and minimum values :
In order to double check the average value generated I've made use of Excel's SUM()/35 function. I expected identical average values, however the two methods brought diverse results.
Also the Max and Min value in the specified range is actually incorrect
Sub freq()
Worksheets("feldolg").Activate
Dim tart As Range
Set tart = Range("B2:H1222")
Dim nums As Byte
nums = InputBox("blabla ?", "blabla")
For szam = 1 To nums
Cells(2, 11).Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
Cells(2, 11).Offset(0, nums + 1) = WorksheetFunction.Average(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1))
Cells(2, 11).Offset(0, nums + 3) = WorksheetFunction.Max(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1))
Cells(2, 11).Offset(0, nums + 5) = WorksheetFunction.Min(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1))
Next szam
'MsgBox Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)).Cells.Count
'MsgBox Cells(2, 11).Offset(0, nums - 1).Address
End Sub
The code parameters have been checked several times and to me they seem to be correct. What did I do wrong ?
You aren't giving a range to Average, Max or Min, you're giving it two cells. It isn't searching between the cells, its just looking at those two cells. You probably meant to have those two cells as arguments in Range() like Range(Cells(...), Cells(...))
Cells(2, 11).Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
Cells(2, 11).Offset(0, nums + 1) = WorksheetFunction.Average(Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)))
Cells(2, 11).Offset(0, nums + 3) = WorksheetFunction.Max(Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)))
Cells(2, 11).Offset(0, nums + 5) = WorksheetFunction.Min(Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)))
I suggest cleaning up the code by using variables
Dim c As Range: Set c = Cells(2, 11)
Dim data As Range: Set data = c.resize(1, nums)
c.Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
c.Offset(0, nums + 1) = WorksheetFunction.Average(data)
c.Offset(0, nums + 3) = WorksheetFunction.Max(data)
c.Offset(0, nums + 5) = WorksheetFunction.Min(data)