AVERAGETOP

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.


REMARKS
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


Example


 

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

Top

PrevNext