The Excel PERCENTRANK.EXC function returns the relative standing of a value within a data set as a percentage that lies strictly between 0 and 1 — the exclusive counterpart to PERCENTRANK.INC.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array of numeric data. |
x | Required | The value whose percentile rank you want to find. |
significance | Optional | Number of significant digits in the result. Defaults to 3 if omitted. |
How to use it
PERCENTRANK.EXC reports where a value sits relative to the rest, using the exclusive method. The rank of the value at sorted position i is i/(n+1), so results never quite reach 0 or 1.
In a 5-point set the value 3 is at sorted position 3, so its exclusive rank is 3/6 = 0.5 — which happens to match the inclusive rank here, though for most values the two methods differ. By default the result is rounded to 3 significant digits.
INC vs EXC at a glance: exclusive ranks (divide by n+1) keep every observation strictly inside 0 and 1; inclusive ranks (divide by n-1) push the extremes all the way to 0 and 1. Pick the one your reporting standard expects.
Try it: interactive demo
Pick a PERCENTRANK.EXC example to see the formula and its result.
Practice workbook
Frequently asked questions
Why can't PERCENTRANK.EXC ever return exactly 0 or 1?
n+1, the smallest value lands at 1/(n+1) and the largest at n/(n+1) — both strictly inside the 0-to-1 range.How is PERCENTRANK.EXC different from PERCENTRANK.INC?
n+1 and excludes the endpoints; INC divides by n-1 and includes them, so the smallest value scores 0 and the largest scores 1 under INC.What does the significance argument control?
Does PERCENTRANK.EXC have a legacy version?
PERCENTRANK uses the inclusive method only.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class