Related Function:

The TRIMMEAN function returns the mean of the interior of a data set.

Syntax

=TRIMMEAN(array,percent)

Arguments

Argument Description
array The array or range of values to trim and average
percent The fractional number of data points to exclude from the calculation

Note: The specified percent is the total percentage of values to be excluded. This percentage is divided by two, to get the number of values that are removed from each end of the range.

When calculating how many values to discard from the supplied array of values, the calculated percentage is rounded down to the nearest multiple of 2. So, if you want to calculate the trimmed mean of an array of 10 values, then:

  • A percentage of 15%, is 1.5 values, which will be rounded down to 0, i.e. no values are discarded from the array
  • A percentage of 20%, is 2 values, so 1 value will be discarded from each end of the array

Examples

  A B C D
1 Data      
2 18      
3 14      
4 20      
5 6      
6 2      
7 12      
8 6      
9 4      
10 9      
11 14      
12        
13 Formula Result Notes
14 =TRIMMEAN(A2:A11,0.2) 10.35 Mean of the interior of the data set contained in A2:A11, with 20 percent excluded from calculation – the values 2 and 20 are excluded when calculating the mean

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the percent argument cannot be interpreted as a numeric value
#NUM! Occurs if the supplied percent argument is < 0 or ≥ 1

The truncated mean is a measure of mean that indicates the central tendancy of a set of values. The truncated mean is calculated by discarding some of the values at either end of the range of values, before calculating the arithmetic mean of the remaining values. This prevents the calculated mean from being skewed by extreme values, known as outliers.

See Wikipedia for more information on truncated mean.