Average without High and Low Value

In the article (& Video), we will go through five examples of how to remove selected numbers (Largest and Smallest), zero, or negative numbers while calculating averages in Excel.

Finding average after ignoring one or multiple deviations or exception numbers. In case you want to calculate the mean of range after excluding values that could be max, mins, blank, or zero values of a given list.

Download Your Example Excel File

Exclude percent of Top and Bottom:

We have two columns, Employee and Sales. Our task is to get the average of sales after excluding the top and bottom 10 or 20 percent of the data set.

Formula to implement:

=TRIMMEAN($C$3:$C$22,20%)

Let’s understand the  formula:

The Excel TRIMMEAN function returns an average (arithmetic mean) after excluding a given percentage i.e. remove from the highest and lowest numbers of a data set.

Formula Explanation (Step by Step)

You can check the formula easily with the shortcut F9 function key.

Let’s break the formula into multiple small steps to understand its application.

Step 1: =TRIMMEAN($C$3:$C$22)

Result: 20

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

Step 2: =TRIMMEAN(20,20%)

Result: 4.0

So, 4 numbers will get deducted two largest and two smallest (* only consider the first stance if values occurrence is multiple times)

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

Result: 464/16=29

The addition of all the 20 numbers is ‘614’. In this formula, Excel would account for 16 numbers and the sum of the range would be 464 (excluding ‘4+6′ & ’82+58’).  

top bottom percent
Average after excluding high and low values
  • Don’t worry if the same number comes multiple times in the range Excel trim only the first occurrence in the range.
  • The TRIMMEAN ignores the values which come after the decimal place, suppose the total number in the range is 13 so Excel will trim only 6 numbers (3 top and 3 bottoms) ignoring the remainder 13/2=6′.5′.

Average except Zero and Negative

In case you want to exclude Zero and Negative numbers from then we can look out for the following example:

In this case, we have Employees’ name and their performance over and under sales targets. You can also calculate the average based on similar names.

And we want to calculate average excluding Zero, so we will add ‘not equal to’ operator in the AVERAGEIF formula and likewise greater than (>) sign if we’re going to exclude zero and negative numbers both. In case 

=AVERAGEIF(D4:D23,”<>0″)

=AVERAGEIF(D4:D23,”>0″)

Average except Zero and Negative
Average a range of data ignoring zero or negative with AVERAGEIF

Average except Max and Min

What if I want to exclude only two numbers, only the biggest and minimal ones?

In our third example, we want to exclude the greatest and the smallest in the range. So, in this case, we would utilize three Excel functions, the AVERAGEIFS function, and exclude with MAX function and MIN function by putting a clear sign of ‘more than’ and ‘less than’ operators in the formula.

Besides, if you want an Average that only includes the maximum and minimum values and excludes the rest of the numbers in the range, you can use the Average of both Max and MIN values, which will account for only two digits.

=AVERAGEIFS(C4:C23,C4:C23,”<“&MAX(C4:C23),C4:C23,”>”&MIN(C4:C23))

Besides, if you want an Average that only includes the maximum and minimum values and excludes the rest of the numbers in the range, you can use the Average of both Max and MIN values, which will account for only two digits.

=AVERAGE(MAX(C4:C23),MIN(C4:C23))

Average exclude only the biggest and minimal
Ignore largest and smallest to calculate average

Average within two numbers

Let’s check out the following example to calculate the average between a range of values. In this case, we will be using the AVERAGEIFS function to average only between a range of numbers, exclude multiple values at once. Also, learn to calculate the average between two dates.

So, in our example, the lowest is 8, and 45 is the highest value. Thus, Excel will return an average of only those in between these two numbers. Remember, we have here greater than and smaller than operators.

So, Excel does not include any number between 8 and 45 in this average calculation.

=AVERAGEIFS(C4:C23,C4:C23,”<“&MAX(C4:C23),C4:C23,”>”&MIN(C4:C23))

Average in Between a range of data set
Average within a range of between two numbers

Average based on Nth term

There could be a scenario where we want to remove or allow from nth small to an nth large number in a data set for average calculation.

Suppose we want to include from 4th bottom numbers to the most significant 3rd number of a given range. So, we will use Excel’s SMALL and LARGE functions to find out numbers in a dataset. Both formulas will get a value at the Nth place in a sequence.

=SMALL(C4:C23,5)

=LARGE(C4:C23,5)

=AVERAGEIF(C4:C23,”>”&F4)

=AVERAGEIF(C4:C23,”<“&F5)

Average based on rank in a list
Average based on rank in a list

Change sign if the opposite is your requirements.

Note: If you want to do this in one formula, you can use the AVERAGEIFS function, as discussed in the previous example.