Returns the harmonic mean of a dataset, which is the reciprocal of the arithmetic average of reciprocals.
Syntax:
HARMEAN(number1; [number2]; …)
Arguments
- number1 (required) – First value or range for calculation.
- number2, … (optional) – Additional values or ranges.
- Can use a single array (e.g., A1:A5) instead of comma-separated values.
Background
The harmonic mean is used for:
- Averaging rates or ratios (e.g., speed = distance/time).
- Cases where values are defined by reciprocal relationships.
Equation:
Harmonic Mean=n1x1+1×2+⋯+1xnHarmonic Mean=x11+x21+⋯+xn1n
where n = number of values, x = data points.
Example. To explain how the harmonic mean is calculated, use the previously mentioned example of speed and time. A bicyclist travels 300
miles through the Alps. The distance is divided into five legs, for which he measures the speed of each.
Now the bicyclist wants to calculate the average speed from the speeds reached in each leg. The result should show the consistent speed at which he could have traveled the same distance in the same time (see Figure below).
To get a better overview, he also calculated the arithmetic average and the geometric mean.
To find out what calculation returns the best result, he transforms the results of the arithmetic, geometric, and harmonic means in meters/seconds and then calculates the time it would take to travel the 300 miles at the average speed (see figure below).
This calculation also confirms that the geometric mean is smaller than the harmonic mean, and the arithmetic mean is smaller than the geometric mean.
Next, you prove that the harmonic mean returns the best result. First you have to calculate speed v in m/s for the actual miles travelled at speed v for each leg in an hour. At a steady speed, the cyclist could have traveled 20 miles per hour in the first leg. If you divide 20 miles by 3,600 seconds, you get the speed v (see figure below).
Where V=S/t
Then you use the result for the speed in m/s in the same formula for t (time) to calculate the time for each leg in seconds. Given the fomular
t=S/V
the figure below shows the result.
The sum of the times in seconds for the legs shows that the value is approximately the same as the harmonic mean. The difference of three seconds is based on the rounded values.
The comparison of the actual result of 50,417.94 seconds with the calculated results of the different means shows that the harmonic mean returns the best result.
Conclusion: The harmonic mean gives the most accurate average for rates.