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

**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

#### Example #1

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

Copy and paste the above data to a worksheet.

#### Step 1: Open PERCENTRANK function

Open excel PERCENTRANK function in the B2 cell.

#### Step 2: Select Number of Range as Absolute Reference

The first argument of 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.

#### Step 3: Now select x reference value.

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

#### Step 4: Hit enter key to get the result.

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

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

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

Go to the next value i.e. 4 this number is ranked as 33.333% because number 4 is the 4^{th} 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.

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

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.

#### Step 2: Select Number of Range as Absolute Reference

The first argument of 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.

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

#### Step 4: Hit enter key to get the result.

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

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

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

As you can see above the decimal places are rounded to the below number. For example in cell C2, without using 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 PERCENTRANK function along with examples and downloadable excel templates. You can learn more about excel functions from the following articles –

- Percentage Change Formula
- How to Calculate Percentage Increase in Excel?
- Percentile Rank Formula
- Calculate the Percent Change in Excel
- Heat Map in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion