< Go Back

Excel PERCENTRANK Function

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.

Formula

=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

  1. 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

Notes

  • 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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar