AVERAGE

What it does

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

AVERAGE(number1, [number2], …) returns the arithmetic mean of the admitted values:

AVERAGE = (1/n) · Σ_{i=1..n} x_i

where (n) is the count of admitted values. That formula is the whole of the mathematics, and it is also, deceptively, the least interesting part of the function. Two other things carry the function's real content:

1. Which values are admitted — the direct-versus-range dual policy described below, which decides (n) as well as the numerator. Everything readers get wrong about AVERAGE lives here.
2. How the sum is formed — because a sum of (n) doubles is not a mathematical object but an ordered sequence of roundings, and different orderings give different answers.

The mean is the minimiser of (\sum_i (x_i - c)^2) over (c) — that is its defining variational property, and it is why the mean, not the median, is the centre that VAR, DEVSQ and STDEV deviate from. Range: the mean of finite values lies between the minimum and the maximum, a property that plain floating-point summation does not guarantee to preserve under all orderings and roundings.

Arguments

Try it

Quirks

What counts, and what is skipped

Microsoft's AVERAGE page documents the range half plainly: "If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included." It documents the direct half as an exclusion: "Logical values and text representations of numbers that you type directly into the list of arguments are not counted."

Skipped means skipped from both the numerator and the denominator: it does not enter as a zero. That is the difference between AVERAGE and AVERAGEA, and it is why the two can differ by a large factor on the same range.

The consequence people meet in practice: a column with three numbers and two text cells averages over three values under AVERAGE and over five under AVERAGEA. Neither is wrong; they answer different questions, and the function name is the only place the choice is recorded.

See Coercion and lifting for the general coercion machinery, and Missing versus Empty for why an omitted slot and an empty cell are different kinds that happen to behave alike here.

Result and edge cases

Returns Number.

• One admitted value. Returns that value. Whether it returns exactly that value depends on whether (x/1) is performed at all; division by one is exact in IEEE arithmetic, so it does.
• Values spanning many magnitudes. The result is correct to within the accumulated summation error, which is not bounded by anything the function documents. See the numerical notes.
• Arrays. The surface lifts natively (lift_broadcast_profile: surface_native); as a reduction it consumes array arguments rather than broadcasting over them.

Errors

How to implement

Numerical notes

Averaging is the canonical worked example in floating-point error analysis, and it deserves the treatment even though the formula fits on one line.

Summation error. For a plain left-to-right accumulation of (n) doubles, the standard bound (Higham, Accuracy and Stability of Numerical Algorithms, chapter 4) is

|ŝ − s| ≤ γ_{n−1} · Σ|x_i|,     γ_k = k·u / (1 − k·u)

with (u = 2^{-53}). The bound is proportional to (\sum |x_i|), not to (|s|), so the relative error is governed by the condition number (\sum|x_i| / |\sum x_i|). Data of mixed sign that nearly cancels — returns around zero, residuals, differences — can lose every significant digit while the same data with a constant added stays accurate. This is the mechanism behind essentially every reported spreadsheet averaging surprise.

What better implementations do. Pairwise (cascade) summation replaces (n) in the bound by (\log_2 n) at no meaningful cost, which is why it is the default in most numerical libraries. Compensated summation — Kahan's algorithm, and Neumaier's variant that also handles the case where the running sum is smaller than the addend — reduces the bound to roughly (2u\sum|x_i|) independent of (n). Exact-accumulation schemes (Shewchuk's expansions, Ogita–Rump–Oishi Sum2/SumK, or a long accumulator) give a correctly rounded sum. Any of these changes the last bits of the answer, which is precisely why an implementation targeting a particular engine's output cannot simply adopt the best one.

The division. Dividing an accurately summed total by (n) adds at most one rounding, so the division is never the problem. Running-mean formulations — (m_{k} = m_{k-1} + (x_k - m_{k-1})/k), the Welford update — trade the overflow hazard for (n) divisions and a different error profile; they are the right choice when the sum would overflow, and a different answer when it would not.

Ordering. Floating-point addition is commutative but not associative. Sorting by increasing magnitude before summing is a classical heuristic and improves the typical case for same-sign data; it changes the answer. So does any parallel or vectorised reduction, which is why a reduction that must agree bit-by-bit across machines has to fix its association order — the concern the Handbook's portable-reproducible flavour exists to address; see About implementation options.

Rust code

aggregate(AVERAGE, "AVERAGE", NUMBERS, average)

/// AVERAGE: the numbers added from 0 and divided by how many there are; #DIV/0! when there is
/// none.
fn average(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    mean(AVERAGE, Held::Numbers, 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

• MEDIAN, MODE.SNGL, TRIMMEAN, GEOMEAN, HARMEAN — the other centres. TRIMMEAN is the one to reach for when the objection to AVERAGE is outliers.
• SUBTOTAL(1, …) and AGGREGATE(1, …) — AVERAGE with hidden-row and error-skipping options, and the usual right answer when a filtered table is involved.

Named variants

No named variants are recorded yet.