QUARTILE.EXC function
QUARTILE.EXC returns a quartile using the exclusive method, which does not allow the minimum or maximum as a result. Choose it when a textbook or standard calls for that method.
Example
Read it as a sentence: find the first quartile of A1:A8 using the exclusive method, which gives 4.
Excel shows 4
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
QUARTILE.EXC(array, quart)
-
array(required) - The data.
-
quart(required) - 1 for first quartile, 2 median, 3 third quartile.
Returns: The requested quartile using the exclusive method.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=QUARTILE.EXC(Data!A1:A8,1) | 4 | Quart 1 |
=QUARTILE.EXC(Data!A1:A8,2) | 4.5 | Quart 2 |
=QUARTILE.EXC(Data!A1:A8,1.9) | 4 | Fractional quart truncated |
=QUARTILE.EXC(Data!A1:A8,{1,2,3}) | 4 | Array quart (spills) |
=QUARTILE.EXC({1,2},1) | #NUM! | Small set (exc) |
=QUARTILE.EXC(Data!A1:A8,0) | #NUM! | Quart 0 |
=QUARTILE.EXC(Data!A1:A8,4) | #NUM! | Quart 4 |
=QUARTILE.EXC(Data!A1:A8,5) | #NUM! | Quart 5 |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Watch out for
- Only 1, 2 and 3 are allowed.
- Needs enough data points or returns #NUM!.
Where it works
- Excel
- QUARTILE.EXC arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .