PERCENTRANK.INC function
Quick answer
PERCENTRANK.INC tells you where a value stands in a list, from 0 for the lowest to 1 for the highest: 0.75 means it is above 75% of the other values.
- Syntax
=PERCENTRANK.INC(array, x, [significance])- Most common use
=PERCENTRANK.INC(A2:A11,8)returns 0.666 (6 of the other 9 values are lower: 0.666 (truncated))
Values between list entries are interpolated, and the result is cut to three decimals unless you ask for more. Use it to turn scores or sales into percentile standings. PERCENTRANK is the older name.
- Available in
- Excel 2010 and later
- Category
- Statistical functions
- Returns
- A number from 0 to 1; #N/A if x is below the smallest or above the largest value.
- Google Sheets
- Google Sheets has PERCENTRANK.INC and PERCENTRANK.
Syntax
=PERCENTRANK.INC(array, x, [significance])
| Argument | Required? | What it is |
|---|---|---|
array | Required | The list of numbers to rank against. |
x | Required | The value to rank. |
significance | Optional | Optional: decimal places to keep; 3 if omitted. The result is truncated, not rounded. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =PERCENTRANK.INC(A2:A11,8) | 0.666 | 6 of the other 9 values are lower: 0.666 (truncated) |
| C3 | =PERCENTRANK.INC(A2:A11,5) | 0.583 | 5 isn't in the list, so it's interpolated between 4 and 8 |
| C4 | =PERCENTRANK.INC(A2:A11,8,1) | 0.6 | One decimal place: 0.6 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Value | Result | |
| 13 | 0.666 | |
| 12 | 0.583 | |
| 11 | 0.6 | |
| 8 | ||
| 4 | ||
| 3 | ||
| 2 | ||
| 1 | ||
| 1 | ||
| 1 |
Practice PERCENTRANK.INC
Graded instantly, then re-checked on changed data.
Sales standing
C2, calculate each rep's percentile rank among all the sales in B2:B9. Fill down to C9.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A | x is lower than the smallest or higher than the largest value in the list. |
| 0.666 instead of 0.667 | PERCENTRANK.INC truncates to the decimals in significance instead of rounding. |
| Text or blanks in the list | Only numbers count. Text and blank cells are skipped. |
Tips
- Format the result as a percentage to read 0.666 as 66.6%.
Questions
How do I calculate percentile rank in Excel?
=PERCENTRANK.INC($B$2:$B$100,B2) gives B2's standing in the list from 0 to 1. Fill it down for every row.
How is PERCENTRANK different from RANK?
RANK gives a position: 1st, 2nd and so on. PERCENTRANK gives a share from 0 to 1, so it compares across lists of different sizes.
Updated Checked by running: every formula on this page was run by our formula engine on .