Percentile Calculator - Value, Rank, Excel INC and EXC

One list, one percentile, three different answers

Ask three programs for the 90th percentile of the same twelve delivery times, 18, 21, 22, 24, 25, 27, 28, 30, 33, 35, 41 and 52 minutes, and you can get 40.4, 48.7 and 41.0. None of them is a bug. Percentiles have several accepted definitions, and this calculator shows the three you are most likely to meet side by side: linear interpolation (Excel PERCENTILE.INC, Google Sheets, R and NumPy), exclusive interpolation (Excel PERCENTILE.EXC, Minitab, SPSS) and nearest rank. It works in both directions, from a percentile to a value and from a value to its percentile rank, and it prints the matching Excel figures, including the cases where Excel answers #NUM! or #N/A.

8.3 min

gap between the three definitions of P90 for the twelve delivery times

7.7th to 92.3rd

the only percentiles PERCENTILE.EXC accepts with 12 values

0.583

PERCENTRANK.INC of 5 in Microsoft's own example, reproduced to the digit

Seven fields, one of them a direction switch

Data - numbers separated by spaces, commas, semicolons or new lines, or a column pasted from a spreadsheet. Use a dot for decimals and leave out thousands separators: the tool refuses "1,000 2,000" rather than reading it as 1 and 2.
What to find - the value at a percentile you choose, or the percentile rank of a value you already have, such as your own score.
Percentile - any number from 0 to 100, decimals allowed. 50 is the median, 25 and 75 are the quartiles, 90 marks the top tenth.
Method - linear interpolation unless you need to match a specific program or rule. The other two are always shown in the comparison table anyway.
Value - used only for percentile rank. It does not have to appear in the list.
Decimal places - optional. Left empty, the tool follows the most precise number you typed, and very small measurements switch to scientific notation instead of collapsing to 0.0000.
Name of the data - optional, printed above the result so a screenshot keeps its context.

Percentile, percentile rank, quartile: two questions under three names

A percentile is a value. The 90th percentile of the delivery times is a number of minutes, about 40, below which roughly nine tenths of the deliveries fall. A percentile rank goes the other way: it starts from a value and tells you what share of the list sits below it. A delivery of 30 minutes has a percentile rank of 62.5 in that list. The two are inverses of each other, which is why the calculator has one switch for the direction rather than two separate tools.

Quartiles, deciles and the median are simply percentiles with their own names. The first quartile is P25, the median P50, the third quartile P75, and the ninth decile P90. Whatever a program calls them, it computes them with one of the definitions below, and that choice decides the digits you see.

None of this is the same as a percentage. A score of 80% on a test says how many questions were right; the 80th percentile says how the score compares with everyone else who sat the test. A result of 55% can be at the 90th percentile of a hard exam.

An atlas of percentile definitions

Statisticians Rob Hyndman and Yanan Fan listed nine sample quantile definitions in a paper in The American Statistician, and R still numbers them type 1 to type 9. Most software you will touch uses one of three. Write n for the number of values and p for the percentile as a fraction, so 0.9 for P90.

Definition	Position in the sorted list	Where you meet it	Always one of your values?	Works for every p?
Linear interpolation (R type 7)	1 + (n - 1)p	Excel PERCENTILE.INC and PERCENTILE, Google Sheets PERCENTILE, R default, NumPy default	No	Yes, 0 to 100
Exclusive interpolation (R type 6)	(n + 1)p	Excel PERCENTILE.EXC, Minitab, SPSS	No	Only from 1/(n + 1) to n/(n + 1)
Nearest rank	ceil(p × n), no interpolation	Textbooks, grading and pay rules, NumPy method "inverted_cdf"	Yes	Yes
Rule of thumb	To match a spreadsheet, use linear interpolation. To match SPSS or Minitab output, use exclusive. When the answer must be a value that actually occurred, use nearest rank.

A fractional position means "part of the way between two neighbors". For P30 of 1, 2, 3 and 4, linear interpolation lands at position 1 + 3 × 0.3 = 1.9, which is nine tenths of the way from the first value to the second, so the answer is 1.9. That is the example in Microsoft's help page for PERCENTILE.INC, and the calculator returns the same figure.

Twelve delivery times, every method at five percentiles

The same list as above, sorted: 18, 21, 22, 24, 25, 27, 28, 30, 33, 35, 41 and 52 minutes. Each cell is what the calculator returns with the method in the column header.

Percentile	Linear interpolation	Exclusive	Nearest rank
P10	21.1	18.9	21.0
P25, first quartile	23.5	22.5	22.0
P50, median	27.5	27.5	27.0
P75, third quartile	33.5	34.5	33.0
P90	40.4	48.7	41.0
Reading	Near the median the methods differ by half a minute; at P90 by 8.3 minutes, because the last gap in the list, 41 to 52, is the widest.

P95 shows the limit of the exclusive method. With 12 values it is defined only between the 7.7th and the 92.3rd percentile, so Excel's PERCENTILE.EXC returns #NUM! for P95 while PERCENTILE.INC gives 46.0. The calculator marks the exclusive row "(Excel: #NUM!)" and, if you picked that method, says that its figure has been clipped to the largest value, 52.

Patterns that hold for any list

