AVERAGEA

What it does

AVERAGEA(value1, [value2], ...)

AVERAGEA(value1, [value2], …) returns the arithmetic mean

AVERAGEA = (1/n) · Σ_{i=1..n} v_i

The consequence is worth stating baldly, because it is the entire reason the function exists and the entire reason it surprises people: text does not merely join the sum as zero, it joins the count. A column holding 10, 20 and the word "n/a" averages to 15 under AVERAGE and to 10 under AVERAGEA. Neither result is a bug. AVERAGEA is answering "what is the mean if every non-empty entry is worth something, and unusable entries are worth nothing", which is a legitimate question and a different one.

Range: as with AVERAGE, the result of finite inputs lies between the smallest and largest admitted value — where the admitted values now include the zeros contributed by text.

Arguments

Try it

Quirks

Values included by AVERAGE and AVERAGEA

Two rows repay attention.

Numeric-looking text in a range counts as 0, not as its numeric value. The reference engine's averagea_argument_value helper converts text only when it arrived as a direct scalar argument; text reaching it from a reference returns 0.0 without any conversion attempt. So a range cell holding the text "2" contributes zero, while the same "2" typed into the formula contributes two. This is the sharpest edge on the function and the one that turns an imported CSV — where numbers often arrive as text — into a silently deflated average.

Result and edge cases

Returns Number.

• A range of only text. Under AVERAGE this is #DIV/0! (nothing admitted); under AVERAGEA it is 0 (everything admitted, all as zero). The two functions differ between an error and a number on the same input, which makes this the best single discriminating probe.
• TRUE cells. They contribute 1, which is almost never what a reader intends and is the second most common source of a wrong AVERAGEA.
• Arrays. The surface lifts natively (lift_broadcast_profile: surface_native) and consumes arrays as a reduction.
• Very large / very small magnitudes, negative values, non-integers. Ordinary; no domain guard is recorded on this surface (arg_domain_guard=none, non_finite=allow).

Errors

How to implement

Numerical notes

The floating-point analysis is exactly that of AVERAGE — sequential summation, a condition number of (\sum|v_i| / |\sum v_i|), and one final division — with one addition that is specific to this surface.

The logicals are exact too (1.0 and 0.0 are exactly representable). The only inexactness on this surface is the same summation and division that AVERAGE has.

Rust code

aggregate(AVERAGEA, "AVERAGEA", VALUES, averagea)

/// AVERAGEA: AVERAGE, with held text read as 0 and held logicals as 1 and 0.
fn averagea(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    mean(AVERAGEA, Held::AverageValues, arguments, services)
}

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

Relationships

• COUNT / COUNTA — the same widening, applied to counting. AVERAGEA's denominator is closer to COUNTA than to COUNT, which is a useful way to remember it.
• Confused with: AVERAGEIF with a criterion excluding text. That is a different function answering the AVERAGE question with extra selection, not the AVERAGEA question.

Named variants

No named variants are recorded yet.