each function.

“AVERAGED” MEASURES OF CENTRAL TENDENCY

6.1

These set of functions perform some type of averaging to measure a

“mean” value. You may want to use the Trimmed Mean function to

estimate an average that excludes the extreme values of the data series.

The Harmonic Mean estimates the averages of the reciprocals of the

numbers in the series. The Geometric Mean is used to average rates of

change.

Samples will be available at http://www.vjbooks.net/excel/samples.htm.

6.1.A AVERAGE

The function calculates the simple arithmetic average of all cells in the

chosen range.

Menu path to function: Go to the menu option INSERT/FUNCTION and

choose the formula “AVERAGE the function category STATISTICAL.

Figure 85: AVERAGE function

89

Statistical Analysis with Excel

Data requirements: The X values can be input as references to one or more

ranges that may be non“adjacent. The second range can be referenced in

the first text-box “Number1” after placing a comma after the first range,

or it could be referenced in the second text-box “Number2.” If you use the

second text-box, then a third text-box “Number3” will automatically open.

(As you fill the last visible box, another box opens until the maximum

number of boxes ” 30 ” is reached.)

The function does not count invalid cell values when counting the number

of X values. The X values can take any real number value.

6.1.B TRIMMEAN (“TRIMMED MEAN”)

This function is a variation of the average or mean. This function

calculates the average for a set of X values after removing “extreme

values” from the set. The excluded cells are chosen by the user based on

the extremity (from mean/median) of the values in the range.

TRIMMEAN calculates the mean taken by excluding a percentage of data

points from the top and bottom tails of a data set. The user decides on the

percentage of extreme values to drop. For symmetry, TRIMMEAN

excludes a set of values from the top and bottom of the data set before

moving on to the next exclusion.

Menu path to function: INSERT/FUNCTION/STATISTICAL/TRIMMEAN.

Data requirements: The X values can be input as references to one or more

ranges that may be non“adjacent. The function does not count invalid cell

values when counting the number of X values. The X values can take any

real number value.

90

Chapter 6: Functions for Basic Statistics

Figure 86: TRIMMEAN (Trimmed Mean)

In the dialog (shown above), Percent is the fractional number of data

points to exclude from the calculation. Percent must be greater than zero

and less than one.

6.1.C HARMEAN (“HARMONIC MEAN”)

The function calculates the harmonic mean of all cells in the chosen

range(s). The harmonic mean is the reciprocal of the arithmetic mean of

reciprocals. In the formula below, H is the harmonic mean, n the

sample/range size and the Y™s are individual data values.

Menu path to function: INSERT/FUNCTION/STATISTICAL/HARMEAN.

Figure 87: HARMEAN (Harmonic Mean)

Data requirements: The X values can take any real number value except

zero.

91

Statistical Analysis with Excel

Table 10: Comparing the results of the functions Average, Trimmed Mean

and Harmonic Mean

Function s1 s2 x1 x2 x3 x4

Average/mean 7.32 7.23 1173.00 14.55 0.17 1158.45

Trimmed Mean 7.13 7.00 1173.00 14.42 0.02 1158.71

#NUM!

Harmonic Mean 3.84 3.18 120.17 13.52 0.01