QUARTILE in Google Sheets: Five-Number Summaries and Transparent Outlier Screens

• Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; this guide uses the web worksheet workflow.
• Checked: October 14, 2026; official QUARTILE reference. Documentation and independent calculations, with no native Sheets execution or wider client certification.

=QUARTILE(data,quartile_number)

Both arguments are required. Google’s reference defines the selectors as:

The official catalogue directs QUARTILE.INC to QUARTILE. The dedicated page uses nearest-value wording without a detailed interpolation equation. To avoid treating that short wording as a complete boundary specification, these worked quartiles fall exactly on observed positions in nine-value datasets.

Fractional selectors, invalid-selector error codes and ambiguous interpolation cases remain unverified; do not import another application’s truncation rules.

Enter 0,10,20,30,40,50,60,70,80 in A2:A10. Put selectors 0,1,2,3,4 in C2:C6, then enter this formula in D2:

=QUARTILE($A$2:$A$10,C2)

Fill through D6. Expected summary: 0,20,40,60,80. The minimum and maximum are the first and last values; the median is the fifth. The first and third quartile controls are the third and seventh observations in this numeric model.

The direct controls =MIN(A2:A10), =MEDIAN(A2:A10) and =MAX(A2:A10) give 0,40,80, matching selectors 0,2,4. These checks help detect an accidentally shifted data range.

=QUARTILE(A2:A10,3)-QUARTILE(A2:A10,1)

Expected interquartile range: 40, from 60-20. The full range is 80, from maximum minus minimum. They describe different parts of the distribution.

The interquartile range has the same units as the data. It is neither a variance nor the number of records between two cutoffs. If the inputs are minutes, report an IQR of 40 minutes.

A commonly used screening rule places fences at first quartile minus 1.5 times IQR and third quartile plus 1.5 times IQR. For this fixture, the independent arithmetic gives:

• Lower fence: 20-1.5*40=-40
• Upper fence: 60+1.5*40=120

No original observation falls outside those fences. This is an analyst-selected screening rule, not automatic proof that values outside it are erroneous.

Replace the final value 80 with 200. The interior quartile positions still give 20 and 60, so the IQR remains 40 and the upper fence remains 120. The new 200 is flagged; all other values remain inside.

Keep the original record and inspect its context before deciding whether to correct or exclude it. A legitimate extreme value can still trigger a screening rule.

Restore the original maximum 80. The third quartile is 60. Values strictly greater than 60 are 70 and 80, two of nine observations. Values at or above 60 are 60,70,80, three of nine.

=COUNTIF(A2:A10,">"&QUARTILE(A2:A10,3))
=COUNTIF(A2:A10,">="&QUARTILE(A2:A10,3))

Expected counts: 2 and 3. A cutoff at the 75% mark does not guarantee exactly 25% of a finite set under either comparison. Ties can add another layer to that policy decision.

Transform every original value with 2*x+5, producing 5,25,45,65,85,105,125,145,165. The corresponding summary is 5,45,85,125,165. The IQR becomes 80, twice the original, while the added constant changes the location but not the spread.

The worked inputs are ordinary complete numbers. Text, Booleans, empty ranges, formula-empty results and native mixed-error behavior remain outside this source-backed model. PERCENTILE provides separately documented interpolation; its details should not be assumed to settle every QUARTILE edge case.

• Google QUARTILE reference.
• Editorial testing standards.

Examples use fictional data, English function names and comma argument separators. Expected results are independent calculations or explicitly documented rules, not native Sheets captures. Check spreadsheet locale when reproducing punctuation-sensitive formulas.