Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

TRIMMEAN

Trimmean Function
Average after excluding outliers

Rational

Find the values after excluding outliers

Description

The Excel TRIMMEAN function returns an average (arithmetic mean) after excluding a given percentage i.e. remove from the high and low numbers in a given data set.

Syntax

= TRIMMEAN(array, percent)

Parameters

Array – the range of cells to average
Percentage – the percentage of values to exclude from the given data array. The percentage can be given either in decimal format or percentage format.

Return Value

Average of numbers after excluding percentage from the given array.

Key Notes

  • The TRIMMEAN function calculates the average (arithmetic mean) of numbers in the range after excluding certain numbers.
  • To make the function effective minimum of 10 and 20 data points is required to calculate 20% and 40%, respectively.
  • Use AVERAGEIF and AVERAGEIFS to calculate the average for the cells specified by one or multiple conditions.
  • To know how many numbers from the top and bottom will be removed in an array is always divided by 2.
  • Function ignore the highest and lowest number in the data set be default.

Example (Image Above)

Suppose that a list of digits is in a column and user want to find the average of numerical values after excluding certain numbers, the formula can be =TRIMMEAN(B5:B15,20%).

It first counts array (B5 to B15) i.e. 11 then it excluded 20% of array(11)  i.e. 2.2 then it divides with 2 gives 1.1 so largest (15) and smallest (4) number in the range will not be accounted. Therefore the function returns an average 8.89 after excluding top and bottom numbers.

Keep Reading Similar Functions:

Count reference cells based on criteria in Excel

COUNTIF

The COUNTIF function returns a count of number based on given criteria like =COUNTIF(A1:A5,”>5″) that count numbers in the list >5 and returns 3.

Read More »
Find the average of numbers in Excel

AVERAGEIF

The AVERAGEIF function returns an average figure based on given criteria like =AVERAGEIF({6;8;10;3;5},”>5″) average numbers that are more than 5 and return 8.

Read More »
Find the average of numbers in Excel

AVERAGE

The AVERAGE function returns an average of numbers positioned in one or multiple ranges, =AVERAGE(5,15,30) add numbers and divide by 3, and returns 16.67.

Read More »
Find the average of a cell range based on multiple criteria in Excel

AVERAGEIFS

The AVERAGEIFS function returns an average number based on one or multiple given criteria, sum adjacent cells that meet given conditions.

Read More »