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!
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.