PERCENTRANK.EXC function
PERCENTRANK.EXC shows where a value sits in a data set using the exclusive method, returning a fraction strictly between 0 and 1. A result of 0.5 means roughly the middle.
Example
Read it as a sentence: find the percentile rank of 4 among the values in A1:A8 using the exclusive method, which gives 0.222.
Excel shows 0.222
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
PERCENTRANK.EXC(array, x, [significance])
Arguments in [square brackets] are optional.
-
array(required) - The data.
-
x(required) - The value whose rank you want.
-
significance(optional) - Digits in the result; default 3.
Returns: The rank of x as a fraction strictly between 0 and 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.EXC({10,20,30,40,50},25) | 0.416 | Array constant |
=PERCENTRANK.EXC(Data!A1:A8,4) | 0.222 | X in data |
=PERCENTRANK.EXC(Data!A1:A8,6) | 0.722 | X between values |
=PERCENTRANK.EXC(Data!A1:A8,2) | 0.111 | X = min |
=PERCENTRANK.EXC(Data!A1:A8,9) | 0.888 | X = max |
=PERCENTRANK.EXC(Data!A1:A8,6,1) | 0.7 | Significance 1 |
=PERCENTRANK.EXC(Data!A1:A8,1) | #N/A | X below min |
=PERCENTRANK.EXC(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
- Returns #N/A if x is outside the data range.
- Default 3 digits truncates, not rounds.
Where it works
- Excel
- PERCENTRANK.EXC arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .