The PERCENTRANK function returns the rank of a value in a dataset as a percentage of the dataset -- essentially, the relative standing of a value within the whole dataset. For example, you could use PERCENTRANK to determine the standing of an individual's test score among the field of all scores for the same test.
Important: This function has been replaced along with other functions that may provide improved accuracy and whose names better reflect their purpose. This function remains available for backward compatibility but may not be available in future versions of Excel. Consider now using the newer functions.
To learn more about the new functions, see PERCENTRANK.EXC function and PERCENTRANK.INC function.
Syntax
PERCENTRANK(array,x,[significance])
The PERCENTRANK function syntax has the following arguments:
-
Array Required. The range of data (or pre-defined array) of numeric values within which percent rank is determined.
-
X Required. The value for which you want to know the rank within the array.
-
Significance Optional. A value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK uses three digits (0.xxx).
Remarks
-
If the array is empty, PERCENTRANK returns the #NUM! error value.
-
If significance < 1, PERCENTRANK returns the #NUM! error value.
-
If X does not specifically match one of the values in the array (e.g., a list of specific student test scores) PERCENTRANK interpolates to return a percentage rank for X.
Example
From the following table, copy the example data (from the word Data on through all of the formulas, etc.). Then, in a new Excel worksheet, paste all of it into cell A1 and below. To show a formula's results select it, press F2, and press Enter. Adjust column widths if needed for visibility.
|
Data |
||
|---|---|---|
|
13 |
||
|
12 |
||
|
11 |
||
|
8 |
||
|
4 |
||
|
3 |
||
|
2 |
||
|
1 |
||
|
1 |
||
|
1 |
||
|
Formula |
Description (Result) |
Result |
|
=PERCENTRANK(A2:A11,2) |
Percent rank of 2 in the range A2:A11. Result is 0.333 because three values in the set are less than 2, and six are greater than 2. Because 2 is found in the range (cell A8), the number of values less than 2 is divided by the sum of the number of values less than 2 and the number of values greater than 2. That is, 3/(3+6)=0.333. |
0.333 |
|
=PERCENTRANK(A2:A11,4) |
Percent rank of 4 in the range A2:A11. Five values are less than 4, and four are greater. Following the example above, 5/(4+5)=0.555. |
0.555 |
|
=PERCENTRANK(A2:A11,8) |
Percent rank of 8 in the range A2:A11. Six values are less than 8, and 3 are greater. Following the example above, 6/(6+3) =0.666. |
0.666 |
|
=PERCENTRANK(A2:A11,5) |
Percent rank of 5 in the range A2:A11. Unlike the examples above, 5 is not found in the range. The PERCENTRANK of 5 is calculated by finding the one-quarter mark between the PERCENTRANK of 4 and the PERCENTRANK of 8. This is (0.555)+(0.25*(0.666-0.555)), or 0.583. |
0.583 |
