Excel TRIMMEAN Function
Excel TRIMMEAN function calculates trimmed mean of supplied set of data values by excluding a percentage of data points from the top and bottom tails of a data set. It returns mean of the interior portion of a set of data values.
Excel TRIMMEAN function syntax has the following arguments:
Array (required): It is an array or range of numeric values for which you want to calculate the trimmed mean.
Percent (required): The percentage of values that need to be discarded from the supplied set of data values in array or range.
Trimmed mean is just like standard mean or average of supplied values. However the trimmed mean is calculated by discarding or truncating a certain percentage of the top and bottom values, also known as an outlier, of provided set of values. Excel TRIMMEAN function basically calculates the inner tendency or mean without being distracted by extreme or outlier values in the dataset.
Specified percentage value in percent argument of Excel TRIMMEAN function is total percentage of values to be excluded from the dataset before calculating mean. This percentage of total values in the range is rounded down to the nearest multiple of 2 and then divided by 2 to get the number of values discarded by either side of the supplied array.
If the supplied array has contains 10 values for which you want to calculate trimmed mean by using Excel TRIMMEAN function, and you need to know what percent value will exclude how many values from each side of the array, then:
- 15% of 10 values is 1.5 values, which will be rounded down to the nearest multiple of 2 to 0. So no value will be excluded from the dataset.
- 25% of 10 values is 2.5 values, which will be rounded down to the nearest multiple of 2 to 2. This rounded down value of 2 is again divided by 2, so 1 value will be excluded by either side of the array, 1 top, and 1 bottom value.
Suppose you have a set of values and you want to calculate the mean of the interior portion by excluding 20% of values from the top and bottom tails of provided array. You need to apply the following formula by using Excel TRIMMEAN function:
As there are 11 values in the array, so the 20% of 11 values is 2.2 values, which will be rounded down to 2 (nearest multiple of 2), and then divided by 2. So, 1 top value and 1 bottom value will be excluded to get the trimmed mean of values as shown in the table below.
Figure 1. TRIMMEAN_Function
Please note that if percent argument is < 0 or > 1, Excel TRIMMEAN function returns #NUM! error value.