HARMEAN in Excel: Average Positive Rates With the Right Weights

• Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official HARMEAN reference. Documentation and independently calculated examples; no native Excel execution or broader platform certification.

=HARMEAN(number1, [number2], ...)

Microsoft allows 1–255 arguments and requires participating values to be positive. A value at or below zero gives #NUM!. Referenced text, logical values and empty cells are ignored; numeric zero is included. Direct logical values and numeric text count.

The equal-weight formula is n/(1/x1+...+1/xn). Microsoft’s prose says harmonic, geometric and arithmetic means are strictly ordered. The mathematically complete statement permits equality when all positive observations are equal; the worked equal-input control below makes that exception explicit.

=HARMEAN(30,60)
=2/(1/30+1/60)

Both expected results are 40. The reciprocal sum is 0.05 and dividing two by it gives 40. The arithmetic mean of 30 and 60 is 45, so choosing the function changes the modeled quantity.

A harmonic mean is not a default replacement for every average of numbers labeled rates. The quantity held equal across observations determines whether an unweighted harmonic mean is appropriate.

In a fictional processing model, two batches each contain 120 units. Rates are 30 units/hour and 60 units/hour. The batches take 4 hours and 2 hours, so total work divided by total time is:

=(120+120)/(120/30+120/60)

Expected overall rate: 40 units/hour, matching HARMEAN(30,60). Equal batch sizes supply the weighting assumption.

If instead each rate applies for two hours, the work amounts are 60 and 120, for 180 units over four hours. The average rate is 45 units/hour, matching the arithmetic mean. The rate values are unchanged; the observation weights changed.

Let the first batch contain 60 units at rate 30 and the second 180 units at rate 60. Their durations are two and three hours:

=(60+180)/(60/30+180/60)

Expected rate: 48 units/hour. The unweighted two-value HARMEAN would still return 40 and would not represent these unequal batch sizes.

For this small integer weight ratio 1:3, =HARMEAN(30,60,60,60) also gives 48. Repetition models four equal-sized units of work, one processed at 30 and three at 60. It is not evidence that arbitrary external weights are automatically understood by HARMEAN.

For the positive data 2 and 8:

=HARMEAN(2,8)
=GEOMEAN(2,8)
=AVERAGE(2,8)

Expected results are 3.2, 4 and 5. Here the inequalities are strict. Replacing both observations with five makes all three means 5, showing why “always less than” is too strong without an equal-input exception.

See GEOMEAN for multiplicative models and AVERAGE for the arithmetic mean. A formula name cannot determine the correct weighting policy for a report.

Create F2:F4 with numeric 2, text 8 entered with a leading apostrophe, and logical TRUE:

=HARMEAN(F2:F4)
=HARMEAN(2,"8",TRUE)

The documented participant rules give expected results 2 and approximately 1.846153846. The direct call uses 2, 8 and 1, so its reciprocal sum is 13/8 and the result is 24/13. The referenced range uses only the numeric two.

This is a narrow input-type control, not a claim that every unconvertible direct string or error cell can be ignored.

=HARMEAN(30,0,60) and =HARMEAN(30,-1,60) have Microsoft’s documented expected error #NUM!. Replacing an unavailable positive rate with zero therefore cannot be treated as a neutral missing observation.

Check the positivity, stored types and equality of the intended weights before changing the formula. These finite controls do not establish overflow behavior, extreme-value numerical conditioning or native error codes beyond those stated directly by the vendor.

• Microsoft HARMEAN reference.
• Editorial testing standards.

Inputs are fictional. Decimal answers are independent mathematical expectations rounded to nine places, not captured native results. Extreme magnitudes, complete coercion matrices and cross-version numerical accuracy remain untested.