PERCENTRANK.EXC Function

Returns a value's rank as a percentage of the data set, excluding 0 and 1.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number between 0 and 1

Want to practice PERCENTRANK.EXC in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

PERCENTRANK.EXC is the reverse of PERCENTILE.EXC. It ranks on an (n + 1) scale, so even the smallest value scores above 0 and the largest below 1.

Results are cut (not rounded) to 3 decimal places unless you set significance.

Syntax

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

Arguments

ArgumentRequired?What it does
arrayYesThe range of numbers.
xYesThe value to rank. If it's between two data points, the result is interpolated.
significanceNoDecimal places to keep (default 3). Extra digits are cut off, not rounded.

Examples

Example 1 — Each rep's percentile rank (exclusive)

With 7 values, ranks run from 1/8 (0.125) to 7/8 (0.875).

D2fx=PERCENTRANK.EXC($C$2:$C$8,C2)
ABCD
1RepRegionSalesPct rank
2AnaNorth620000.75
3BenSouth485000.25
4CyNorth500000.5
5DiEast712500.875
6EdSouth398000.125
7FayNorth554000.625
8GusEast485000.25
=PERCENTRANK.EXC($C$2:$C$8,C2) Explain

Common mistakes

  • #N/A x is outside the range of the data.
  • #NUM! significance is less than 1.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.