Excel PERCENTRANK Function

PERCENTRANK Function in Excel

PERCENTRANK function gives the rank to each number against overall numbers in percentage numbers. Not many of us use this Excel PERCENTRANK function because of the lack of knowledge or maybe the situation never demanded this at all. But this will be a wonderful function to have because showing the rank in percentage values within the set of data will be a new trend.

Syntax

Below is the syntax of the PERCENTRANK function.

PERCENTRANK Function in Excel Formula
  • Array: This is the range of value against, we are giving rank.
  • x: This is nothing but the individual value from the lot.
  • [Significance]: It is an optional parameter. If you want to see decimal places for the percentage rank values, then we can make use of this argument. For example, if the rank percentage is 58.56% and if you want to see two-digit decimal values, then we can enter the significance number as 2, then it will show the percentage as 58.00%, if you ignore then it will take three digits significance by default.

Examples

You can download this PERCENTRANK Function Excel Template here – PERCENTRANK Function Excel Template

Example #1

Let me start the example with a simple series of numbers; consider the below series of numbers for this example.

PERCENTRANK Function in Excel Example 1

Copy and paste the above data to a worksheet.

  1. Open PERCENTRANK function

    Open excel PERCENTRANK function in the B2 cell.


    PERCENTRANK Function in Excel Example 1.1

  2. Select Number of Range as Absolute Reference

    The first argument of the PERCENTRANK function is “Array,” i.e., our entire numbers range from A2 to A11, so select this range as the reference and make it an absolute reference.

    PERCENTRANK Function in Excel Example 1.2

  3. Now select x reference value.

    Next, select the cell A2 as the “x” reference. As of now, ignore [significance] argument, close the bracket.


    PERCENTRANK Function in Excel Example 1.3

  4. Hit the Enter key to get the result.

    Example 1.4

Also, drag the formula to the below cells as well.

Percentrank Excel gif

Now closely look at the numbers lets go from the bottom.

We have number 3 for the first three values, so the first lowest number in the array is always ranked as 0.000%.

PERCENTRANK Function in Excel Example 1.5

Go to the next value, i.e., 4; this number is ranked as 33.333% because number 4 is the 4th positioned number in the range of values below this number 4, there are 3 values, and above this number, there are 6 values, so the percentage is arrived by using below equation.

PERCENTRANK Function in Excel Example 1.6
Percentage Rank = Number of Values below / (Number of Values below + Number of Values Above)

i.e. 3 / (3 + 6)

= 33.333%

Similarly, the top value in the range is 15, and it is ranked as 100%, which is equivalent to rank 1.

Example #2

Now, look at the below scores of students in an examination.

Example 2

Using this data, we will find the percentage rank. Below is the formula applied for these numbers.

Step 1: Open PERCENTRANK function

Open PERCENTRANK function in the C2 cell.

Example 2.1

Step 2: Select Number of Range as Absolute Reference

The first argument of the PERCENTRANK function is “Array,” i.e., our entire numbers range from B2 to B11, so select this range as the reference and make it an absolute reference.

Example 2.2

Step 3: Now select x reference value.

Next, select the cell B2 as the “x” reference. As of now, ignore [significance] argument, close the bracket.

PERCENTRANK Function in Excel Example 2.3

Step 4: Hit the Enter key to get the result.

Example 2.4

Also, drag the formula to the below cells as well.

Percentrank Excel 1 gif

The lowest value in the range is 326 (B8 cell), and it has given a rank of 0.00%, and the highest value in the range is 589 (B4 cell), and it has given a rank of 100%.

PERCENTRANK Function in Excel Example 2.6

Now I will give the significance parameter as 2 and see what happens.

Example 2.5

As you can see above, the decimal places are rounded to the below number. For example, in cell C2, without using a parameter, we have got 88.80%, but this time we have got only 88.00% because of the significant value of 2.

Things to Remember Here

  • Only numerical values work for this function.
  • If the significance value is <=0, then we will get #NUM! Error.
  • Array and “x” value should not be empty.

Recommended Articles

This has been a guide to Excel PERCENTRANK. Here we discuss how to calculate the percent rank for numbers or scores of students using the PERCENTRANK function along with examples and downloadable excel templates. You can learn more about excel functions from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>