ADVERTISEMENT

MS Excel: Creating a custom average function (that excludes outlyers from the calculation)

---

Question:  In Excel, how can you average a column of numbers, excluding one or more cells that are outlyers that shouldn't be averaged?

For example, if I wanted to average the following numbers, but exclude 4527 from the average calculation because it is a value that is less than 5000.

8462
7153
4527
8019
8310
8176
8328
8383
7810
8555
8950
6760
8199

Answer:  To accomplish this, you need to create a custom function.

You'll need to open your Excel spreadsheet. Press Alt-F11 and create a new module.

Then paste into the new module the following function:

Function CustomAverage(pRange As Range, pThreshold As Long) As Long

    Dim LFirstRow, LLastRow As Integer
    Dim LFirstCol, LLastCol As Integer

    Dim LCurrentRow As Integer
    Dim LCurrentCol As Integer

    Dim LTotal As Double
    Dim LCount As Integer

    On Error GoTo Err_Execute

    'Determine first and last row to average
    LFirstRow = pRange.Row
    LLastRow = LFirstRow + pRange.Rows.Count - 1

    'Determine first and last column to average
    LFirstCol = pRange.Column
    LLastCol = LFirstCol + pRange.Columns.Count - 1

    'Initialize variables
    LTotal = 0
    LCount = 0

    'Move through each cell in the range and include in the average
    ' calculation if the value >= pThreshold
    For LCurrentCol = LFirstCol To LLastCol
        For LCurrentRow = LFirstRow To LLastRow
            If Cells(LCurrentRow, LCurrentCol) >= pThreshold Then
                LTotal = LTotal + Cells(LCurrentRow, LCurrentCol)
                LCount = LCount + 1
            End If
        Next
    Next

    'Return the average
    CustomAverage = LTotal / LCount

    On Error GoTo 0

    Exit Function

Err_Execute:
    CustomAverage = 0
    MsgBox "An error occurred while calculating the Custom Average."

End Function

Now, when you want to average your values, you can use the CustomAverage function as follows:

=CustomAverage(A2:A14,5000)

This would return the average of the values in cells A1 through A14, but exclude any values that are below 5000. So in this example, it would exclude 4527 from the average calculation.