QUARTILE.EXC in Google Sheets: Exclusive Quartile Summaries

=QUARTILE.EXC(data, quartile_number)

The Google QUARTILE.EXC reference defines data as an array or range and quartile_number as the requested quartile:

Both arguments are supplied. Google excludes selectors 0 and 4 and documents truncation of a noninteger selector. Use explicit integers 1, 2 and 3 to avoid accidental changes in meaning.

The exclusive suffix is part of the statistical convention. It is not an instruction to remove the smallest and largest observations from the input range before calculating.

Enter these synthetic observations in A2:A12, one per cell:

4
7
9
12
15
18
20
24
28
31
36

Put 1, 2 and 3 in C2:C4. In D2, enter and fill down:

=QUARTILE.EXC($A$2:$A$12,C2)

The input is sorted here to make the check easy to follow. Sorting is not an additional function argument. The same multiset of observations represents the same distribution regardless of its row order.

For eleven ordered observations under the exclusive convention, the quartile positions are 3, 6 and 9. These correspond to (n+1)*p at p = 0.25, 0.5 and 0.75. Reading the third, sixth and ninth values of the stated dataset gives 9, 18 and 28.

This position rule independently reproduces Google’s own eleven-value example, whose printed quartiles are 15, 40 and 43. Google’s linked PERCENTILE.EXC reference also provides numerical interpolation examples consistent with exclusive n-plus-one positions.

The original examples here deliberately use quartile positions that land on observations. This avoids presenting untested small-sample boundary behavior as a universal rule. Percentile algorithms differ, so a result from another convention need not agree.

With Q1 in D2 and Q3 in D4, calculate the interquartile range in D6:

=D4-D2

Expected result: 19. NIST’s exploratory box-plot rule places inner fences 1.5 interquartile ranges below Q1 and above Q3. Calculate them in D7 and D8:

=D2-1.5*D6
=D4+1.5*D6

Expected fences: -19.5 and 56.5. In B2, enter and fill down:

=IF(A2<$D$7,"Review",IF(A2>$D$8,"Review","Within fences"))

Every observation in the original dataset is within these fences. If the final value 36 is replaced with 80, the quartiles remain 9, 18 and 28 for this specific eleven-value dataset, so 80 is flagged for review.

The fences are an exploratory rule, not an error detector or a requirement to delete values. A flagged observation may be valid. The underlying data, measurement process and analysis purpose determine the appropriate action.

For the original data:

=QUARTILE.EXC(A2:A12,2.9)

has expected result 18, following Google’s documented truncation to selector 2. It does not ask for the 72.5th percentile or interpolate between the second and third quartiles.

Use a percentile function for a freely chosen probability level. QUARTILE.EXC’s second argument selects one of three quartile positions, not a probability such as 0.25.

Google distinguishes QUARTILE.EXC from QUARTILE.INC. Do not assume changing the suffix merely enables selectors 0 and 4 while preserving every interior result. The statistical convention matters when comparing summaries across files.

Check the selector before changing the dataset. Values outside the documented exclusive 0-to-4 selector interval are unsupported. A selector of 0.25 is not the way to request Q1. Use 1.

The reviewed individual page does not establish a full native error matrix for tiny samples, nonnumeric entries, blanks or constant data. The related PERCENTILE.EXC page documents interpolation limits for that separate function; this guide does not silently transfer every error behavior to QUARTILE.EXC.

QUARTILE.EXC is documented in current rolling Google Sheets. Original introduction date, first release and last functioning numbered version were not established.

NOT_TESTED_IN_GOOGLE_SHEETS. Individual Google quartile and percentile references were inspected on October 17, 2026. Original order positions, quartiles, fences and screening outcomes were independently calculated. No native result or screenshot is claimed.

Related published guides: IF, COUNT, MIN and ROUND.