PERCENTILE.EXC function
PERCENTILE.EXC finds a percentile using the exclusive method, which treats the data as a sample and cannot return the minimum or maximum. Use it when a method requires the exclusive definition.
Example
Read it as a sentence: find the 25th percentile 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
PERCENTILE.EXC(array, k)
-
array(required) - The data.
-
k(required) - Percentile as a fraction, strictly between 1/(n+1) and n/(n+1) at the extremes.
Returns: The k-th percentile, excluding the endpoints.
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 |
|---|---|---|
=PERCENTILE.EXC(Data!A1:A8,0.25) | 4 | K 0.25 |
=PERCENTILE.EXC(Data!A1:A8,{0.25,0.5,0.75}) | 4 | Array k (spills) |
=PERCENTILE.EXC(Data!B1:B8,0.5) | 30 | Mixed range |
=PERCENTILE.EXC({10,20,30,40,50},0.9) | #NUM! | K 0.9 array |
=PERCENTILE.EXC(Data!A1:A8,0) | #NUM! | K 0 |
=PERCENTILE.EXC(Data!A1:A8,1) | #NUM! | K 1 |
=PERCENTILE.EXC(Data!A1:A8,-0.1) | #NUM! | K negative |
=PERCENTILE.EXC(Data!A1:A8,1.1) | #NUM! | K above 1 |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
1
2
10
2
4
20
3
4
TRUE
4
4
5
5
abc
6
5
30
7
7
FALSE
8
9
40
Data
Watch out for
- Returns #NUM! if k is too close to 0 or 1 for the dataset size.
- Gives different answers from PERCENTILE.INC on the same data.
Where it works
- Excel
- PERCENTILE.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 .