PERCENTRANK function
PERCENTRANK shows how far up a data set a value sits, as a fraction from 0 to 1. It is a compatibility function; PERCENTRANK.INC and PERCENTRANK.EXC replace it in new workbooks.
Example
Read it as a sentence: find the percentile rank of 4 among the values in A1:A8, which gives 0.142.
Excel shows 0.142
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
PERCENTRANK(array, x, [significance])
Arguments in [square brackets] are optional.
-
array(required) - The data.
-
x(required) - Value whose rank you want.
-
significance(optional) - Digits in the result; default 3.
Returns: Relative standing of x as a fraction from 0 to 1.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=PERCENTRANK({10,20,30,40,50},25) | 0.375 | Array constant |
=PERCENTRANK(Data!A1:A8,4) | 0.142 | X in data |
=PERCENTRANK(Data!A1:A8,6) | 0.785 | X between values |
=PERCENTRANK(Data!A1:A8,2) | 0 | X = min |
=PERCENTRANK(Data!A1:A8,9) | 1 | X = max |
=PERCENTRANK(Data!A1:A8,6,1) | 0.7 | Significance 1 |
=PERCENTRANK(Data!A1:A8,1) | #N/A | X below min |
=PERCENTRANK(Data!A1:A8,10) | #N/A | X above max |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Watch out for
- Superseded by PERCENTRANK.INC / PERCENTRANK.EXC; use that in new workbooks.
- Result is truncated, not rounded, to 3 digits unless you set significance.
- x outside the data range returns #N/A.
Where it works
- Excel
- PERCENTRANK is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .