PERCENTRANK.INC function
PERCENTRANK.INC tells you what share of the data falls below a value, with the smallest value at 0 and the largest at 1. It interpolates when the value sits between data points.
Example
Read it as a sentence: find the percentile rank of 4 among the values in A1:A8 using the inclusive method, 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.INC(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 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.INC({10,20,30,40,50},25) | 0.375 | Array constant |
=PERCENTRANK.INC(Data!A1:A8,4) | 0.142 | X in data |
=PERCENTRANK.INC(Data!A1:A8,6) | 0.785 | X between values |
=PERCENTRANK.INC(Data!A1:A8,2) | 0 | X = min |
=PERCENTRANK.INC(Data!A1:A8,9) | 1 | X = max |
=PERCENTRANK.INC(Data!A1:A8,6,1) | 0.7 | Significance 1 |
=PERCENTRANK.INC(Data!A1:A8,1) | #N/A | X below min |
=PERCENTRANK.INC(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
- The result is truncated, not rounded, to the significance digits.
- #N/A when x is outside the min-to-max range.
Where it works
- Excel
- PERCENTRANK.INC arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, but behaves differently. Sheets has PERCENTRANK and PERCENTRANK.INC.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .