How to Use the TRIMMEAN Function in Google Sheets

Published on

To exclude outliers in the average calculation, use the function TRIMMEAN instead of AVERAGE. The TRIMMEAN function in Google Sheets returns the mean (average) of a dataset excluding some user-specified proportion of data.

The proportion of the dataset in this function can be specified either in percentages like 10%, 50%, etc. or in equivalent numbers like 0.1, 0.5, etc.

The TRIMMEAN will exclude values from high and low ends of the dataset (values much higher than or smaller than most of the values in the dataset) based on this percentage.

Why should we use the TRIMMEAN function instead of the AVERAGE function in Google Sheets? Can you provide one example?

Assume you have a dataset like this in Docs Sheets and want to calculate the average.

TRIMMEAN Function in Google Sheets - How To

If you use the AVERAGE function the outliers 2200 and 12 will make a huge impact on the average quantity sold. Because these two values lie outside most of the other values in the range C2:C11.

Please do note that the mean (average) is the sum/count. The average sales of the above 10 products are 522.6.

=average(C2:C11)

If you remove the outliers, then you will get the average value, i.e. 376.75.

In a smaller dataset or you can say array/range, you can manually exclude the outliers as above, but not possible in a larger dataset. Here comes the use of TRIMMEAN in Google Sheets.

TRIMMEAN – Syntax and Arguments

Syntax:

TRIMMEAN(data, exclude_proportion)

Arguments:

data – The range/array containing the dataset to consider.

exclude_proportion – The proportion of the dataset to exclude from the top and tail (or you can say to exclude from the extremities of the set).

This proportion must be greater than or equal to 0 (or 0%) and less than 1 (or 100%). When specifying the proportion in the TRIMMEAN function please keep remembering this.

Smaller the data size, larger the proportion

Example Formula:

=TRIMMEAN(C2:C11,0.2)
or
=TRIMMEAN(C2:C11,20%)

Result: 376.75

The TRIMMEAN formula excludes the outliers 2200 and 12 from the average calculation.

Conditional TRIMMEAN in Google Sheets

For conditional TRIMMEAN in Google Sheets, you can use the FILTER function to filter the ‘data’. You can also use Query instead of FILTER.

Here is one example of conditional TRIMMEAN in Google Sheets. To further deepen the use of condition, do master the Filter function.

Must Check: Google Sheets Function Guide.

=trimmean(filter(C2:C11,B2:B11<>"Product 1"),10%)

This Google Sheets TRIMMEAN is an example of conditional average calculation excluding outliers.

=trimmean(query(B2:C11,"Select C where B <>'Product 1'"),10%)

The above formulas find the TRIMMEAN excluding the product “Product 1” from the dataset. That means I have used one criterion in the calculation.

If you know the use of the above two data manipulation functions (Query and Filter), then you can include more conditions easily in the average calculations excluding outliers.

Related Reading:

  1. Average IF: Find Average Based on Condition in Google Sheets.
  2. Averageifs Multiple Criteria Function in Google Sheets.
  3. AVERAGE.WEIGHTED: Calculate the Weighted Average in Google Sheets.
  4. DAVERAGE Function in Google Sheets – Formula Examples.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.