QUARTILE.EXC Function

Returns quartile 1, 2 or 3 using the exclusive method.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice QUARTILE.EXC in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

QUARTILE.EXC returns the 1st, 2nd or 3rd quartile using the (n + 1) method, matching many statistics packages. Quartiles 0 and 4 aren't available.

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.EXC(array, quart) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range of numbers.
quartYes1, 2 or 3.

Examples

Example 1 — Quartiles (exclusive)

Compare with QUARTILE.INC: Q3 is 62,000 here, against 58,700.

E2fx=QUARTILE.EXC($C$2:$C$8,D2)
ABCDE
1RepRegionSalesQuartileValue
2AnaNorth62000148500
3BenSouth48500250000
4CyNorth50000362000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=QUARTILE.EXC($C$2:$C$8,D2) Explain

Common mistakes

  • #NUM! quart is 0 or 4 (or anything other than 1–3), or there are too few values.

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.