In Excel, the PERCENTILE.EXC and PERCENTILE.INC functions are used to find the k-th percentile of a dataset. The k-th percentile is the value below which a certain percentage of observations in a dataset falls. While both functions are used to calculate percentiles, they differ in how they handle the percentile range.
- PERCENTILE.EXC: This function excludes the percentile 0 and 1, meaning that it calculates percentiles between 0% and 100%, excluding the extremes.
- PERCENTILE.INC: This function includes the percentile 0 and 1, meaning that it calculates percentiles between 0% and 100%, including the extremes.
Syntax
| Syntax | Description |
|---|---|
| PERCENTILE.EXC(array, k) | Returns the k-th percentile of values in a range, excluding the 0th and 100th percentiles. |
| PERCENTILE.INC(array, k) | Returns the k-th percentile of values in a range, including the 0th and 100th percentiles. |
Example
Suppose you have a dataset representing the scores of students in a math test. You want to calculate the 25th, 50th (median), and 90th percentiles using both PERCENTILE.EXC and PERCENTILE.INC.
| Student | Score |
|---|---|
| A | 56 |
| B | 78 |
| C | 85 |
| D | 93 |
| E | 62 |
| F | 89 |
| G | 70 |
| H | 81 |
You can use the PERCENTILE.EXC and PERCENTILE.INC functions to calculate the 25th, 50th, and 90th percentiles for the scores.
| Percentile | Function | Formula |
|---|---|---|
| 25th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B9, 0.25) |
| 50th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B9, 0.50) |
| 90th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B9, 0.90) |
| 25th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B9, 0.25) |
| 50th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B9, 0.50) |
| 90th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B9, 0.90) |
Practice Exercise 1
In this exercise, you have a dataset representing the monthly sales revenue for a company. Calculate the 10th, 50th, and 90th percentiles using the PERCENTILE.EXC and PERCENTILE.INC functions.
Task: Use the data below to calculate the 10th, 50th, and 90th percentiles.
Practice Exercise 2
In this exercise, you have a dataset representing the completion times (in minutes) for a group of employees to finish a specific task. Calculate the 15th, 50th, and 85th percentiles using the PERCENTILE.EXC and PERCENTILE.INC functions.
Task: Use the data below to calculate the 15th, 50th, and 85th percentiles.
Solution for Exercise 1
In the first exercise, the monthly revenue data is used to calculate the 10th, 50th, and 90th percentiles. Below are the formulas and their explanations.
| Percentile | Function | Formula |
|---|---|---|
| 10th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B13, 0.10) |
| 50th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B13, 0.50) |
| 90th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B13, 0.90) |
| 10th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B13, 0.10) |
| 50th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B13, 0.50) |
| 90th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B13, 0.90) |
Solution for Exercise 2
In the second exercise, the completion times for employees are analyzed to determine the 15th, 50th, and 85th percentiles. Below are the formulas and their explanations.
| Percentile | Function | Formula |
|---|---|---|
| 15th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B9, 0.15) |
| 50th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B9, 0.50) |
| 85th Percentile | PERCENTILE.EXC | =PERCENTILE.EXC(B2:B9, 0.85) |
| 15th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B9, 0.15) |
| 50th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B9, 0.50) |
| 85th Percentile | PERCENTILE.INC | =PERCENTILE.INC(B2:B9, 0.85) |
These explanations alongside the formulas provide a clear understanding of how each percentile is calculated using the two different functions.