The PERCENTILE.INC function
Returns the value below which a given share of the data falls, such as the 90th percentile. The share k is a fraction from 0 to 1.
Syntax
PERCENTILE.INC(array, k)- array
- The range of numbers.
- k
- The percentile as a fraction, from 0 to 1. 0.9 is the 90th percentile, 0.5 the median.
Examples
D1
=PERCENTILE.INC(1B2:B6,20.9)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Sales | 132 | |
| 2 | Mon | 120 | ||
| 3 | Tue | 95 | ||
| 4 | Wed | 140 | ||
| 5 | Thu | 110 | ||
| 6 | Fri | 80 |
In plain words: The value that 290% of 1the sales in B2:B6 are at or below.
D1=PERCENTILE.INC(B2:B6,0.25)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Sales | 95 | |
| 2 | Mon | 120 | ||
| 3 | Tue | 95 | ||
| 4 | Wed | 140 | ||
| 5 | Thu | 110 | ||
| 6 | Fri | 80 |
D1=PERCENTILE.INC(B2:B6,0.5)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Sales | 110 | |
| 2 | Mon | 120 | ||
| 3 | Tue | 95 | ||
| 4 | Wed | 140 | ||
| 5 | Thu | 110 | ||
| 6 | Fri | 80 |
Questions
What is the difference between PERCENTILE.INC and PERCENTILE.EXC?
PERCENTILE.INC accepts any k from 0 to 1, so 0 gives the smallest value and 1 the largest. PERCENTILE.EXC leaves out the ends. PERCENTILE.INC gives the same results as the older PERCENTILE.
Why does the result not appear in my data?
When the percentile falls between two values, Excel interpolates. Here the 90th percentile lies between 120 and 140, so the result is 132.