Harmonic Mean
The harmonic
mean
can be understood as an "average" when the numbers are defined
in relation to some unit. The common example is averaging speed.
For example, suppose that you have four 10 km
segments to your automobile trip. You drive your car:
- 100 km/hr for the first 10
km
- 110 km/hr for the second 10
km
- 90 km/hr for the third 10 km
- 120 km/hr for the fourth 10
km.
What is average speed? Here is a spreadsheet solution:
|
Distance
|
Velocity
|
Time
|
||
|
km
|
km/hr
|
hr
|
||
|
10
|
100
|
0.1
|
||
|
10
|
110
|
0.091
|
||
|
10
|
90
|
0.111
|
||
|
10
|
120
|
0.083
|
||
|
40
|
|
0.385
|
||
|
|
103.8
|
Avg V
|
||
Excel calculates this with the formula =HARMEAN(100,110,90,120).
Calculating
Geometric Means in Spreadsheets
Rather than using a
calculator, it is far easier to use spreadsheet functions. For example, in Microsoft
Excel the simple function "GeoMean" is provided to calculate the
geometric mean of a series of data.
For example, if you had 11 values in the range
A1...A10, you would simply write this formula in any empty cell:
'=geomean(A1:A10)'The following formulas are equivalent in Excel:
=GEOMEAN(datarange)
=POWER(PRODUCT(datarange),(1/count(datarange)))
{=EXP(AVERAGE(LN(datarange)))}
The curly brackets in the last formula means this is an array formula, and it is created by simultaneously pressing CTL-SHIFT-ENTER after you type in the formula. You can, of course, use a defined range name in these formulas in Excel and other spreadsheet programs.
=GEOMEAN(datarange)
=POWER(PRODUCT(datarange),(1/count(datarange)))
{=EXP(AVERAGE(LN(datarange)))}
The curly brackets in the last formula means this is an array formula, and it is created by simultaneously pressing CTL-SHIFT-ENTER after you type in the formula. You can, of course, use a defined range name in these formulas in Excel and other spreadsheet programs.
Source: http://buzzardsbay.org
