Find the values after excluding outliers
The Excel TRIMMEAN function returns an average (arithmetic mean) after excluding a given percentage i.e. remove from the high and low numbers in a given data set.
= TRIMMEAN(array, percent)
Array – the range of cells to average
Percentage – the percentage of values to exclude from the given data array. The percentage can be given either in decimal format or percentage format.
Average of numbers after excluding percentage from the given array.
- The TRIMMEAN function calculates the average (arithmetic mean) of numbers in the range after excluding certain numbers.
- To make the function effective minimum of 10 and 20 data points is required to calculate 20% and 40%, respectively.
- Use AVERAGEIF and AVERAGEIFS to calculate the average for the cells specified by one or multiple conditions.
- To know how many numbers from the top and bottom will be removed in an array is always divided by 2.
- Function ignore the highest and lowest number in the data set be default.
Example (Image Above)
Suppose that a list of digits is in a column and user want to find the average of numerical values after excluding certain numbers, the formula can be =TRIMMEAN(B5:B15,20%).
It first counts array (B5 to B15) i.e. 11 then it excluded 20% of array(11) i.e. 2.2 then it divides with 2 gives 1.1 so largest (15) and smallest (4) number in the range will not be accounted. Therefore the function returns an average 8.89 after excluding top and bottom numbers.