A trimmed mean is the mean of a dataset that has been calculated after removing a specific percentage of the smallest and largest values from the dataset.
For example, a 10% trimmed mean would represent the mean of a dataset after the 10% of values from the extremities of the dataset have been removed.
To calculate a trimmed mean in Excel you can use the TRIMMEAN function, which uses the following basic syntax:
TRIMMEAN(array, percent)
where:
- array: Range containing the dataset
- percent: Percent of data to exclude (between 0 and 1)
The following example shows how to use this function to calculate a trimmed mean in practice.
Example: Calculate Trimmed Mean in Excel
Suppose we have the following dataset in Excel:
We can use the following formula to calculate a 10% trimmed mean for this dataset:
TRIMMEAN(A2:A21, 0.1)
The following screenshot shows how to use this formula in practice:
The 10% trimmed mean of the dataset is 7.61.
In this particular dataset there are 20 total values. Thus, 10% of 20 is 2.
So, to calculate a 10% trimmed mean in this example Excel must remove two values from the extremities of the dataset.
This means Excel removed the one smallest value (2) and one largest value (15) from the dataset and then calculated the mean.
We can confirm the formula is correct by manually calculating this trimmed mean ourselves:
10% Trimmed Mean: (2+3+3+4+5+6+7+7+7+7+8+8+9+10+11+12+14+14) / 18 = 7.61.
Note that in this particular example, the values that were removed from the extremities were clearly not outliers.
However, a trimmed mean is most useful in practice when there are extreme outliers in the dataset and you’d like to calculate the mean value without allowing these outliers to influence the calculation.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Conditional Mean in Excel
How to Calculate the Standard Error of the Mean in Excel
How to Calculate Mean Absolute Percentage Error (MAPE) in Excel