QUARTILE.EXC

What it does

QUARTILE.EXC(array, quart)

QUARTILE.EXC(array, quart) returns a sample quantile of the data in array under the exclusive convention: the one that treats the sample as an interior slice of an unseen population, and therefore declines to identify any quantile with the observed extremes.

Sort the numeric values of array ascending as x_(1) <= … <= x_(n), and let k = quart/4 with quart in {1, 2, 3}, so k is 1/4, 1/2 or 3/4. Define the rank

h  =  k * (n + 1)

and, writing j = floor(h) and g = h - j,

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

The n + 1 is the whole difference from QUARTILE.INC, and it changes the function's character rather than just its answer. This is type 6 in the Hyndman–Fan taxonomy, the definition used by Minitab and by SPSS, and it comes from assigning the order statistic x_(i) the plotting position i/(n+1) — the expected value of the i-th uniform order statistic. Under that assignment the sample never reaches probability 0 or 1, because no finite sample can be expected to contain the population extremes.

Three consequences follow directly from the rank formula, and all three are user-visible:

1. There is no quart = 0 and no quart = 4. k = 0 gives h = 0 and k = 1 gives h = n+1, and both lie outside [1, n]. The minimum and maximum are simply not quantiles under this convention. QUARTILE.EXC admits only 1, 2, 3.
2. The function is undefined on small samples. h = (n+1)/4 >= 1 requires n >= 3. With n = 1 or n = 2 the first quartile's rank falls below the first order statistic and the result is #NUM! — even though the data is perfectly well formed and QUARTILE.INC answers happily. The mirror condition 3(n+1)/4 <= n also requires n >= 3.
3. QUARTILE.EXC is more extreme than QUARTILE.INC in both tails. For the same data, QUARTILE.EXC(A,1) <= QUARTILE.INC(A,1) and QUARTILE.EXC(A,3) >= QUARTILE.INC(A,3), so the exclusive interquartile range is the wider of the two. That is the intended behaviour: the exclusive convention is making an allowance for the population beyond the sample.

At quart = 2 the two conventions coincide. The exclusive rank is (n+1)/2 and the inclusive rank is 1 + (n-1)/2, which is the same number, so

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

for every n >= 3. The median is the one quartile everybody agrees on, which is exactly why it is the one people report.

There is no single correct definition of a sample quartile. Hyndman and Fan catalogue nine conventions in use. Excel publishes two of them under two names and lets the caller choose. A reader comparing Excel against R, SAS or SPSS should expect a different number, and should check which type the other tool used before calling anything wrong.

Arguments

array is consumed by scanning: text, logical values and blank cells in a scanned range are skipped rather than converted, so n is the count of numeric cells. See coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number.

• Fewer than three numeric values. #NUM!, for the structural reason above rather than because the data is malformed. This is the edge case that surprises people: a two-point sample has a perfectly good inclusive first quartile and no exclusive one.
• quart outside {1, 2, 3}. #NUM!. In particular QUARTILE.EXC(A, 0) and QUARTILE.EXC(A, 4) are errors, not the minimum and maximum.
• Repeated values are ordinary; a constant data set returns that constant at every admissible quart.
• Non-numeric cells in array are skipped and do not enlarge n — which means adding a text label to the top of a two-number column does not rescue it from #NUM!.
• Errors in array propagate as themselves.
• The result is an interpolated value and need not appear in the data.

How to implement

Numerical notes

The rank arithmetic is exact. h = k*(n+1) with k in {1/4, 1/2, 3/4} — all dyadic rationals, exactly representable in binary64 — and n+1 a small integer. The product is exact for every n within range, so floor(h) and h - floor(h) are exact and the branch on h integral versus fractional is never a near-miss. This is a real advantage of quartiles over general percentiles: PERCENTILE.EXC with k = 0.1 computes 0.1*(n+1) from a k that is not exactly one tenth, and its floor can therefore differ from the mathematically intended one at values of n where the product lands adjacent to an integer. QUARTILE.EXC cannot hit that class of bug.

The interpolation is evaluated as lower + g*(upper - lower) rather than (1-g)*lower + g*upper. The two are algebraically equal and differ in floating point. The form used is monotone in g and stays inside the bracket for lower <= upper, which is the property a quantile needs; it can round short of upper at g near 1, but the g = 0 path returns x_(j) directly so the exact-endpoint case is not routed through the interpolation at all.

Where the answers actually differ from a competitor. The gap between type 6 and type 7 at quart = 1 is (x_(j+1) - x_(j)) scaled by a rank difference of (n+1)/4 - (1 + (n-1)/4), which is (2 - n)/4 + … — in words, it shrinks like 1/n. On a large sample the two conventions converge; on a sample of four or five they can differ by most of a gap between adjacent observations. Every disagreement between Excel and another package about quartiles that is worth investigating is on small n, and every one that is not is on large n.

Sorting and cost. A full O(n log n) sort where a linear-time selection would do, and a total-order comparison that treats incomparable pairs as equal — reachable only if a NaN crosses in from an add-in boundary, since the worksheet value universe has none.

Rust code

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

• QUARTILE.INC is the inclusive sibling, Hyndman–Fan type 7 and the default of R and NumPy. It admits quart 0 through 4, is defined for every non-empty sample, and returns a narrower interquartile range. The two agree only at quart = 2. Neither supersedes the other.
• QUARTILE, the legacy Compatibility-category name, corresponds to the inclusive convention, not this one. QUARTILE.EXC has no legacy predecessor: it was added when the dotted names arrived, because the exclusive convention was not previously available in Excel at all. That asymmetry is worth stating, since it means the .INC/.EXC pair is not a renaming of one old function into two — it is one old function plus one genuinely new one.
• MEDIAN is the quart = 2 case and the point of agreement with QUARTILE.INC.
• SMALL and LARGE return order statistics without interpolation, and are what you want if the answer must be a value that actually occurs.
• Readers reach for QUARTILE.EXC expecting "the quartile excluding the median", which is a third, different convention (Tukey's hinges, Hyndman–Fan type 2-ish behaviour on odd n). The EXC does not refer to excluding the median; it refers to the probability interval being open at both ends.

Named variants

No named variants are recorded yet.