Percentile Calculator: Excel .INC, .EXC & Percentile Rank
What is the 90th percentile of these 12 test scores: 55, 62, 64, 68, 70, 71, 74, 77, 80, 85, 88 and 94? Excel’s PERCENTILE.INC says 87.7. The (n + 1) method in many statistics textbooks says 92.2. The nearest-rank method says 88. All three are correct. They follow different definitions, and with a short list the definitions don’t land in the same place.
This calculator finds any percentile with the method you choose and shows the other two beside it, so you can match whatever your class, report or spreadsheet uses. It also works the other way: give it a value and it tells you that value’s percentile rank in the list.
Separate values with commas, spaces or new lines (so type 1200, not 1,200). The order doesn't matter: the calculator sorts them.
Three Answers for One Percentile: The Methods Side by Side
For the 12 scores above, here is every common percentile under each method:
| Percentile | Inclusive (Excel .INC, Google Sheets) | Exclusive (n + 1) | Nearest rank |
|---|---|---|---|
| 10th | 62.2 | 57.1 | 62 |
| 25th (first quartile) | 67 | 65 | 64 |
| 50th (median) | 72.5 | 72.5 | 71 |
| 75th (third quartile) | 81.25 | 83.75 | 80 |
| 90th | 87.7 | 92.2 | 88 |
The gaps are biggest in the tails, where the methods disagree most about how far past the last few values to reach. With hundreds of values the three columns would almost match. With 12, the 90th percentile can be anything from 87.7 to 92.2 depending on the rule.
None of these is the “real” percentile. A percentile is a cut-off that puts about k% of the data below it, and with only 12 values no single number can put exactly 90% below it. 90% of 12 is 10.8 values. Each method resolves that in its own way. What matters is to use one method consistently and to say which one.
The Inclusive Method, Step by Step
This is the method behind Excel’s PERCENTILE.INC (and the older PERCENTILE), Google Sheets’ PERCENTILE, and the default in Python’s NumPy. It treats the smallest value as the 0th percentile and the largest as the 100th.
- Sort the values from smallest to largest.
- Find the position: position = k ÷ 100 × (n − 1) + 1.
- If the position is a whole number, that value is the answer. If not, move the matching fraction of the way from one value to the next.
For the 90th percentile of the 12 scores:
- Position = 90 ÷ 100 × 11 + 1 = 10.9.
- The 10th value is 85 and the 11th is 88.
- Go 0.9 of the way from 85 to 88: 85 + 0.9 × (88 − 85) = 87.7.
For the 25th percentile, the position is 0.25 × 11 + 1 = 3.75. That is three quarters of the way from the 3rd value (64) to the 4th (68): 64 + 0.75 × 4 = 67.
Because 0 and 100 always land on the ends, this method gives an answer for every percentile, even with two values.
The Exclusive (n + 1) Method, and Why It Sometimes Has No Answer
Many statistics textbooks, Excel’s PERCENTILE.EXC and Minitab use a slightly different position:
position = k ÷ 100 × (n + 1)
For the 90th percentile: 0.9 × 13 = 11.7, so go 0.7 of the way from the 11th value (88) to the 12th (94): 88 + 0.7 × 6 = 92.2.
The idea behind n + 1 is that a sample rarely contains the true smallest or largest value. So the method leaves room beyond both ends, and the lowest value counts as the 1/(n + 1) point, not the 0th percentile.
That has a cost. With 12 values, the smallest value sits at the 100 ÷ 13 ≈ 7.69th percentile and the largest at the 92.31th. Anything outside that range has no answer, because it would need a value outside the data. Excel’s PERCENTILE.EXC returns #NUM! there. Microsoft’s help page says it returns #NUM! when k is 0 or less, 1 or more, or a value it can’t interpolate for. Try the 95th percentile of these 12 scores with the exclusive method, and the calculator tells you the reachable range instead of guessing.
The fix is either more data or a different method. With 99 values, the exclusive method reaches from the 1st to the 99th percentile.
Nearest Rank: Always One of Your Own Values
The simplest rule skips interpolation:
rank = k ÷ 100 × n, rounded up to a whole number
For the 90th percentile: 0.9 × 12 = 10.8, which rounds up to 11. The 11th value is 88.
Nearest rank always returns a value that is actually in the list. That suits data where an in-between number would mean nothing, such as shoe sizes, a count of goals, or a ranking position. For the median of an even-sized list it gives a different answer from the other two methods: 0.5 × 12 = 6, so the median is the 6th value, 71, not the 72.5 you get by averaging the 6th and 7th.
Percentile vs Percentile Rank
The two words sound alike and get swapped, but they go in opposite directions:
- A percentile starts from a percentage and gives a value. “The 90th percentile is 87.7.”
- A percentile rank starts from a value and gives a percentage. “A score of 80 has a percentile rank of about 71.”
Use the Percentile rank of a value tab for the second kind. For a score of 80 among the 12 scores, 8 values are below it and the 80 itself is the ninth:
| Rule | Calculation | Percentile rank |
|---|---|---|
| Share below | 8 ÷ 12 | 66.67% |
| Share at or below | 9 ÷ 12 | 75% |
| Midpoint (ties count half) | (8 + 0.5) ÷ 12 | 70.83% |
| Excel PERCENTRANK.INC | 8 ÷ 11 | 72.73% |
Once again there is more than one rule. The “share below” and “at or below” versions differ by one value’s worth, because they disagree about whether the value itself counts. The midpoint rule splits the difference. Excel divides by n − 1 instead of n, which matches its inclusive percentile: the smallest value gets a rank of 0 and the largest a rank of 1.
A percentile rank is a position in a group, not a mark out of 100. A rank of 67 on a hard test can mean a lower percentage score than a rank of 40 on an easy one.
Ties: When the Value Appears More Than Once
Ties make the rules split further. Take six scores: 70, 75, 75, 75, 80, 90. What percentile rank is 75?
- Below it: only the 70, so 1 ÷ 6 = 16.67%.
- At or below it: the 70 and all three 75s, so 4 ÷ 6 = 66.67%.
- Midpoint: the 70 plus half of the three 75s, (1 + 1.5) ÷ 6 = 41.67%.
Same value, same list, and the answers run from 16.67% to 66.67%. The midpoint rule is the middle ground: it doesn’t push a tied group to either end. The calculator shows all three. When the value you type is in the list, it leads with the midpoint; when it isn’t, the first three rules agree.
If you report a percentile rank for data with many ties, such as whole-number scores out of 10, say which rule you used. Otherwise two people can quote different ranks for the same student and both be right.
Why Excel Shows 0.555 Instead of 0.556
Excel’s PERCENTRANK.INC has an optional third argument, significance, and when you leave it out the function keeps three digits. It cuts the extra digits off rather than rounding.
Microsoft’s own example uses ten values: 13, 12, 11, 8, 4, 3, 2, 1, 1, 1. The rank of 4 is 5 ÷ 9 = 0.5555…, and Excel shows 0.555, not 0.556. The rank of 8 is 6 ÷ 9 = 0.6666…, shown as 0.666.
For a value between two data points, Excel interpolates between their ranks. The rank of 5 lies a quarter of the way from 4 (rank 5/9) to 8 (rank 6/9), which gives 21/36 = 0.58333…, shown as 0.583.
The calculator shows the full value and, next to it, what Excel will display. If you need more digits in Excel, add the third argument: =PERCENTRANK.INC(A2:A11, 4, 6) keeps six.
The Same Three Methods in Excel and Google Sheets
If the scores are in cells A2 to A13, these formulas match the calculator:
| Method | Formula | Result for the 90th percentile |
|---|---|---|
| Inclusive | =PERCENTILE.INC(A2:A13, 0.9) | 87.7 |
| Exclusive | =PERCENTILE.EXC(A2:A13, 0.9) | 92.2 |
| Nearest rank | =SMALL(A2:A13, ROUNDUP(0.9*COUNT(A2:A13), 0)) | 88 |
Two details trip people up. The spreadsheet functions take k as a fraction, so the 90th percentile is 0.9, not 90. And there is no built-in nearest-rank function, which is why the third row builds it from SMALL and ROUNDUP: the 11th smallest value is 88.
For percentile ranks, =PERCENTRANK.INC(A2:A13, 80) returns 0.727 for the score of 80, using the n − 1 divisor described above. Google Sheets accepts PERCENTILE and PERCENTRANK with the same arguments.
Quartiles, Deciles and the Median Are Percentiles Too
Several familiar statistics are percentiles under other names:
- The median is the 50th percentile.
- The first and third quartiles (Q1 and Q3) are the 25th and 75th percentiles. The gap between them, the interquartile range, holds the middle half of the data. For the 12 scores with the inclusive method, that is 81.25 − 67 = 14.25 points.
- Deciles are the 10th, 20th, … 90th percentiles. The calculator’s quick table lists the 10th, 25th, 50th, 75th and 90th at once.
Because quartiles are percentiles, they inherit the same method problem. Q1 for the 12 scores is 67 by the inclusive method, 65 by the exclusive one, and 64 by nearest rank. If your answer differs from the back of the book by a point or two, check the method before checking your arithmetic. The mean, median and mode calculator also gives quartiles and the interquartile range for a list, using the median of each half, which is yet another variant.
When You Have a Mean and SD Instead of a List
Sometimes there’s no list to rank, only a summary: “the mean is 500 and the standard deviation is 100”. If the data follow a bell-shaped normal curve, you can still find percentiles from those two numbers. The 90th percentile is the mean plus 1.2816 standard deviations: 500 + 1.2816 × 100 ≈ 628.16.
That calculation uses the normal curve, not your data, so it is only as good as the bell-shape assumption. The z-score calculator does it in both directions: percentile to score, and score to percentile.
With the actual list in hand, it’s better to use this calculator. It makes no assumption about the shape of the data, so it works for skewed data too.
Reporting a Percentile So Nobody Misreads It
A percentile on its own can be read several ways. A clear report includes:
- The method, if the list is short. “90th percentile (Excel PERCENTILE.INC) = 87.7” can be checked. “90th percentile = 87.7” can’t.
- How many values it came from. With 12 values, the 90th percentile rests on just two scores near the top. With 1,200 values it is far more stable.
- Whether it’s a percentile or a percentile rank. “Scored at the 80th percentile” is a rank. “The 80th percentile score was 81” is a value.
- The direction. “Top 10%” means at or above the 90th percentile. In response times or waiting lists, where a lower number is better, a high percentile is the slow end, not the good end.
Never average percentile ranks across groups of different sizes. A student ranked 60 in a class of 10 and 80 in a class of 300 isn’t necessarily at the 70th percentile overall. To combine groups, pool the raw values and rank again.
Percentiles You Meet Outside the Classroom
Children’s growth charts. The CDC places a child’s BMI on a percentile scale for their age and sex, for ages 2 through 19. Below the 5th percentile counts as underweight. From the 5th up to the 85th is a healthy weight, the 85th up to the 95th is overweight, and the 95th or higher is obesity. Here the percentile compares the child with a fixed reference group, not with classmates.
Internet bills. Many data centers bill bandwidth by the 95th percentile. Usage is sampled every 5 minutes, which gives 8,640 samples in a 30-day month. The top 5%, 432 samples or 36 hours in total, are thrown away, and the highest remaining sample sets the bill. Short bursts are effectively free: an hour of peak traffic a day adds up to 30 hours in a month and falls inside the discarded 36. Two busy hours every day (60 hours) don’t.
Website speed. Engineers quote the 95th or 99th percentile response time alongside, or instead of, the average. An average hides the slow requests. A 99th percentile of 2 seconds says that 1 request in 100 took 2 seconds or longer.
