Table of Contents
A trimmed mean is a type of average that excludes a certain percentage of data from the highest and lowest values. To calculate a trimmed mean in Google Sheets, first, select the range of values to be included in the calculation. Then, use the function AVERAGE.TRIM in the formula bar to specify the percentage of data to exclude from the calculation. The trimmed mean will be automatically calculated and will be displayed in the cell selected.
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 Google Sheets you can use the TRIMMEAN function, which uses the following basic syntax:
TRIMMEAN(data, exclude_proportion)
where:
- data: Range containing the dataset
- exclude_proportion: Proportion 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 Google Sheets
Suppose we have the following dataset in Google Sheets:
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 example, there are 20 total values in the dataset. Thus, 10% of 20 is 2.
So, to calculate a 10% trimmed mean in this example, Google Sheets needs to remove two values from the extremities of the dataset.
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 .
The following tutorials provide additional information about trimmed means: