How to calculate Average Excluding Outlayers from the Calculation – VBA Macros

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.

4024, 10554, 9295, 11343, 8056, 8546, 4661, 6374, 5098, 6082, 9288, 14648, 5007

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 8572 from the average calculation.

Courtesy: techonthenet.com