GEOMEAN

What it does

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

GEOMEAN(number1, [number2], ...) computes the geometric mean of positive numbers: the nth root of their product. For growth rates, supply growth factors such as 1.05, then subtract one from the result if a rate is wanted.

Try it

=GEOMEAN(2,8) returns 4. Both observations can be replaced by four without changing their product. =GEOMEAN(1.1,0.9) is about 0.9949874371, showing that equal percentage gains and losses do not cancel multiplicatively.

The retained build 20430 controls give GEOMEAN(1,2)=1.414213562373095 and SQRT(2)=1.4142135623730951, adjacent binary64 values. FN-GEOMEAN-001 retains the exact cell inputs and both answers. Their mathematical values agree, but the recorded calculation routes do not publish the same bits.

Quirks

An admitted zero or negative value gives #NUM!. References ignore text, logical values and blanks; direct logical values and numeric text can be admitted. A product may overflow or underflow even when its geometric mean is representable. The current fallback for that case is not an identified Excel route.

How to implement

1. Collect the admitted numeric values. Reject an empty collection or any nonpositive admitted value with #NUM! before indexing the first value or taking logarithms. The current code returns the common positive input immediately when all admitted values are equal; a singleton is also an identity.
2. Fold the product in input order, storing each multiplication with R(op) from the variance recipe. Continue with positive normal product P and count n. If the product is zero, subnormal or nonfinite, stop this normalized recipe: the current sum-of-logs fallback is a separately unresolved candidate.
3. Extract the integer binary exponent e=floor(log2(P)) from the encoded exponent. Compute the integer q=trunc(e/n), toward zero. Scale by an exact power of two: M=P*2^(-q*n). Do not use floor for negative exponent division.
4. Store the measured public LN(M) result. Separately compute and store R(1/n), then store R(LN(M)*reciprocal). A direct LN(M)/n removes an observed rounding stage.
5. Call the measured public EXP on that stored argument, store its result, and scale by 2^q.

Worked identity reduction: 2*8=16, e=4, n=2, q=2, M=1; LN gives zero, EXP gives one and scaling returns four. A nontrivial reduction, 2*4=8, uses e=3, q=1, M=2; stored LN times the stored half is about 0.34657359027997265, EXP is about 1.414213562373095, then scaling gives about 2.82842712474619.

The normalized composition and product stores are isolated on the cited scalar controls. LN/EXP depend on their measured instruction models; substituting a language’s libm is a numerical alternative with different possible last bits. Private root internals, global branch boundaries and the overflow/underflow product fallback remain open. The code’s sum-of-logs fallback is an explicit candidate, not an Excel identification.

Rust code

3 geomean "GEOMEAN" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    let values = all(s, a, sv, Held::Numbers)?;
    if values.is_empty() || values.iter().any(|v| *v <= 0.0) {
        return fail(WorksheetErrorCode::Num);
    }
    // The equal-positive identity also avoids product underflow followed
    // by a log/exp round trip across the minimum-normal boundary.
    if values.iter().all(|value| *value == values[0]) {
        return s.number(values[0]);
    }
    // R26: each product assignment stores from x87 PC64. Independent
    // GEOMEAN(product,1,1) readings distinguish this loop from plain
    // binary64 without assuming the private root implementation.
    // The current root reconstruction still has recorded mismatches.
    // The overflow/underflow fallback remains a separate open question.
    let n = values.len() as f64;
    let product = values.iter().fold(1.0, |p, &x| k::excel_numeric::excel_x87_mul(p, x));
    if product.is_normal() {
        s.number(k::excel_numeric::excel_root_x87(product, values.len()))
    } else {
        let ln = values.iter().map(|v| crt_log(*v)).sum::<f64>();
        s.number(crt_exp(ln / n))
    }
}

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.

Retained controls from the indexed captures:

These are earlier recorded answers, not new Excel runs or an additional cross-build comparison. The function JSON retains each source locator, exact argument bits where available, and input-route status. Call notation does not imply that captured binary64 values were entered as decimal formula literals.

Recorded variants

Alternatives

AVERAGE suits additive quantities; HARMEAN suits rates over equal-sized portions. A scaled or logarithmic product is useful for a mathematical implementation if its different rounding history is acceptable.

Named variants

No named variants are recorded yet.