Excel PERCENTRANK: Find the Percentile Rank of a Score

PERCENTRANK in Excel tells you where a value sits in a list, as a share from 0 to 1: =PERCENTRANK.INC(B2:B9,74) returns 0.571, so a score of 74 beats about 57% of the other scores. It is the function to use for "what percentile is this score?" questions in test results, salary bands or sales rankings. This guide covers the INC and EXC versions, the formula behind them, and the errors you may see.

What PERCENTRANK does#

PERCENTRANK returns the percentile rank of a value: the share of the other values that are lower. A result of 0 means it is the lowest value, and 1 means it is the highest.

Excel has three versions:

All examples use these eight test scores in A1:B9:

The data does not need to be sorted.

How to use PERCENTRANK.INC#

The syntax has two required arguments and one optional one:

=PERCENTRANK.INC(array, x, [significance])

• array is the list of numbers, such as B2:B9.
• x is the value whose rank you want, such as 74 or B2.
• significance is the number of significant digits in the result. Leave it out and Excel uses 3.

To find Ana's percentile rank:

=PERCENTRANK.INC($B$2:$B$9,B2)

The result is 0.571. Format it as Percent and it shows 57.1%. Fill the formula down to rank every student: Dev (55) gets 0, Chloe (93) gets 1, and Eli (80) gets 0.714. The $ signs keep the list fixed while B2 moves down.

Note that Excel cuts the result to three digits rather than rounding it. The exact value is 0.5714…, and with =PERCENTRANK.INC($B$2:$B$9,B2,4) you get 0.5714.

The formula behind PERCENTRANK.INC#

For a value that appears in the list, PERCENTRANK.INC is a simple count. It divides the number of lower values by the number of values minus 1:

=COUNTIF(B2:B9,"<"&B2)/(COUNT(B2:B9)-1)

Four scores are lower than 74 (55, 62, 68 and 71), and there are 8 scores, so the result is 4 / 7 = 0.5714. COUNTIF counts the cells that meet a condition, and COUNT counts the numbers. The COUNTIF function guide and the COUNT function guide explain both.

Why divide by 7 and not 8? Ana is not compared with herself, so there are 7 other scores. The lowest score has 0 below it (0 / 7 = 0), and the highest has 7 below it (7 / 7 = 1).

Knowing this formula helps when you need to explain the result, or when a test asks you to work it out without the function. Tied values get the same percentile rank, because they have the same number of values below them.

PERCENTRANK.INC vs PERCENTRANK.EXC#

The two versions use slightly different scales. PERCENTRANK.EXC adds 1 to the count of lower values and divides by the number of values plus 1:

For 74, EXC is (4 + 1) / (8 + 1) = 0.5555…, cut to 0.555. EXC never returns exactly 0 or 1. Use it when your data is a sample and you don't want to claim a value is the absolute lowest or highest possible. For grades and internal rankings, INC is the usual choice and matches the older PERCENTRANK.

What happens with values that are not in the list?#

PERCENTRANK works for any value between the lowest and highest, even if it isn't in the list. Excel fills the gap by straight-line interpolation:

=PERCENTRANK.INC(B2:B9,77)

77 sits halfway between 74 (0.5714) and 80 (0.7143), so the result is about 0.6429, shown as 0.642. A value outside the range, such as 50 or 95, returns #N/A. If you score new values against old data, wrap the formula in IFERROR to show a message instead.

Percentile rank vs RANK and PERCENTILE#

Three related tools answer different questions about the same list:

RANK gives a place, which depends on how many values there are. Percentile rank gives a share, so you can compare across groups of different sizes. The guides to the RANK function and to percentiles and quartiles cover the other two. For the very top or bottom values, LARGE and SMALL are often simpler; see finding the top N values.

Common PERCENTRANK mistakes#

Check these if the result looks wrong:

• Swapping the arguments. The list comes first and the value second: PERCENTRANK.INC(B2:B9,74), not PERCENTRANK.INC(74,B2:B9).
• Expecting a rounded result. 0.5714 shows as 0.571, not 0.572. Add the significance argument for more digits.
• Text in the list. Numbers stored as text are ignored. Convert them with VALUE first.
• Reading 0.571 as 0.571%. It means 57.1%. Use Percent format to make that clear.
• Forgetting the $ signs when filling down, so the list shrinks row by row.

Practise it#

Percentile rank builds on two basics: knowing where the smallest and largest values are, and counting with conditions. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise LARGE and SMALL to find top and bottom values: you type the formula and it checks it instantly. Or try the exercise on this page and work out Ana's percentile rank with COUNTIF and COUNT.