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
data set.
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
Growth Rate
Search JabSto ::

Custom Search