Microsoft Office Tutorials and References

In Depth Information

**Using TRIMMEAN to Exclude Outliers from the Mean**

Using

Using
TRIMMEAN

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%.

Syntax

=TRIMMEAN(array,percent)

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

array
—
This is the array or range of values to trim and average.

•
percent

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.

Using

Using
GEOMEAN

Rate

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