Excel VBA: Select top k numbers from a range and compute an average

Question

I need to select top k numbers from an Excel range (column) and calculate their mean.

In Excel it looks easy: =AVERAGE(LARGE(<<range>>,SEQUENCE(<<k>>)))

When using a range containing numbers from 1 to 10, and a k of 3, I get a correct result of 9 (an average of 8, 9 and 10).

Where I'm stuck, is trying to do the same in a UDF in Excel VBA. A simple conversion of Excel functions to VBA does not work:

Public Function top_k_average(ByVal rng As Range)
top_k_average = Excel.WorksheetFunction.Average(Excel.WorksheetFunction.Large(rng, Excel.WorksheetFunction.Sequence(3)))
End Function

Using this is an Excel cell returns a #VALUE error =top_k_average(<<range>>)

It looks to me that WorksheetFunction.Large does work with k which is not just a scalar.

Any hints very much appreciated - thanks!

Answer 1

Try this way:

Public Function top_k_average(ByVal rng As Range)
    top_k_average = Excel.WorksheetFunction.Average(Application.Large(rng, Excel.WorksheetFunction.Sequence(3)))
End Function

WorksheetFunction.Large requires single value as a second argument.
But you can use an older version Application.Large instead.