Statistical Analysis with Excel Notes (Trimmean & Geometric Mean, Harmonic Mean)

Trimmean:Trimming the Mean.  This is Excels way to eliminate statistical outliers from data.

How to use TRIMMEAN:

1.  Type the scores into a worksheet and select a cell for the result.

For this example, I put these numbers into cells B2 through B11:

500,280,550,540,525,595,620,1052,591,618

These scores might result from a psychology experiment that measures reaction time in milliseconds.  I selected B12 for the result.

2.  From the Statistical Functions menu, select TRIMMEAN.

3.  In the Function Arguments box, type the values for the arguments.

The data array goes into the data array box.  B2:B11
Next I have to identify the percent of scores I want to trim.  In the percent box I enter .2.  This tells TRIMMEAN to eliminate the extreme 20 percent of the scores before calculating the mean.  The extreme 20 percent means the highest 10 percent and the lowest 10 percent of scores.

The result is 567.375

**The label percent is a little misleading in the function arguments box.  You have to express the percent as a decimal.  So you enter .2 rather than 20 in the percent box if you want to trim the extreme 20 percent.

Geometric Mean:

Suppose you have a 2 year investment that yields 25% the first year and 75 percent the second year.  What's the average annual rate of return?

To answer that question, you might be tempted to find the mean of 25 and 75.  But that misses an important point: At the end of the first year, you multiply your investment by 1.25 - you don't add 1.25 to it.  At the end of the second year, you multiply the first year result by 1.75.  This kind of average is called a geometric mean.

In the example, the geometric mean is the square root of the product of the two numbers SQRT 1.25*1.75  For three numbers, the geometric mean is the cube root of the product of the three.

The Excel worksheet function GEOMEAN calculates the geometric mean of a group of numbers.

Harmonic Mean:

Supposed you drive from your house to your job at the rate of 40 miles an hour.  On the way home you drive from your job to your home at 60 miles an hour.  What's the average speed for the total time you're on the road?

It's not 50 MPH because you're on the road a different amount of time on each leg of the trip.  The formula for figuring this out is 1/average = Average of {1/40 + 1/60} = 1/48

The average is 48.  This kind of average is called a harmonic mean.  You can calculate it for any amount of numbers.  Just put each number in the denominator of a fraction with 1 as the numerator (reciprocal).  Add all the reciprocals together and take their average.

The Excel function for this is HARMEAN