The methods disagree most where the data is thinnest. In the middle of a list the neighbors are close together, so interpolating one way or another moves the answer very little. In the tails a single gap can be wide, and that gap is exactly what the definitions divide differently. For 1 to 10 the three P90 figures are 9.1, 9.9 and 9.0, less than one step apart; for the delivery times they spread over 8.3 minutes.

An extreme value can move one method and leave another alone. Replace the slowest delivery, 52 minutes, with 90. Linear interpolation keeps P90 at 40.4, because its position, 10.9, lies between the 10th and 11th values and never touches the 12th. The exclusive method sits at position 11.7, between the 11th and 12th values, and jumps from 48.7 to 75.3.

Nearest rank never invents a number. Its answer is always a value from your list: 41.0 at P90, 27.0 at the median. That makes it the natural choice for rules such as "the score of the 90th-percentile candidate", but it also means a small change in p can make the answer jump from one value to the next.

Excel's rank functions interpolate too. In Microsoft's example list 13, 12, 11, 8, 4, 3, 2, 1, 1, 1, the value 4 has PERCENTRANK.INC 0.555 and 8 has 0.666. The value 5 is not in the list, so Excel goes a quarter of the way from one to the other and returns 0.583, truncated rather than rounded to three decimals. The plain share of values below 5 is 60.0.

A percentile is not a share of the range. The delivery times run from 18 to 52 minutes, and 30 minutes is only 35% of the way along that range, yet its percentile rank is 62.5. Percentiles count values, not distance, so a list bunched at one end pushes most of them to the same side.

Percentile rank and the trouble with ties

For a value that appears once, the counting is simple. 30 minutes has 7 deliveries below it, 1 equal and 4 above, so "strictly below" gives 58.3, "at or below" gives 66.7, and the middle rule, below plus half of the ties, gives 62.5. Excel's PERCENTRANK.INC reports 0.636 (7 of the 11 other values) and PERCENTRANK.EXC 0.615 ((7 + 1) / 13).

Ties widen the gap between the rules. In 10, 20, 30, 30, 30, 40, 50, 60 the value 30 has 2 values below and 3 equal to it, so its rank is 25.0, 43.8 or 62.5 depending on the rule. The usual formula for test norms takes the middle one, which is why the calculator puts it in the headline and shows the other two underneath. A value outside the list gets 0 or 100 however far away it is, and Excel returns #N/A for it; the calculator says so rather than printing a number that looks precise.

Where percentiles show up, and which definition they use

Setting	What the percentile describes	Can you recompute it here?
Your own spreadsheet column	Position inside your data	Yes, with linear interpolation for PERCENTILE.INC
Standardized test score report	Position in a reference group chosen by the test maker	No, the reference scores are not public
Child growth charts	Position against CDC or WHO reference curves for the same age and sex	No, use a growth-chart tool
Website response times (P95, P99)	The slow tail of your own measurements	Yes, once you know which method the monitoring tool uses
Salary surveys	Pay at a given share of the surveyed jobs	Only with the raw survey data

The pattern is simple: if you have the numbers, this tool reproduces the percentile; if the percentile compares you with someone else's population, the answer depends on data you do not hold.

Percentile questions, answered briefly

Does the 90th percentile mean the top 10%?

Roughly. About 90% of the values lie below it and about 10% above, but with a short list "about" matters: in the twelve delivery times, 10 of 12 values, 83.3%, lie below the linear-interpolation P90 of 40.4.

Which method should I pick?

Linear interpolation, unless you have to agree with a figure produced elsewhere. Then pick the method that program uses: exclusive for SPSS, Minitab and PERCENTILE.EXC, nearest rank for rules that require a value from the list.

Why does Excel return #NUM! for PERCENTILE.EXC?

Because the exclusive method needs p between 1/(n + 1) and n/(n + 1). With 12 values that is the 7.7th to the 92.3rd percentile, so P95 or P5 is out of range. Use PERCENTILE.INC or add more data.

Is the median always the 50th percentile?

With linear and exclusive interpolation, yes: both give 27.5 for the delivery times, the midpoint of 27 and 28. Nearest rank gives 27.0, because it refuses to go between two values.

Can a value be at the 100th percentile?

By the "at or below" rule, the largest value always is. By the middle rule it is not: the slowest delivery, 52, has a rank of 95.8, because half of it counts as a tie with itself. P100 as a value is simply the maximum.

How many values do I need for a meaningful P99?

At least 100, and more is better. With fewer, P99 is mostly a statement about your largest value: for the delivery times it is 50.8 by linear interpolation, just below the maximum of 52.

Does the order of the numbers matter?

No. The calculator sorts the list first, so a column pasted straight from a spreadsheet gives the same answer as a sorted one.

Related tools

Coefficient of Variation Calculator

Relative spread of a list, from raw numbers or a mean and standard deviation - See calculator

Interquartile Range Calculator

Quartiles, the IQR, outlier fences and a box plot - See calculator

Median Calculator

The middle value of a sorted list, the 50th percentile - See calculator

Mode Calculator

The most frequent value, bimodal lists and lists with no mode - See calculator

Standard Deviation Calculator

How far values spread around the mean - See calculator

Average Calculator

Arithmetic, weighted, geometric and harmonic means - See calculator

Child Weight Percentile Calculator

A child's weight against reference growth curves - See calculator

Wilcoxon Test Calculator

Compares two groups by ranks instead of means - See calculator