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:
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.