Analyse a subset of numeric Array only

[Whisky]

Hi

I have an array based on data read in from Excel that I would like to calculate summary stats based on the subset of the data but I am getting an error.

So for example where Y is the array, the following average calculation of the array works fine.

MEAN = Application.WorksheetFunction.Average(y)

Now I would like to run the same calculation based on only observation 66 to 77 of the array and I am trying to use Index based on examples I have found online but it does not work saying "unable to get average of worksheet function class" :

MEAN = Application.WorksheetFunction.Average(Application.WorksheetFunction.Index(y , 1, Evaluate("ROW(66:77)")))

Anyone know why this doesn't work /what am I doing wrong?

Thanks

W

[p45cal]

Could you give us the dimensions of Y please?

[Whisky]

Could you give us the dimensions of Y please?

Sure. Here is the basic sub. Lastrow = B102



Option Base 1
Public Sub example()


Dim y As Variant

With Sheets("Calculation")


lastrow = Cells(5000, 3).End(xlUp).row

y = Range("B5:B" & lastrow)

'MEAN = Application.WorksheetFunction.Average(y)
MEAN = Application.WorksheetFunction.Average(WorksheetFunction.Index(y, 1, Evaluate("ROW(66:77)")))


MsgBox MEAN


End With
End Sub

Thanks

W

[p45cal]

So it's a vertical array; many rows, one column.
so Index(y, 1, Evaluate("ROW(66:77)") would mean row 1, columns 66 to 77. Won't work. You don't have that many columns.
It should be more like: Index(y, Evaluate("ROW(66:77)"),1) meaning rows 66 to 77, column 1

Next, remove the .WorksheetFunction from the Index part, leaving:
MEAN = Application.WorksheetFunction.Average(Application.Index(y, Evaluate("ROW(66:77)"), 1))
can be shortened again by removing the other .WorksheetFunction:
MEAN = Application.Average(Application.Index(y, Evaluate("ROW(66:77)"), 1))
and again:
MEAN = Application.Average(Application.Index(y, [ROW(66:77)], 1))
and again:
MEAN = Application.Average(Application.Index(y, [ROW(66:77)]))

[Whisky]

Thanks P45cal. This makes sense. Sorry I lifted my base example from a random google search and assumed it was correctly referencing rows and not columns! Confirming all of these interations work.

W