Excel PERCENTRANK function
The PERCENTRANK function is an Excel function grouped under the Statistical functions. It is also a worksheet function (WS) which means it can be inserted as part of a formula into a cell in the worksheet.
What is the PERCENTRANK function in Excel?
The PERCENTRANK function in Excel returns the relative position of a value within a set of values as a percentage of the set of values.
You can use the PERCENTRANK to find the standing of a specific score among all scores for a standard test.
=PERCENTRANK (array, x, [significance])
Parameters or arguments of PERCENTRANK function
Excel PERCENTRANK function uses the following arguments:
Array (required) – array or range of data values
X (required) – Value to rank
Significance (optional) – Number of the significant digit in the returned result (When omitted, returns 3 significant digits).
How to use the PERCENTRANK function in Excel?
Consider the example for detailed illustration on how to use the PERCENTRANK function in Excel.
Suppose we have the scores of 8 students who sat for an aptitude test. The PERCENTRANK function shows the performance of a student compared to all other students
- Insert the data for each student in the cells of column A and B of the worksheet.
Figure 1. Data table to show how to use the PERCENTRANK function
2. Apply the PERCENTRANK function formula to calculate the percent rank. The array in the formula is from the cell B2 to B9.
Figure 2. PERCENTRANK function showing the ‘array’ parameter
3. The ‘X’ parameter is after the array, with a comma separating them. This is the value of the score to be ranked.
Figure 3. PERCENTRANK function showing the ‘X’ parameter
4. The next parameter is the ‘significance’ which is optional as stated before. In this example, 2 is the significant digit.
Figure 4. PERCENTRANK function showing the ‘significance’ parameter
5. The PERCENTRANK calculates the percent rank of score ‘30’ which belongs to Student 3 as shown below.
Figure 5. PERCENTRANK function calculates the percent rank of Student 3
- If the significance < 1, Excel shows the #NUM! Error.
- If the x value does not exist in the array or is omitted, the #N/A! Error occurs.