MEDIAN

What it does

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

MEDIAN(number1, [number2], …) returns the middle value of the numeric data it admits.

Let x₍₁₎ ≤ x₍₂₎ ≤ … ≤ x₍ₙ₎ be the order statistics of the admitted values. Then

n odd :   MEDIAN = x₍₍ₙ₊₁₎/₂₎
n even:   MEDIAN = ( x₍ₙ/₂₎ + x₍ₙ/₂₊₁₎ ) / 2

Microsoft states the even case directly: "If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle." The documented worked example is MEDIAN({2, 3, 3, 5, 7, 10}) = 4, the average of 3 and 5.

Two consequences that are the whole character of the function:

1. On an odd count MEDIAN is a selector — its result is bit-identical to one of the inputs, with no arithmetic performed. On an even count it is not: it returns a mean of two data points, which need not be in the data and is subject to floating-point rounding. MEDIAN is therefore the one member of the order-statistic group whose answer can differ in the last bits between two correct implementations. See Numerical notes.
2. The median is the minimiser of mean absolute deviation, argmin_c Σ|xᵢ − c|, as the mean is the minimiser of mean squared deviation. On an even count the minimiser is not unique — every point of the closed interval [x₍ₙ/₂₎, x₍ₙ/₂₊₁₎] achieves the same total absolute deviation — and the mid-average convention is a choice of representative from that interval, not a derivation. Other conventions (lower median, upper median, interpolated median) are equally defensible and are used elsewhere; this one is Excel's, and it agrees with the mainstream statistical convention.

Domain. At least one admitted numeric value. Range: the closed interval [min, max] of the data; the median is a location statistic and cannot leave the convex hull of the sample.

The median is the 50th percentile, the second quartile, and the most robust common measure of centre: its breakdown point is 50%, meaning up to half the data can be replaced by arbitrary values before the median can be forced anywhere. The arithmetic mean's breakdown point is 0% — a single value moves it arbitrarily far. That contrast is the reason MEDIAN exists next to AVERAGE, and it is the right basis for choosing between them.

Arguments

The coercion behaviour is the family-standard split documented on Microsoft's page:

The zero row is called out on Microsoft's page for a reason: users routinely expect a "blank or zero" range to contribute nothing, and blanks and zeros are treated oppositely. A column where missing readings were entered as 0 has a very different median from one where they were left blank, and the function gives no signal about which situation it is in.

See Coercion and lifting for the general rule this instantiates.

Try it

Quirks

Result and edge cases

Returns Number.

• n = 1 — the single value, returned unchanged.
• n = 2 — the arithmetic mean of the two, which is where the rounding question below first bites.
• Ties and repeated values need no special handling; multiplicity is preserved by the ordering and the middle position is well defined regardless.
• Very large values. MEDIAN over two values near the largest finite double is the overflow probe: (a + b)/2 overflows to infinity where a + (b − a)/2 does not. Excel has no infinity in its published value universe, so an implementation that overflows internally must produce something — a #NUM!, a saturated value, or a correct answer, depending on how the mean is formed. Which one is unverified and is on the probe list.
• +0 and −0. On an odd count the returned representation is whichever value was selected; on an even count (+0 + −0)/2 is +0 under round-to-nearest, so the sign information is destroyed. Observable through 1/MEDIAN(...).
• Arrays are consumed by scanning, not lifted elementwise; MEDIAN is not a lift kernel.

How to implement

Numerical notes

MEDIAN divides into a selection problem and, on even counts, a one-operation arithmetic problem. Both are small and both have a right answer.

Selection. Finding the middle order statistic does not require a full sort. The classical routes are quickselect (expected linear, quadratic worst case), the median-of-medians algorithm of Blum, Floyd, Pratt, Rivest and Tarjan (worst-case linear), and simply sorting (O(n log n), and usually fastest at spreadsheet sizes). Knuth's The Art of Computer Programming volume 3 §5.3.3 treats minimum-comparison selection; Numerical Recipes §8.5 presents exactly this problem, including the two-element case that even counts require. An even count needs both middle elements, which a naive single-selection routine does not give — running quickselect twice is wasteful and a single partition pass can yield both.

