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

**QUERY**

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

**=TRIMMEAN($C$3:$C$13,20%)**

## Download Your Example Excel File

**Function applicable:**

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)

**Result:** 11

In the first step, it counts all numbers in column C.

Step 2: =TRIMMEAN(11,20%)

**Result:** 2.2

In first step it counts now in second step it returns 20% of 11 *(counts).*

Step 3: =TRIMMEAN($C$3:$C$13,20%)

**Result:** 184.4

(163+200+232+243+136+192+147+150+197) =1660

(1660/9) =184.4

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.