QUARTILE.INC Function

Returns a quartile of a data set (0 = minimum, 4 = maximum).

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Descriptive Statistics module

Overview

Quartiles split sorted data into four equal parts. QUARTILE.INC returns quartile 0 (minimum), 1 (25th percentile), 2 (median), 3 (75th percentile) or 4 (maximum).

The .INC version includes the end points (0 and 1, the smallest and largest values); the .EXC version excludes them and needs k strictly between them, so it can't reach the minimum or maximum. Both interpolate between values when k falls between two data points.

Syntax

=QUARTILE.INC(array, quart) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range of numbers.
quartYes0, 1, 2, 3 or 4.

Examples

Example 1 — Five-number summary

The five quartiles describe the spread of the sales.

E2fx=QUARTILE.INC($C$2:$C$8,D2)
ABCDE
1RepRegionSalesQuartileValue
2AnaNorth62000039800
3BenSouth48500148500
4CyNorth50000250000
5DiEast71250358700
6EdSouth39800471250
7FayNorth55400
8GusEast48500
=QUARTILE.INC($C$2:$C$8,D2) Explain

Example 2 — Interquartile range (IQR)

The middle 50% of results spans Q3 − Q1.

=QUARTILE.INC(C2:C8,3)-QUARTILE.INC(C2:C8,1) Explain

Returns 10,200. Values more than 1.5 × IQR beyond Q1 or Q3 are often treated as outliers.

Common mistakes

  • #NUM! quart is below 0 or above 4.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.