How to Calculate Rank Percentile in Excel (7 Suitable Examples)

If you are trying to determine the rank position of your score or salary etc with others in percentage form, then Excel Percentile Rank is very useful in this term. So, let’s start the article with more about the ways of using Percentile Rank in Excel.

Download Workbook


7 Ways to Calculate & Use Percentile Rank in Excel

We will use the following dataset containing marks of different students of a college to demonstrate the examples of Excel Percentile Rank.

Excel percentile rank

We have used Microsoft Excel 365 version here; you can use any other version according to your convenience.


Method-1: Using a Formula to Calculate Percentile Rank in Excel

Here, we will determine the 65th percentile rank of the marks of the students by using a formula and for this purpose, we have added the column Serial No. here.

Excel percentile rank

Step-01:
Before adding the serial numbers of these marks we have to sort the marks in Ascending order (from smallest to highest value).
➤ After selecting the range, go to Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.

using a formula

Then, the Sort dialog box will appear.
➤ Check the My data has headers option and select the followings

Sort by → Marks (the column name on the basis of which we are sorting)
Sort On → Cell Values
Order → Smallest to Largest

➤ Press OK.

using a formula

Afterward, you will get the marks from the lowest value to the highest value.

using a formula

➤ Enter the serial numbers of the marks in the Serial No. column.

Excel percentile rank

Step-02:
Now, we will get the rank of the 65th percentile mark.
➤ Use the following formula in the cell E13

=(65/100)*(B12+1)

Here, B12 is the total number of marks and after being added with 1, it will be 10 and finally, we will multiply it with 0.65 (percentile rank).

using a formula

As a result, we are getting 6.5 as the Rank.

using a formula

Now, we will determine the corresponding marks at the 65th percentile by using the following formula

=E9+(E13-B9)*(E10-E9)

Here, E9 is the marks at serial number 6, E10 is the marks at serial number 7, E13 is the Rank and B9 is the serial number 6.

  • (E13-B9)5-6
    Output → 0.5
  • (E10-E9)80-71
    Output → 9
  • E9+(E13-B9)*(E10-E9) becomes
    71+0.5*9
    Output → 75.5

Excel percentile rank

So, we are getting the marks 75.5 as 65th percentile mark which is in between the marks of the serial numbers 6 and 7.

Read More: How to Calculate the Top 10 Percent in Excel (4 Ways)


Method-2: Combining RANK.EQ and COUNT Function to Calculate Percentile Rank

Here, we will determine the percentile ranks of the marks of the students by using the RANK.EQ function and the COUNT function.

Excel percentile rank

Steps:
➤ Type the following formula in the cell E4

=RANK.EQ(D4,$D$4:$D$12,1)/COUNT($D$4:$D$12)

Here, D4 is the marks for the student Michael, $D$4:$D$12 is the range of marks and 1 is the Ascending Order (it will return 1 for the lowest mark and the highest rank for the highest number).

  • EQ(D4,$D$4:$D$12,1)determines the rank of the mark in cell D4 among the range of the marks $D$4:$D$12.
    Output → 1 (as the number in the cell D4 is the lowest number in the range)
  • COUNT($D$4:$D$12)counts the number of non-blank cells in this range
    Output → 9
  • EQ(D4,$D$4:$D$12,1)/COUNT($D$4:$D$12) becomes
    1/9
    Output → 0.11 or 11%

RANK.EQ and COUNT function

➤ Press ENTER and drag down the Fill Handle tool.

RANK.EQ and COUNT function

Result:
Then, we will get the percentile ranks of the marks, for example, the lowest rank 11% means there are only 11% marks below this mark and (100-11)% or 89% marks are above this mark, whereas 100% means 100% marks are below this mark and (100-100)% or 0% marks are above this mark.

Excel percentile rank

Read More: Rank IF Formula in Excel (5 Examples)


Method-3: Using PERCENTRANK.INC Function to Calculate Percentile Rank in Excel

In this section, we will use the PERCENTRANK.INC function for calculating the percentile ranks of the marks where this function will include the bottom rank (0%) and the top rank (100%).

Excel percentile rank

Steps:
➤ Type the following formula in the cell E4

=PERCENTRANK.INC($D$4:$D$12,D4)

Here, D4 is the marks for the student Michael, $D$4:$D$12 is the range of marks.

PERCENTRANK.INC function

➤ Press ENTER and drag down the Fill Handle tool.

PERCENTRANK.INC function

Result:
Here, we are getting 0% for the lowest mark which means there are no marks below this mark, and 100% for the highest mark which means all of the marks are below this mark.

Excel percentile rank


Method-4: Using Excel PERCENTRANK.EXC Function to Calculate Percentile Rank

