SMALL

What it does

SMALL(array, k)

SMALL(array, k) returns the k-th order statistic of the numeric values in array, counting from the bottom.

Let the numeric values of array, sorted ascending, be x_(1) <= x_(2) <= … <= x_(n). Then

SMALL(array, k)  =  x_(k)

for 1 <= k <= n, and #NUM! otherwise.

This is selection, not estimation. Unlike PERCENTILE.INC or QUARTILE.INC, SMALL performs no interpolation: the result is always a value that actually occurs in the data, returned unchanged. That property is the reason to reach for it — when a report must cite a real observation rather than a computed quantile, SMALL is the function that guarantees it.

The two ends are the familiar aggregates:

SMALL(A, 1)  =  MIN(A)          SMALL(A, n)  =  MAX(A)

and the ascending/descending duality with LARGE is exact:

SMALL(A, k)  =  LARGE(A, n - k + 1)

where n is the count of numeric values, which is COUNT(A) and not the size of the range. That last qualification is where the identity is usually got wrong in practice: a range with text labels in it has an n smaller than its cell count, and a formula written as LARGE(A, ROWS(A)-k+1) silently indexes the wrong statistic.

Ties are not deduplicated. SMALL indexes the sorted multiset, not the sorted set of distinct values. On {5, 5, 5}, SMALL(A, 2) is 5, not an error and not the second distinct value. Readers wanting the second distinct smallest need a different construction entirely.

SMALL is monotone non-decreasing in k, and monotone non-decreasing in each data value — it is an order-preserving selector, which is what makes it safe to compose with itself.

Arguments

array is consumed by scanning. Text, logical values and blank cells reached through a range or array are skipped rather than converted — they do not become zeros and they do not enlarge n. Values passed directly as scalars go through ordinary to-number coercion under the dual policy of coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number — one of the values from array, returned unchanged.

• array contains no numeric values. n = 0, so every k fails: #NUM!. A range holding only text produces #NUM!, not #VALUE!.
• k greater than the count of numeric values. #NUM!. Note again that the count is of numeric cells, so adding a header row to a range does not increase the admissible k.
• k less than 1 after truncation. #NUM!.
• Repeated values are indexed as they occur; n counts them all.
• The result is the stored value, not a rounded one. SMALL never computes; whatever double was in the cell is what comes back, including its exact bit pattern.
• Errors in array propagate as themselves.
• array given as a rectangular grid is flattened; shape is irrelevant.
• SMALL returns a scalar, not a spilled array, even when k is array-valued — whether Excel lifts over an array-valued k in a modern build is a lifting question this page does not settle.

Errors

How to implement

Numerical notes

SMALL performs no arithmetic on the data. It sorts and it indexes, so there is no rounding error in the result at all — the returned value is bit-identical to the input value that was selected. That makes it one of the few statistical functions whose correctness is entirely a question of ordering and admissibility rather than of accuracy. Three ordering questions nonetheless deserve attention.

Signed zeros. IEEE-754 defines -0.0 == +0.0 as true and neither as less than the other, so a data set containing both admits two valid sorted orders and SMALL may return either zero. The values compare equal and print identically, so this is invisible in a spreadsheet — but it is visible to any comparison of raw bits, and a vector suite comparing bit patterns must decide whether it cares. The mathematically honest position is that SMALL is well defined as a multiset selection and under-determined as a bit selection.

Stability and cost. The kernel performs a full sort, O(n log n), to answer a question that quickselect answers in expected O(n) — the classical result of Blum, Floyd, Pratt, Rivest and Tarjan (1973) gives a worst-case linear bound. For a single SMALL over a large column the sort is the entire cost of the call, and a column of SMALL formulas over the same range re-sorts the same data once per formula. That is a performance property rather than a correctness one, but it is the reason a k-th-smallest column is slow and a SORT spill is not, and no amount of formula rewriting inside SMALL fixes it.

The truncation of k. trunc(k) before validation means the admissible interval for k is the half-open [1, n+1) rather than the closed [1, n]: k = n + 0.5 truncates to n and succeeds. Nothing documents this, and the alternative — validate first, then truncate — would reject it. Which one Excel does is a one-cell experiment nobody has run.

Rust code

10 small "SMALL" (2, 2) [R "array", V "k"] |s, a, sv| { nth(s, a, sv, false) }

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

• LARGE is the descending counterpart, with the exact identity SMALL(A,k) = LARGE(A, COUNT(A)-k+1). The two are the same selection with the comparison reversed.
• MIN is SMALL(A, 1) and MAX is LARGE(A, 1). MIN and MAX are the ones to use for the extremes — they need no count and no k, and they do not error on an empty range (they return 0), which is a behavioural difference worth knowing when a range might be empty.
• MEDIAN is SMALL(A, (n+1)/2) for odd n and the mean of the two middle order statistics for even n.
• QUARTILE.INC and PERCENTILE.INC are the interpolating relatives. They return quantiles, which may be values that occur nowhere in the data; SMALL returns observations. Choosing between them is a choice about what the number is supposed to mean, not a choice of formula.
• RANK.EQ is the inverse map: LARGE(ref, RANK.EQ(x, ref)) recovers x, because competition rank and the descending order-statistic index coincide. The ascending version composes with RANK.EQ(x, ref, 1).
• SORT and SORTBY return the whole ordering as a spilled array. Where several order statistics are wanted at once, one SORT beats several SMALL calls by an order of magnitude in work.
• The idiom SMALL(IF(cond, range), k) is the classic way to get the k-th smallest among rows meeting a condition, and depends on the FALSE branch producing values SMALL skips.
• Readers confuse SMALL with MINIFS, which returns the minimum under criteria — the k = 1 case of the conditional idiom, with the condition expressed properly rather than through an array formula.

Named variants

No named variants are recorded yet.