Microsoft Office Tutorials and References
In Depth Information
Using TRIMMEAN to Exclude Outliers from the Mean
TRIMMEAN to Exclude Outliers from the Mean
to Exclude Outliers from the Mean
Sometimes a data set includes a few outliers that radically skew the aver-
age. For example, suppose you have a list of gross margin percentages. Most
percentages fall in the 45% to 50% range, but there was one deal where for
customer satisfaction reasons, the product was given away at a loss. This
one data point would skew the average unusually low.
The TRIMMEAN function takes the mean of data points but excludes the n%
highest and lowest values. You have to use some care in expressing the n%.
The TRIMMEAN function returns the mean of the interior of a data set.
TRIMMEAN calculates the mean taken by excluding a percentage of data
points from the top and bottom tails of a data set. You can use this function
when you want to exclude outlying data from your analysis. This function
takes the following arguments:
array — This is the array or range of values to trim and average.
percent — This is the fractional number of data points to exclude
from the calculation. For example, if percentis 0.2, 4 points are
trimmed from a data set of 20 points (that is, 20 × 0.2): 2 from the top and
2 from the bottom of the set.
If percentis less than 0 or greater than 1, TRIMMEAN returns a #NUM! error.
TRIMMEAN rounds the number of excluded data points down to the nearest
multiple of 2. If percentequals 0.1, 10% of 30 data points equals 3 points. For
symmetry, TRIMMEAN excludes a single value from the top and bottom of the
Suppose that your 401(k) plan is invested in a stock market index fund. The
stock market goes up 5%, 40%, and 15% in three successive years. Taking the
average of these numbers might lead someone to believe that the average in-
crease was 20% per year. This is not correct. The growth rates are all mul-
tiplied together to find an ending value of your investment. To find the aver-
age growth rate, you need to find a number that, when multiplied together three
times, yields the same result as 105% × 140% − 115%. You can calculate this
by using GEOMEAN.
GEOMEAN to Calculate Average
to Calculate Average Growth