For calculating the percentile ranks of the marks you can use the PERCENTRANK.EXC function which will exclude the bottom rank (0%) and the top rank (100%).

Excel percentile rank

Steps:
➤ Type the following formula in the cell E4

=PERCENTRANK.EXC($D$4:$D$12,D4)

Here, D4 is the marks for the student Michael, $D$4:$D$12 is the range of marks.

PERCENTRANK.EXC function

➤ Press ENTER and drag down the Fill Handle tool.

PERCENTRANK.EXC function

Result:
After that, we are getting 10% for the lowest mark instead of 0% and 90% for the highest mark in lieu of 100%.

Excel percentile rank


Method-5: Using PERCENTILE.INC Function

For determining the marks of the range at different percentile ranks such as 65th, 0th, and 100th, you can use the PERCENTILE.INC function.

Excel percentile rank

Steps:
➤ Type the following formula in the cell D13

=PERCENTILE.INC($D$4:$D$12,0.65)

Here, $D$4:$D$12 is the range of marks, 0.65 is for the 65th percentile.

PERCENTILE.INC function

For getting the mark at the 0th percentile, enter the following formula in the cell D14

=PERCENTILE.INC($D$4:$D$12,0)

Here, $D$4:$D$12 is the range of marks, 0 is for the 0th percentile.

As a result, it is returning the lowest mark of the range for the 0th percentile.

PERCENTILE.INC function

Use the following formula in the cell D15 for having the mark at the 100th percentile rank

=PERCENTILE.INC($D$4:$D$12,1)

Here, $D$4:$D$12 is the range of marks, 1 is for the 100th percentile.

 

As a result, it is returning the highest mark of the mange for the 100th percentile.

Excel percentile rank


Method-6: Using PERCENTILE.EXC Function to Calculate Percentile Rank in Excel

To determine the marks of the range at different percentile ranks such as 65th, 0th, and 100th, you can also use the PERCENTILE.EXC function.

Excel percentile rank

Steps:
➤ Type the following formula in the cell D13

=PERCENTILE.EXC($D$4:$D$12,0.65)

Here, $D$4:$D$12 is the range of marks, 0.65 is for the 65th percentile.

PERCENTILE.EXC function

For getting the mark at the 0th percentile, enter the following formula in the cell D14

=PERCENTILE.EXC($D$4:$D$12,0)

Here, $D$4:$D$12 is the range of marks, 0 is for the 0th percentile.

 

As a result, it is returning the #NUM! error because of the PERCENTILE.EXC function will work with the values excluding the bottom value of the range.

PERCENTILE.EXC function

To have the mark at the 100th percentile, enter the following formula in the cell D15

=PERCENTILE.EXC($D$4:$D$12,1)

Here, $D$4:$D$12 is the range of marks, 1 is for the 100th percentile.

As a result, it is returning the #NUM! error because of the PERCENTILE.EXC function will work with the values excluding the top value of the range.

Excel percentile rank

To avoid the #NUM! error, you have to be careful about the fact that you can’t use 0 and 1 for determining the lowest and highest marks, rather you can use 0.1 instead of 0 and 0.9 instead of 1.


Method-7: Using SUMPRODUCT and COUNTIF Functions for Conditional Ranking

Here, we will get the percentile ranking for the same student for different three subjects like Physics, Chemistry, and Biology by using the SUMPRODUCT function and the COUNTIF function.

Excel percentile rank

Steps:
➤ Type the following formula in the cell E4

=SUMPRODUCT(($B$4:$B$12=B4)*(D4>$D$4:$D$12))/COUNTIF($B$4:$B$12,B4)

Here, D4 is the marks for the student Michael, $D$4:$D$12 is the range of marks, B4 is the name of the student, and $B$4:$B$12 is the range of names.

  • SUMPRODUCT(($B$4:$B$12=B4)*(D4>$D$4:$D$12)) becomes
    SUMPRODUCT(({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})*({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))SUMPRODUCT({0;0;0;0;0;0;0;0;0})
    Output → 0
  • COUNTIF($B$4:$B$12, B4)counts the number of presence of the student Michael in the Name column
    Output → 3
  • SUMPRODUCT(($B$4:$B$12=B4)*(D4>$D$4:$D$12))/COUNTIF($B$4:$B$12,B4) becomes
    0/3
    Output → 0%

SUMPRODUCT and COUNT function

➤ Press ENTER and drag down the Fill Handle tool.

SUMPRODUCT and COUNT function

Result:
So, we are having different percentile rankings for the three subjects for different students, here, the Red indicating box is for Michael, the Blue indicating box is for Howard, Green indicating box is for Lara.

Excel percentile rank


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Conclusion

In this article, we tried to cover the example of Excel Percentile rank. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo