HARMEAN

What it does

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

HARMEAN(number1, [number2], ...) returns n / sum(1/x) for positive observations. It is useful for rates attached to equal-sized portions. Two equal distances travelled at different speeds have equal distance weights, not equal time weights; averaging their speeds arithmetically answers a different question.

Try it

=HARMEAN(30,60) returns 40. Travelling 60 km at 30 km/h takes two hours; another 60 km at 60 km/h takes one hour. Total distance 120 km divided by total time three hours is 40 km/h.

=AVERAGE(30,60) returns 45. That is the average speed for equal amounts of time at the two speeds. The difference comes from what receives equal weight.

Quirks

Every admitted value must be positive. A numeric zero or negative value gives #NUM!; a blank cell is skipped rather than treated as zero. References and arrays also skip logical values and text, including numeric-looking text. Direct numeric text and logical constants can be converted, so direct FALSE becomes a rejected zero.

Worksheet errors propagate, and unconvertible direct text gives #VALUE!. The current code checks direct scalars before reading held values, returns #N/A when collection finds no numbers, and gives #NUM! for a nonfinite reciprocal sum. Those last two choices are implementation behaviour, not claims established by Microsoft’s published error list.

How to implement

The mathematical identity n/sum(1/x) is not the current operation tree. Use R for the stored 64-bit-significand operation.

1. Preserve whether each value is direct, a reference cell or an array element; collect it using the rules above. Reject any admitted x<=0.
2. Starting from S=0, visit the admitted values in order. Store r=R(1/x), then store S=R(S+r) for each one.
3. After the empty-set and nonfinite-sum checks, store the reciprocal mean m=R(S/n).
4. Return R(1/m), subject to worksheet number publication. Replacing these last two divisions by R(n/S) changes the intermediate rounding history.

For 30,60, the mathematical reciprocal sum is 1/20; the reciprocal mean is 1/40, whose reciprocal is 40. The current staged route also publishes 40. These worked numbers explain the route; they do not identify all Excel rounding stages on every input.

Small positive inputs can make a reciprocal overflow. Scaling the reciprocal sum can help a mathematical implementation, but its different arithmetic needs separate Excel comparisons before being offered as a compatibility route.

Rust code

4 harmean "HARMEAN" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    direct_first(s, a)?;
    let values = all(s, a, sv, Held::Numbers)?;
    let mut reciprocal_sum = 0.0;
    for v in &values {
        if *v <= 0.0 {
            return fail(WorksheetErrorCode::Num);
        }
        reciprocal_sum = k::excel_numeric::excel_x87_add(
            reciprocal_sum,
            k::excel_numeric::excel_x87_recip(*v),
        );
    }
    if values.is_empty() {
        return fail(WorksheetErrorCode::NA);
    }
    if !reciprocal_sum.is_finite() {
        return fail(WorksheetErrorCode::Num);
    }
    let average = k::excel_numeric::excel_x87_div(reciprocal_sum, values.len() as f64);
    s.number(k::excel_numeric::excel_x87_recip(average))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

AVERAGE suits equally weighted observations; GEOMEAN suits multiplicative factors. Use a weighted harmonic mean when the portions are unequal.

Named variants

No named variants are recorded yet.