How to Use the TRIMMEAN Function in Google Sheets to Exclude Outliers from Averages

Calculating a standard arithmetic mean using AVERAGE in Google Sheets can distort your insights when a dataset contains extreme anomalies, data entry typos, or atypical statistical spikes. The TRIMMEAN function solves this problem by calculating the mean of an interior dataset, systematically trimming a specified percentage of data points from the top and bottom tails of your distribution.

What is TRIMMEAN and How Does It Work?

In statistics, a trimmed mean (or truncated mean) offers a robust measure of central tendency. Unlike the standard median—which only evaluates the single central data point—or the traditional arithmetic mean—which treats all values equally—TRIMMEAN discards extreme upper and lower percentiles and averages the remaining stable data.

Common applications include:

• Performance and sales analytics: Removing extraordinary one-off enterprise deals or zero-activity accounts to evaluate typical team output.
• Scientific experiments and sensor logs: Discarding transient voltage spikes or calibration glitches from laboratory readings.
• Scoring competitions: Emulating Olympic scoring rules where the highest and lowest judge marks are dropped before averaging.

TRIMMEAN Formula Syntax and Arguments

The syntax for the function in Google Sheets is defined as follows:

=TRIMMEAN(data, percent)

Argument	Requirement	Description
data	Required	The array or cell range containing the numerical values to analyse (e.g., A2:A50).
percent	Required	The fractional portion of data points to exclude, expressed between 0 and 1 (e.g., 0.2 for 20%).

Understanding Tail Trimming Calculations

When you supply a percent value, Google Sheets rounds the number of excluded data points down to the nearest multiple of 2. Half of the excluded count is removed from the minimum values, and the other half is removed from the maximum values:

• Suppose your dataset has 20 numbers and you specify 0.2 (20%).
• Total items to exclude: 20 * 0.2 = 4.
• Google Sheets removes the 2 lowest numbers and the 2 highest numbers.
• The mean is calculated across the remaining 16 values.

Step-by-Step Practical Examples in Google Sheets

Example 1: Trimming 20% Outliers from Employee Support Response Times

Imagine column B contains customer support resolution times in minutes (cells B2:B21, 20 entries). A few technical outages caused response times of 300 minutes, while automated bots resolved two tickets in 0.1 minutes. To remove these four skewing entries:

=TRIMMEAN(B2:B21, 0.2)

Alternatively, express the percentage using percent notation:

=TRIMMEAN(B2:B21, 20%)

Example 2: Olympic Style Judging (Dropping Highest and Lowest Marks)

If you have 10 judge scores in range C2:C11 and want to drop exactly 1 lowest score and 1 highest score (2 scores total out of 10, or 20%):

=TRIMMEAN(C2:C11, 2 / COUNT(C2:C11))

Using 2 / COUNT(...) dynamically ensures that exactly two observations are discarded regardless of how many scores are entered.

Example 3: Dynamic Outlier Trimming with Cell References

Rather than hardcoding percentage values, reference an external control cell (such as E1 formatted as a percentage slider or input):

=TRIMMEAN(A2:A100, $E$1)

Comparing AVERAGE, MEDIAN, and TRIMMEAN

Function	Formula Syntax	Sensitivity to Extreme Spikes
AVERAGE	=AVERAGE(A2:A20)	High (vulnerable to severe skewing by single outlier).
MEDIAN	=MEDIAN(A2:A20)	Zero (ignores magnitude, reports only the centre value).
TRIMMEAN	=TRIMMEAN(A2:A20, 0.1)	Balanced (averages bulk data while stripping tail noise).

Troubleshooting Common TRIMMEAN Errors

• #NUM! Error: Occurs if percent < 0 or percent >= 1. Ensure your percentage argument falls strictly between 0 and 0.999. Setting percent = 1 triggers an error because 100% of data points cannot be discarded.
• #VALUE! Error: Displayed if non-numeric text strings are passed into the percent argument instead of a decimal or percentage literal.
• Unchanged result compared to AVERAGE: If your sample size is small and your percentage is too low, the calculated excluded count may round down to 0 (for instance, 10 items with 10% trimming yields 10 * 0.1 = 1, which rounds down to 0). Increase the percentage or supply more data points.