Statistical
Choose arithmetic, geometric or harmonic means for the right weighting
Compute Geometric Mean PRODUCT(x)^(1/n) for compounded growth factors and Harmonic Mean COUNT(x)/SUM(1/x) for speeds, rates, and P/E multiples.
Choose the right mean: Arithmetic vs Geometric (growth) vs Harmonic (rates)
At 20,30,60 mph over three equal 60-mile legs, total distance is 180 miles and total time is 6 hours. Average speed is therefore 30 mph, the harmonic mean. Equal-time legs would instead use an arithmetic mean.
The geometric mean of positive growth factors gives an equivalent compounded factor for equal periods. Convert returns r to factors 1+r, take their geometric mean, then subtract one. Do not directly take the geometric mean of percentage returns.
For positive observations, arithmetic mean >= geometric mean >= harmonic mean. The examples use three positive numbers and do not cover missing values or nonpositive factors.
The product method can overflow for long lists or extreme values; a log-based method is preferable in that case. Different weighting requires weighted formulas.
| Pythagorean Mean on [20, 30, 60] | Formula | Result & Domain |
|---|---|---|
| Harmonic Mean (HM) | =COUNT(A2:A4) / SUM(1 / A2:A4) | 30.0000 (equal-distance speeds) |
| Geometric Mean (GM) | =PRODUCT(A2:A4) ^ (1 / COUNT(A2:A4)) | 33.0193 (Growth factors, ratios) |
| Arithmetic Mean (AM) | =AVERAGE(A2:A4) | 36.6667 (Additive totals) |
Harmonic Mean and Geometric Mean formulas
Reciprocal array division COUNT(A2:A4) / SUM(1 / A2:A4) matches Excel's HARMEAN on positive arrays.
=IF(OR(COUNT(A2:A4)<>3,MIN(A2:A4)<=0),"Use three positive numbers",ROUND(COUNT(A2:A4) / SUM(1 / A2:A4), 4))Geometric Mean (Nth root of product)
Matches Excel's GEOMEAN on positive values.
=IF(OR(COUNT(A2:A4)<>3,MIN(A2:A4)<=0),"Use three positive numbers",ROUND(PRODUCT(A2:A4) ^ (1 / COUNT(A2:A4)), 4))Try the example
Use the original values or change them to test your understanding.