How to Use the TRIMMEAN Function in Google Sheets to Calculate Averages Excluding Statistical Outliers

Standard arithmetic averages calculated with the AVERAGE function in Google Sheets are highly sensitive to extreme outliers. In performance tracking, benchmark testing, customer satisfaction scoring, and financial modelling, a single anomalously high or low value can distort overall trends. The TRIMMEAN function solves this issue by trimming a designated percentage of data points from the dataset extremes before computing the mean.

Syntax and Parameters of the TRIMMEAN Function

The syntax for TRIMMEAN requires two arguments:

=TRIMMEAN(data, percent)

• data: The array or contiguous cell range containing the numeric dataset you wish to average (e.g., B2:B50).
• percent: The fractional portion of data points to exclude from the calculation, expressed as a decimal between 0 and 1 (or as a percentage, such as 0.2 or 20%).

Understanding How Data Trimming Is Calculated

When you supply an exclusion percentage, Google Sheets divides the percentage by two and removes that amount equally from both the top and bottom tails of your sorted dataset:

For example, in a dataset of 20 scores with a 20% trim:

20 items * 0.20 = 4 items excluded total (2 lowest scores and 2 highest scores)

The formula then computes the mean of the remaining 16 central values. Note that Google Sheets automatically rounds down the number of excluded data points to the nearest even integer to guarantee equal exclusions from both extremes.

Step-by-Step Example: Calculating Outlier-Resistant Survey Scores

Suppose you have customer satisfaction ratings recorded in cells A2:A21 representing 20 responses ranging from 1 to 100, where two disgruntled users gave scores of 1 and two mistaken entries entered 100.

To compute a 20% trimmed mean that discards these anomalies:

=TRIMMEAN(A2:A21, 0.2)

Comparing this with standard formulas illustrates the value of trimming:

• =AVERAGE(A2:A21) might return 68.5, biased downwards by extreme low ratings.
• =TRIMMEAN(A2:A21, 0.2) returns 74.2, offering a far more accurate representation of true customer sentiment.

Trimming Olympic-Style Scoring (Extreme Outlier Removal)

In competitive judging (such as gymnastics or diving), standard rules dictate dropping only the single highest and single lowest score. To replicate this dynamically for any dataset size:

If column B has 10 scores (cells B2:B11) and you want to drop exactly 2 scores (the minimum and maximum), set your trim percentage to 2 / COUNT(B2:B11):

=TRIMMEAN(B2:B11, 2 / COUNT(B2:B11))

This dynamic formula automatically adjusts the percentage parameter, ensuring exactly one top score and one bottom score are discarded regardless of row count.

Best Practices and Common Mistakes to Avoid

• Zero Percentage: Entering =TRIMMEAN(data, 0) returns the exact same result as a standard =AVERAGE(data).
• Values Outside Range: Providing a percentage less than 0 or greater than or equal to 1 generates a #NUM! calculation error.
• Text Ignored: Cells containing text strings within the data range are automatically ignored when computing counts and means.