The average of two. This is the only arithmetic in the function and it has three formulations that are not equivalent in floating point:

(a + b) / 2          overflows when |a + b| exceeds the largest double
a/2 + b/2            never overflows; can underflow both halves to zero for subnormals
a + (b − a)/2        never overflows; exact for a == b; the usual recommendation

The third is the standard "midpoint without overflow" formula and is what a careful implementation uses; it also guarantees the result lies in [min(a,b), max(a,b)], which the first formulation does not guarantee under directed rounding. For a median that must be a when a == b, only the third form is exact by construction. The discussion in Sterbenz's Floating-Point Computation and in Higham's Accuracy and Stability of Numerical Algorithms (chapter 2, on the perils of naive averaging) covers the general case; the same reasoning is why (low + high)/2 is the famous binary-search overflow bug.

For ordinary spreadsheet data all three agree to the last bit, and the difference appears only at the extremes of the exponent range. That makes the extremes the only place a residual plate for MEDIAN would show anything, and it makes them worth probing precisely because nothing else will reveal which formulation is in use.

Comparison predicate. The selection needs a total order, with the same open questions as on LARGE: raw IEEE < versus a tolerant truncation-style comparison, and the ±0 tie. For MEDIAN a tolerant comparator could reorder near-equal values and thereby change which two values are averaged on an even count — a slightly larger consequence than for a pure selector.

Robustness is not stability. The median's 50% breakdown point is a statistical property of the estimator, not a numerical one. A median computed by a numerically careless route is still a median; a median of badly-computed inputs is still garbage. The two robustness stories are independent and are frequently conflated.

Rust code

5 median "MEDIAN" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    direct_first(s, a)?;
    let values = sorted(all(s, a, sv, Held::Numbers)?);
    if values.is_empty() {
        return fail(WorksheetErrorCode::Num);
    }
    let mid = values.len() / 2;
    let result = if values.len() % 2 == 1 {
        values[mid]
    } else {
        let half = (values[mid] - values[mid - 1]) / 2.0;
        values[mid - 1] + if half < f64::MIN_POSITIVE { 0.0 } else { half }
    };
    // W111's cancellation pairs have normal inputs and retain subnormal
    // results. Their input coercion and middle-value arithmetic stay the same.
    out_with_subnormal(s, Ok(result))
}

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

• AVERAGE — the other location statistic, with the same coercion split and a completely different robustness profile. Comparing the two on the same data is the standard skewness diagnostic: MEDIAN < AVERAGE indicates right skew.
• PERCENTILE.INC at p = 0.5 and QUARTILE.INC at quart = 2 compute the same quantity by the same interpolation convention, so all three must agree. They are separate registered surfaces and may reach the answer by different code; whether they agree in bits is a first-class question the Handbook has not settled. PERCENTILE.EXC at p = 0.5 also equals the median, because the two percentile conventions coincide exactly at the centre — which makes the centre a poor place to test the difference between them, and a good place to test that they agree.
• LARGE / SMALL — the general order-statistic selectors. On an odd count, MEDIAN equals LARGE(array, (n+1)/2) exactly; on an even count it does not equal any LARGE.
• MODE.SNGL / MODE.MULT — the third classical measure of centre. Mean, median and mode answer different questions and coincide only for symmetric unimodal data.
• TRIMMEAN — the tunable compromise between mean and median, discarding a proportion from each tail.
• SUBTOTAL (function numbers 12 and 112) and AGGREGATE (function number 12) — the filter-aware and error-skipping medians. AGGREGATE is the only route to a median that ignores error values.
• Confused with: "the middle cell of the range", which is what a reader sometimes assumes when the data are already sorted in the sheet — the two coincide only by accident, and stop coinciding the moment a blank appears.

Named variants

No named variants are recorded yet.