Statistics function
Excel PERCENTILE.EXC function
PERCENTILE.EXC returns a percentile using the exclusive method, which places the sorted values at positions 1 to n out of n + 1. It matches the way many statistics packages and textbooks define percentiles. Because the ends are excluded, it cannot return the minimum or maximum for k close to 0 or 1 on small data.
=PERCENTILE.EXC(array, k)Arguments
array- The range of numbers. Text and empty cells are skipped.
k- The percentile as a fraction strictly between 0 and 1, and not so extreme that the position falls outside the data.
PERCENTILE.EXC examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | |
|---|---|---|
| 1 | Ticket | Minutes to resolve |
| 2 | T-31 | 22 |
| 3 | T-32 | 15 |
| 4 | T-33 | 30 |
| 5 | T-34 | 12 |
| 6 | T-35 | 25 |
| 7 | T-36 | 18 |
| 8 | T-37 | 35 |
| 9 | T-38 | 20 |
| 10 | T-39 | 28 |
Download this sheet as an Excel practice file (.xlsx)
Result: 16.5
With nine tickets the position is 10 x 0.25 = 2.5, halfway between the 2nd and 3rd fastest (15 and 18): 16.5 minutes.
Result: -1.5
PERCENTILE.INC puts the 25th percentile at 18, so the exclusive answer is 1.5 minutes lower. Report which method you used.
Result: 35
Position 10 x 0.9 = 9 is the slowest ticket, 35 minutes.
Result: #NUM!
Position 9.5 is past the last value, so PERCENTILE.EXC returns #NUM!. With nine values, k must be from 0.1 to 0.9.
How to use PERCENTILE.EXC
- Click the cell for the result.
- Type =PERCENTILE.EXC( and select the numbers.
- Type a comma and k as a fraction, such as 0.25.
- Press Enter. If you get #NUM!, k is too close to 0 or 1 for the amount of data.
Common PERCENTILE.EXC mistakes
Using k = 0 or 1
PERCENTILE.EXC never accepts 0 or 1. For the minimum or maximum use MIN, MAX or PERCENTILE.INC.
Extreme percentiles on small lists
With n values, k must be at least 1/(n+1) and at most n/(n+1). For a 99th percentile you need at least 99 values.
Mixing methods in one report
INC and EXC give different results on the same data. Pick one and use it for every percentile and quartile in the report.
PERCENTILE.EXC FAQ
Should I use PERCENTILE.INC or PERCENTILE.EXC?
Use PERCENTILE.INC unless you need to match software or a textbook that uses the exclusive method. INC works for any k from 0 to 1 and matches the older PERCENTILE function.
Why does PERCENTILE.EXC return #NUM!?
k is 0, 1 or outside 0 to 1, or the position (n + 1) x k falls before the first or after the last value.
Is there an exclusive quartile function?
Yes, QUARTILE.EXC. QUARTILE.EXC(range,1) is PERCENTILE.EXC(range,0.25).
Guides that use PERCENTILE.EXC
Related functions
PERCENTILE.INCValue at a percentileQUARTILE.INCQuartile of a listMEDIANMiddle valuePERCENTRANK.INCPercentile rank of a value
Updated