Percentile Rank of a Value (PERCENTRANK)

Excel Formulas › Statistics

All versionsPERCENTRANK

“You scored in the 90th percentile.” PERCENTRANK tells you what fraction of the data a value sits above — turning a raw number into a standing within the group.

Quick formula: for the standing of a value in A2 within B2:B20:
=PERCENTRANK($B$2:$B$20, A2)
Returns 0 to 1 — 0.9 means the value is higher than 90% of the data. Multiply by 100 for a percentile.

Functions used (tap for the full reference guide):

PERCENTRANKRANK

The example

A score of 88 in a class — its percentile standing.

AB
1ScorePercentile
295100th
38875th
4600th

The formula

The value’s standing as a fraction:

=PERCENTRANK($B$2:$B$20, A2) // 0.75 → higher than 75% of scores

How it works

PERCENTRANK reports relative standing, not raw value:

  1. Give it the data range and the value whose standing you want.
  2. It returns a number from 0 to 1: the fraction of the data the value is greater than or equal to.
  3. Multiply by 100 to express it as a percentile — 0.75 becomes the 75th percentile.
  4. An optional 3rd argument sets the number of significant digits (default 3).

Rank vs percentile rank: RANK gives an ordinal position (3rd of 20); PERCENTRANK gives relative standing (better than 85%). For exact-match-only behavior, the 365-era PERCENTRANK.EXC excludes the 0 and 1 endpoints, while PERCENTRANK.INC (= the classic PERCENTRANK) includes them.

Try it: interactive demo

Live demo

Enter data, then a value to rank.

Percentile rank:

Variations

As a percentile number

0–100 instead of 0–1:

=PERCENTRANK($B$2:$B$20, A2) * 100

Exclusive version

Excludes the endpoints:

=PERCENTRANK.EXC($B$2:$B$20, A2)

Ordinal rank instead

Position, not percentile:

=RANK(A2, $B$2:$B$20)

Pitfalls & errors

0 and 1 at the extremes. The classic (inclusive) PERCENTRANK returns exactly 0 for the minimum and 1 for the maximum. Use PERCENTRANK.EXC if you need values strictly between.

Value out of range. If the value is smaller than the min or larger than the max, the EXC version returns #N/A; the inclusive one clamps to 0 or 1.

Interpolates between points. For values not exactly in the data, PERCENTRANK interpolates — useful, but it means the result isn’t a simple count.

Practice workbook

📊
Download the free Percentile Rank of a Value (PERCENTRANK) practice workbook
A percentile-rank sheet with the percentage, exclusive, and ordinal-rank variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the percentile rank of a value in Excel?
Use =PERCENTRANK(range, value). It returns a fraction from 0 to 1 — the proportion of the data the value sits above. Multiply by 100 for a percentile.
What's the difference between PERCENTRANK.INC and PERCENTRANK.EXC?
INC (the classic PERCENTRANK) includes the 0 and 1 endpoints; EXC excludes them and returns values strictly between 0 and 1.
How is PERCENTRANK different from RANK?
RANK gives an ordinal position (e.g. 3rd of 20). PERCENTRANK gives relative standing as a percentile (e.g. better than 85% of values).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Rank values · Percentile & quartile · Z-score (standardize)

Function references: PERCENTRANK · RANK