PERCENTILE.EXC function
Quick answer
PERCENTILE.EXC returns the value at a given percentile using the exclusive method, which leaves out the very bottom and top of the range: k must sit strictly between 0 and 1, and close to the ends of a short list it returns #NUM!.
- Syntax
=PERCENTILE.EXC(array, k)- Most common use
=PERCENTILE.EXC(B2:B9,0.25)returns 12.75 (The 25th percentile, exclusive method: 12.75)
Some statistics courses and tools use this method, so reach for it when your numbers need to match theirs.
- Available in
- Excel 2010 and later
- Category
- Statistical functions
- Returns
- A number; #NUM! when k is 0, 1, or too close to either end for the size of the list.
- Google Sheets
- Google Sheets has PERCENTILE.EXC too.
Syntax
=PERCENTILE.EXC(array, k)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The numbers. Text and blanks are skipped. |
k | Required | The percentile as a decimal strictly between 0 and 1. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =PERCENTILE.EXC(B2:B9,0.25) | 12.75 | The 25th percentile, exclusive method: 12.75 |
| D3 | =PERCENTILE.INC(B2:B9,0.25) | 14.25 | The inclusive method gives 14.25 for the same data |
| D4 | =PERCENTILE.EXC(B2:B9,0.05) | #NUM! | #NUM!: 8 values are too few for the 5th percentile with this method |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Ticket | Minutes | Result | |
| T-101 | 12 | 12.75 | |
| T-102 | 45 | 14.25 | |
| T-103 | 8 | #NUM! | |
| T-104 | 30 | ||
| T-105 | 22 | ||
| T-106 | 95 | ||
| T-107 | 15 | ||
| T-108 | 40 |
Practice PERCENTILE.EXC
Graded instantly, then re-checked on changed data.
Exclusive 20th percentile
E2, calculate the 20th percentile of the delivery times in B2:B10 using the exclusive method.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NUM! | k is 0 or 1, or below 1/(n+1) or above n/(n+1) for a list of n values. Use PERCENTILE.INC, or more data. |
| Doesn't match PERCENTILE | PERCENTILE and PERCENTILE.INC use the inclusive method. The two methods give different values, most of all on short lists. |
Tips
- With 9 values, the smallest k PERCENTILE.EXC accepts is 1/10 = 0.1, and the largest is 0.9.
Questions
When should I use PERCENTILE.EXC?
When you need to match a textbook or tool that uses the exclusive method, such as type 6 in R. Otherwise PERCENTILE.INC is the usual choice.
Why does PERCENTILE.EXC return #NUM!?
The list is too short for the k you asked for. With n values, k must be at least 1/(n+1) and at most n/(n+1).
Updated Checked by running: every formula on this page was run by our formula engine on .