You have a sales report, and you need to count an average. That’s easy. However, there is one exceptionally high value in the report, 200. That is not a normal value, therefore the average is higher than normal, because of one high sales figure.

One option is to exclude extreme values in the bottom and in the top. The very high sales values will not be counted but also the low values are not considered, either.

Instead of AVERAGE function use TRIMMEAN.

Here is a sales report. As you can see, the last value is considerably higher than other values.

The average for this report is 61.

We would like to exclude the lowest and highest 10 % from the report, then the 200 value would not be counted. Also, the lowest values would also be left out.

The function TRIMMEAN is simple, there are only two arguments data array and percentage.

When we leave the highest 10 % and the lowest 10 % out of average, the trimmed average is 56,1. That is lower than 61, the traditional average. Leaving out the highest and lowest 10 %, is lowering the average because of 200 value.

We can check manually the TRIMMEAN. We need to count 10 % limits manually. Which values TRIMMEAN is excluding from the data range.

The old PERCENTILE function does not exist anymore, now we have PERCENTILE.INC and PERCENTILE.EXC. Both the functions have two arguments: array and percentile. If we want the lowest 10 %, then the percentile is 0,1. For the highest 10 %, the percentile is 0,9.

Now we have percentile limits. If we use PERCENTILE.EXC values, we exclude the values less than 23,3 and higher than 88,5.

Percentile means that below 23,3 are 10 % of observations and above 88,5 are also 10 % of observations. When we count average for sales between the percentiles, that is what TRIMMEAN is doing, I suppose.

Now we need to mark the values in the middle and leave percentile values out. We need an IF sentence which checks that the value is higher than lower percentile limit and lower than high percentile limit. Then the average is calculated only for the values with X.

We take PERCENTILE.EXC values as benchmark, the X should be next to the values which are higher than 23,3 and lower than 88,5.

Now we are counting average for the values with X next to the numeric value. Then we are counting the average for the values when highest and lowest 10 % are excluded. The expected result is same as with TRIMMEAN.

The average for selected values we can do at least in two different ways.

=AVERAGE(IF(C3:C24=”X”;B3:B24))

When using traditional AVERAGE, we need to have nested IF sentence to check where X is set and calculate the average with X marked values.

=AVERAGEIF(C3:C24;”X”;B3:B24)

Excel also has a modern AVERAGEIF function, where IF is embedded and nested IF is not needed. The only arguments area range for criteria, criteria, and average values.

AVERAGEIF is simpler than AVERAGE with nested IF, but both the formulas calculate the same result.

Let’s see do we have the same results with TRIMMEAN and AVERAGE(IF) with PERCENTILE.EXC/INC.

In B6 we have TRIMMEAN for data range 56,1.

In C and D columns we have X if the value is not in highest or lowest percentile. The sentence in C3 =IF(AND(B3>$F$3;B3<$F$4);”X”;””) and in D3 =IF(AND(B3>$G$3;B3<$G$4);”X”;””). In C column X is marked if the value in between F3 and F4. Those values are counted with PERCENTILE.EXC function. In D column we have X if the values are between G3 and G4. In turn, G3 and G4 are calculated with PERCENTILE.INC.

Once the high and low percentiles are marked without X, we can calculate the averages for the values with X. AVERAGEIF and AVERAGE+ nested IF are returning the same values. There is no difference between these two options.

TRIMMEAN returns the value 56,1. The average for PERCENTILE.EXC is roughly 56,06 and for PERCENTILE.INC is 56,25. Differences between averages are minor. PERCENTILE.EXC is closer to TRIMMEAN than PERCENTILE.INC.  No matter which way you calculated the average for selected values, the results are nearly identical. TRIMMEAN is the simplest option.