Returns the average from a range of values only considering the top "x" values

rgeAverageRangeThe range of values you want to average.
iTopThe value indicating the top number.

If iTop is greater than the number of items then 0 is returned.

Option Explicit 

Public Function AVERAGE_TOP(ByVal rgeCriteria As Range, _
                            ByVal iTop As Integer) As Single

Dim inumber As Integer
Dim sngAverage As Single

    Call Application.Volatile(True)
    If (iTop > rgeCriteria.Cells.Count) Then
        AVERAGE_TOP = 0
        Exit Function
    End If
    sngAverage = 0
    For inumber = 1 To iTop
        sngAverage = sngAverage + Application.WorksheetFunction.Large(rgeCriteria, inumber)
    Next inumber
    AVERAGE_TOP = sngAverage / iTop
End Function



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited