TRIMMEAN

Returns the mean of a set of numbers, ignoring a proportion of high and low values.

Syntax:

TRIMMEAN(numberlist, fraction)

numberlist is a range or array of numbers, not necessarily in order. TRIMMEAN returns the mean, ignoring a fraction (0 <= fraction < 1) of the outermost values in numberlist.

Specifically, where n is the count of numbers in numberlist, the highest and lowest INT(n * fraction / 2) values are ignored.

Example:

TRIMMEAN(A1:A100, 0.1)

returns the mean of the values in cells A1:A100, ignoring the lowest 5 values and the highest 5 values.

Application:

Analyzing Student Test Scores

A teacher wants to find the average test score for her class, but she's concerned that a few exceptionally high or low scores might skew the results. To get a more representative average, she decides to use the TRIMMEAN function to calculate the mean of the scores after removing the top and bottom 20% of the values.

Student Test Scores (out of 100):

	Student	Score
	A	B
1	Alice	98
2	Bob	85
3	Charlie	72
4	David	65
5	Eve	95
6	Frank	88
7	Grace	78
8	Henry	50
9	Irene	92
10	Jack	80

Using the TRIMMEAN function:

The TRIMMEAN function in a spreadsheet would be written as:

TRIMMEAN(A1:A10, 0.2)

Where:

A1:A10 is the range of cells containing the test scores.
0.2 is the proportion of data points to be excluded from the top and bottom of the dataset (20%).

Calculation Breakdown:

Number of scores: 10
Total number of values to trim: 10×0.2=2
Number of values to trim from each end: 2/2=1
Sorted Scores: 50, 65, 72, 78, 80, 85, 88, 92, 95, 98
Values to be ignored (trimmed): The lowest scores (50) and the highest score (98).
Remaining scores to be averaged: 65, 72, 78, 80, 85, 88, 92, 95
TRIMMEAN Calculation: (65 + 72+78+80+85+88+92 + 95)/8=655/8=81.875

Result:

The TRIMMEAN score is 81.875. This is a more accurate representation of the class's average performance because it is not influenced by the outlier scores of the student who scored 50 and the student who scored 98. In contrast, the simple arithmetic mean of all 10 scores is 80.3, which is lower due to the low outlier.

Result for TRIMMEAN:

81.875

Result for mean:

80.3