How and when to use TRIMMEAN function in Excel

TRIMMEAN is used to calculate Mean or Average of data points excluding the outliers or extreme values. Whenever we calculate Mean including outliers, We may get values that are not accurate and does not represent the fact. The reason for this is MEAN tend to get affected by extreme values. One big value in a data set can significantly change the value MEAN. In such cases, We use TRIMMEAN to exclude values from top and bottom tails of the data set.

  Syntax:     

                                          

     "=TRIMMEAN(Array,Percentage)"

TRIMMEAN has two arguments i.e Array and Percentage. Array is a data set for which we have to calculate TRIMMEAN. Percentage is decimal between 0 and 1. Percentage cannot be greater than or equal to 1. If the value of Percentage is 0 then TRIMMEAN = MEAN.

Example:

Consider the below example where MEAN and TRIMMEAN are calculated for 20 random data points. 

As the percentage argument for TRIMMEAN is 0, MEAN=TRIMMEAN which is 39.6.  Now change one of the values say 3rd number to 300. The value of Mean changes to 53. 

Now, change the percentage argument in TRIMMEAN to 0.2 (20%). The TRIMMEAN value changes to 40.69 from 53. In this case TRIMMEAN exclude the 4 values, 2 from top and 2 from bottom (20% of 20 data points) from data set and calculates the average. 

Note: For normally distributed data MEAN = MEDIAN. MEDIAN does not get affected with extreme values. Hence Percentage argument in TRIMMEAN function can be varied to adjust the value of TRIMMEAN approximately equal to MEDIAN. From the below picture it is clear that presence of extreme values in the data set does not affect the value of MEDIAN.