|
<< Click to Display Table of Contents >> TRIMMEAN (mean without marginal values) |
Syntax:
TRIMMEAN(Area, Percentage)
Description:
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" (untypically 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: 5 from the bottom and 5 from the top.
Note: For symmetry, the number of excluded values will automatically be rounded down to a multiple of 2. For example, if 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., because of a measuring error): 0 and 1234. Thus, using the AVERAGE function would return an unusable result:
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 ideally exactly the two outliers among the 10 values – a usable average value is returned:
TRIMMEAN(A1:A10, 20%) returns 42.75
See also: