How to Calculate a Trimmed Mean in Google Sheets


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.

This means Google Sheets 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 provide additional information about trimmed means:

How to Calculate a Trimmed Mean by Hand
How to Calculate a Trimmed Mean in Python
Trimmed Mean Calculator

Leave a Reply

Your email address will not be published.