---

MS Excel: How to create a custom average function (that excludes outlyers from the calculation)

This Excel tutorial explains how to create a custom average function that excludes outlyers from the calculation (with screenshots and step-by-step instructions).

Question: In Microsoft 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 As Integer
   Dim LLastRow As Integer
   Dim LFirstCol As Integer
   Dim 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.