LARGE

What it does

LARGE(array, k)

LARGE(array, k) returns the k-th largest value of the numeric data in array.

Formally, let x₍₁₎ ≤ x₍₂₎ ≤ … ≤ x₍ₙ₎ be the admitted values sorted ascending — the order statistics of the sample. Then

LARGE(array, k) = x₍ₙ₋ₖ₊₁₎

so LARGE(array, 1) is the maximum, LARGE(array, n) is the minimum, and k counts down from the top. Ties count with multiplicity: in {5, 5, 3} both LARGE(·,1) and LARGE(·,2) are 5. This is the point most often missed — LARGE is not "the k-th largest distinct value", and there is no built-in that is.

Domain. 1 ≤ k ≤ n, where n is the count of numeric values actually admitted from array. k outside that range has no order statistic to name. Range: exactly one of the input values — LARGE is a selector, and its result is always bit-identical to some element of the data. No arithmetic is performed, which is the property that distinguishes it from the percentile family.

That last point deserves emphasis, because it is the whole reason LARGE exists alongside PERCENTILE.INC. PERCENTILE.INC(array, p) interpolates linearly between adjacent order statistics and can return a number that appears nowhere in the data. LARGE never does. If you need "the third-highest actual observation", LARGE is the correct function and a percentile is not.

The complementary identity with SMALL is exact:

LARGE(array, k) = SMALL(array, n − k + 1)

Arguments

k is a numeric slot subject to ordinary to-number coercion; array is a scanned collection governed by the range-scan policy. The expected handling of a non-integer k is truncation toward zero before the range check, which is the convention the rest of the index-taking surface uses, but Microsoft's page does not state it on this function and the Handbook has not confirmed it. See Coercion and lifting.

A subtlety worth stating: n is the count of values that survive the scan, not the number of cells in the range. Blanks and text in a referenced range are ignored by the usual aggregate scan policy, so LARGE(A1:A10, 10) over a column with two blanks is an out-of-range request even though the range has ten cells. That is a common and silent source of #NUM!.

Try it

Quirks

Result and edge cases

Returns Number — one of the input values, unmodified.

• k = 1 coincides with MAX over the same admitted set, and k = n with MIN. Whether the three surfaces agree on which bits they return when the extremum is attained by values that compare equal but differ in representation (0 and −0) is unverified.
• k non-integer, k ≤ 0, or k > n → #NUM! as documented.
• A direct logical in either slot is coerced by the direct-argument rules — TRUE becomes 1 in both positions — while a logical inside a referenced range is ignored. This is the standard asymmetry of chapter 02, and it means LARGE(TRUE, TRUE) is a well-formed call.
• Error values in array propagate rather than being skipped.

Errors

How to implement

Numerical notes

LARGE performs no arithmetic, so it has no rounding error and no residual plate worth drawing. Its entire correctness question is the comparison predicate and the selection algorithm.

The comparison predicate. Selecting an order statistic requires a total order on the admitted values. Three questions have to be answered and none of them is answered by Microsoft's page:

1. Is the comparison raw IEEE-754 <, or is it the tolerant, truncation-style 15-significant-digit comparison that Excel uses in several other families? The reference engine's upstream record documents a family split on exactly this point for the criteria functions. If LARGE were tolerant, LARGE({0.3, 0.1+0.2}, 1) and LARGE({0.3, 0.1+0.2}, 2) would be free to return either representation; if it is exact, the two are ordered.
2. How are +0 and −0 ordered? IEEE comparison calls them equal, so a stable selection returns whichever came first in scan order and an unstable one may not.
3. Excel has no NaN in its value universe, so the usual NaN-ordering trap does not arise — but an implementation built on a generic float comparator has to decide what it does with one anyway, and the safe choice is to make it unreachable.

The selection algorithm. Selecting the k-th largest of n values does not require sorting. The classical options are:

• Full sort, O(n log n) — simplest, and for spreadsheet-sized data usually fastest in practice. Must be a stable or at least well-defined sort if the ±0 question above matters.
• Quickselect (Hoare's FIND), expected O(n), worst case O(n²) on adversarial input. See Knuth, The Art of Computer Programming volume 3 §5.3.3, and Numerical Recipes §8.5, which presents exactly this problem under the name "selection of the M-th largest".
• Median-of-medians (Blum, Floyd, Pratt, Rivest, Tarjan), worst-case O(n), with a constant large enough that it is rarely the right choice here.
• A bounded heap of size k, O(n log k) — the right choice when k is small and n is a whole column, which is the common spreadsheet shape.

A recomputation-heavy workbook that calls LARGE(A:A, k) for k = 1…10 in ten cells sorts the same column ten times unless the engine caches; that is an engine question, not a function-semantics question, and the Handbook does not assert what Excel does.

The one genuinely numerical remark: because the result is a copy of an input value, LARGE can never introduce error, and it can never remove it either. LARGE over a column of computed values inherits whatever error those computations had, and selecting the maximum of a noisy set is a biased operation — the expected maximum of n samples of a quantity plus noise exceeds the maximum of the noiseless quantity. That is a statistics observation, not an implementation defect, and it is the reason "take the best of n runs" is a poor estimator.

Rust code

9 large "LARGE" (2, 2) [R "array", V "k"] |s, a, sv| { nth(s, a, sv, true) }

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SMALL — the ascending mirror, with the exact index identity given above. The two are the same algorithm with a reversed comparator, and an implementation that does not share code between them is duplicating the delicate part twice.
• MAX / MIN — the k = 1 and k = n special cases, with different argument shapes: MAX takes a variadic argument list, LARGE takes exactly one array. MAX also has a documented "returns 0 if there are no numbers" behaviour where LARGE errors — the two disagree deliberately on the empty case.
• PERCENTILE.INC / PERCENTILE.EXC / QUARTILE.INC — the interpolating relatives. PERCENTILE.INC at p = (n−k)/(n−1) equals LARGE(array, k) exactly, because at the knots the linear interpolation is degenerate. Away from the knots they differ, and that is the whole point.
• RANK.EQ / RANK.AVG — the inverse direction: given a value, return its position. RANK.EQ and LARGE are inverses on data with no ties and are not inverses when ties are present, because RANK.EQ collapses tied ranks while LARGE does not.
• SORT and TAKE — the modern dynamic-array route to "the top k", which returns all of them at once and makes the tie behaviour visible.
• Confused with: LARGE and MAX when the data contain text — the scan policy decides which values count, and it is the same policy for both, so a disagreement between them is a signal that something else is wrong.

Named variants

No named variants are recorded yet.