There are 2 functions for getting the average.


* For instructions on how to add a function to a workbook refer to the page under Inserting Functions


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

'rgeAverageRange - The range of values you want to average.
'iTop - The value indicating the top number.

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

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

microsoft excel docs


Returns the average of the visible, non blank cells, only considering the top "x" percent.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext