|
<< Click to Display Table of Contents >> TRIMMEAN (mean without marginal values) |
Syntax:
TRIMMEAN(Area, Percentage)
Description:
This returns the mean of the values in the middle of a data set (by excluding the specified percentage of extreme values).
"Extreme values" are the smallest and largest values of a data set. These are often "outliers" (unusually low or high values, for example, due to a measurement error).
Area is the cell range or array to evaluate.
Percentage is the percentage of values to be excluded.
If, for example, 100 values are given and Percentage is set to 10%, 10 values will be excluded: the 5 lowest and the 5 highest.
Note: For symmetrical purposes, the number of excluded values will automatically be rounded down to a multiple of 2. If, for example, 30 values are given and Percentage is set to 10%, only 2 values will be excluded instead of 3.
Example:
Cells A1:A10 contain the following values: 43, 45, 42, 0, 44, 45, 42, 1234, 40, 41
As you can see, two values are exceptionally low/high (e.g., due to a measurement error): 0 and 1234. If you used the AVERAGE function, you would thus obtain an unusable average:
AVERAGE(A1:A10) returns 159.2
If you use TRIMMEAN instead and, for example, exclude 20% of the top and bottom values – in this case, these are conveniently exactly the two outliers among the 10 values – a useful average value is returned:
TRIMMEAN(A1:A10, 20%) returns 42.75
See also: