QUARTILE.INC

What it does

QUARTILE.INC(array, quart)

QUARTILE.INC(array, quart) returns a sample quantile of the data in array, at one of five fixed probabilities selected by quart.

Sort the numeric values of array ascending and call them x_(1) <= x_(2) <= … <= x_(n). Let k = quart / 4, so quart in {0, 1, 2, 3, 4} selects k in {0, 1/4, 1/2, 3/4, 1}. Define the rank

h  =  1 + k * (n - 1)

and let j = floor(h), g = h - j. Then

QUARTILE.INC  =  x_(j)  +  g * ( x_(j+1) - x_(j) )

which is the value at position h in the sorted data, obtained by linear interpolation between the two adjacent order statistics when h is not an integer.

This is exactly type 7 in the Hyndman–Fan taxonomy of sample quantile definitions — the same definition that is R's default quantile() and NumPy's default percentile(). Two properties identify it:

1. The rank runs from 1 to n. At k = 0 the rank is 1 and at k = 1 it is n, so QUARTILE.INC(A, 0) is the minimum and QUARTILE.INC(A, 4) is the maximum, exactly. The whole probability interval [0, 1] maps onto the data, which is what the inclusive in the name means and what distinguishes it from QUARTILE.EXC.
2. It is a continuous, piecewise-linear, non-decreasing function of k on [0, 1], and a non-decreasing function of every data value. It is the linear interpolation of the inverse empirical distribution function evaluated at the plotting positions (i-1)/(n-1).

Because it interpolates, the result need not be one of the data values. QUARTILE.INC of an integer data set is routinely a fraction.

At quart = 2 the rank is 1 + (n-1)/2 = (n+1)/2, which is the median rank, so

QUARTILE.INC(A, 2)  =  MEDIAN(A)

identically, for every n. This is one of the two quartile identities that both the inclusive and exclusive definitions share; see QUARTILE.EXC.

There is no single correct definition of a sample quartile. Hyndman and Fan catalogue nine in use across statistical packages, differing in the plotting positions they assign to the order statistics and therefore in the rank formula. The type-7 answer that QUARTILE.INC returns is a defensible convention, not a theorem, and a reader comparing Excel against another package should expect a different number rather than a bug.

Arguments

array is consumed by scanning. Text, logical values and blank cells in a scanned range are skipped rather than converted — they do not become zeros and they do not enlarge n. This is the ordinary aggregate scan policy described in coercion and lifting, and it is the policy the reference engine's percentile collector implements.

Try it

Quirks

Result and edge cases

Returns Number.

• quart = 0 and quart = 4 return the minimum and maximum, so QUARTILE.INC(A, 0) is MIN(A) and QUARTILE.INC(A, 4) is MAX(A) over the numeric cells — a fact worth knowing mostly because it means those two quarts can never fail for a non-empty array.
• Repeated values are ordinary: the sort is not distinct-value, and a data set that is entirely one value returns that value at every quart.
• Non-numeric cells in array are skipped, so n is the count of numeric cells, not the size of the range. A range of text returns #NUM!, because n = 0.
• Errors in array propagate as themselves.
• array given as a rectangular grid is flattened; the quartile is over all its numeric cells regardless of shape.

Errors

How to implement

Numerical notes

The mathematics is exact selection plus one linear interpolation, so the error budget is small and entirely locatable.

The rank computation. h = 1 + k*(n-1) with k = quart/4 is computed in binary64. For quart in {0, 2, 4} the value of k is exactly representable (0, 0.5, 1) and h is exact whenever n-1 is. For quart in {1, 3}, k is 0.25 or 0.75 — also exactly representable, both being dyadic rationals. This is a small piece of luck specific to quartiles: the four-way split lands on binary fractions, so the rank arithmetic for QUARTILE.INC is exact for every n up to the point where n-1 exceeds 2^53. The general PERCENTILE.INC has no such luck, because k = 0.1 is not dyadic, and its floor(h) can therefore land on the wrong side of an integer boundary. QUARTILE.INC does not inherit that hazard.

lower + g * (upper - lower)

The sort. Values are sorted with a total-order comparison that falls back to "equal" for incomparable pairs. Excel's value universe has no NaN, so this is unreachable from a worksheet; it becomes reachable if a NaN arrives from an add-in boundary, and the result is then an unspecified ordering rather than a diagnosed error. Signed zeros compare equal, so -0.0 and 0.0 may appear in either order; the returned value is 0 either way, but its sign bit is not determined by the data.

Rust code

18 quartile_inc "QUARTILE.INC" (2, 2) [R "array", V "quart"] |s, a, sv| { quartile(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

• QUARTILE.EXC is the exclusive sibling, Hyndman–Fan type 6. It uses the rank k*(n+1) instead of 1 + k*(n-1), admits only quart in {1, 2, 3}, and is undefined on small samples. The two agree at quart = 2 and disagree at quart = 1 and 3 for essentially every data set. Neither supersedes the other; Excel publishes both because the statistical community uses both.
• QUARTILE is the legacy name, retained in the Compatibility category. Microsoft's replacement guidance points QUARTILE at QUARTILE.INC, and the two are documented as computing the same quantity. The Handbook does not treat that as an identity. A legacy alias and its modern name are two entry points that Excel is free to route to two different code paths; the observed agreement of a dotted name and its predecessor is a claim requiring evidence, not a definition. No such evidence is in the Handbook's record.
• MEDIAN is the quart = 2 case.
• SMALL and LARGE return order statistics without interpolation. When you want an actual data value rather than a quantile, they are the correct functions — QUARTILE.INC will happily return a number that is in no cell.
• Readers confuse QUARTILE.INC with PERCENTRANK.INC, which runs the map the other way: data value in, probability out.

Named variants

No named variants are recorded yet.