HARMEAN function. Get harmonic mean.(Microsoft Excel)
Get harmonic mean.
How it works
=HARMEAN(number1 to 255)
| Name | Omission | Specify |
|---|---|---|
| number1 to 255 | Required | Specify a list of values or a range of cells from which to obtain the harmonic mean. |
Example Results
For example, it is used to calculate the average of several speeds over a certain distance.
For example, if you are traveling back and forth between home and office, let "Going speed" be 0.5Km per hour and "Returning speed" be 0.25Km per hour.
The AVERAGE function (arithmetic average) for these two speeds yields a speed of 0.38 km/h, while the HARMEAN function (harmonic average) yields a speed of 0.33 km/h.
| Item | Value | Unit |
|---|---|---|
| Going speed | 0.50 | km/h |
| Returning speed | 0.25 | km/h |
| AVERAGE function (arithmetic average) | 0.38 | km/h |
| HARMEAN function (harmonic average) | 0.33 | km/h |
If it is simply an average of speed, arithmetic average is fine.
However, if a round trip is made over a certain distance, the harmonic mean speed, not the arithmetic mean speed, is the correct answer for the average speed of the round trip.
This is because the weight of the speed of the return trip is different from that of the destination trip, since the time spent traveling at the slower speed is longer.
For example, a round trip on a road 1 km long each way would take 2 hours for the trip there and 4 hours for the trip back.
| Item | Value | Unit |
|---|---|---|
| One way distance | 1 | km |
| Going | 2 | Hour |
| Returning | 4 | Hour |
The round trip distance is 2 times 1Km, which is 2Km, and the round trip time is 2 hours for the trip there and 4 hours for the trip back, which is 6 hours.
Dividing the round trip distance by the round trip time yields the round trip speed, which is calculated to be 0.33, the same as the result of the harmonic mean.
| Item | Value | Unit |
|---|---|---|
| Round trip time | 6 | Hour |
| Round trip distance | 2 | km |
| Round rip speed | 0.33 | km/h |
Spill
Spill when using the BYROW or BYCOL function.
=BYROW(A1:C3,LAMBDA(r,HARMEAN(r)))
=BYCOL(A1:C3,LAMBDA(c,HARMEAN(c)))
---