After reading this article you will be able to get average after excluding 0, blank cells or certain numbers.
To get average after excluding outliers. In case you want to calculate average after excluding some maximum, minimum, blank or 0 values in the range.
We have two columns, Employee and Sales. Our task is to get the average of sales after excluding the top and bottom 20 percent of the data set.
Formula to implement:
Download Your Example Excel File
For this, we will use the TRIMMEAN formula in Excel.
The Excel TRIMMEAN function returns an average (arithmetic mean) after excluding a given percentage i.e. remove from the high and low numbers of a data set.
Syntax: =TRIMMEAN(array, percent)
Array – the range of cells to average
Percentage – the percentage of values to exclude from the given data array. Input can be either in decimal format or percentage format.
Formula Explanation (Step by Step)
You can also debug formula using shortcut F9 function key under formulas tab.
Let’s break the formula into multiple small steps to understand its application.
Step 1: =TRIMMEAN($C$3:$C$13)
In the first step, it counts all numbers in column C.
Step 2: =TRIMMEAN(11,20%)
In first step it counts now in second step it returns 20% of 11 (counts).
Step 3: =TRIMMEAN($C$3:$C$13,20%)
Now step 2 result (2.2) divides by 2 and returns 1.1. So, a number which is highest and a number which is lowest in the range (C3 to C13) will eliminate and returns Average.
- In TRIMMEAN when Excel is calculating how many numbers to be removed in the given range the calculated percentage is always divided by 2 i.e. 2.2/2.
- Ignore the values which come after decimal place in TRIMMEAN suppose when calculated percentage is divided by 2 and gives 2.6 or 1.1, so Excel exclude only numbers count before decimal points like 2 and 1 in this case.