Tanjima Hossain
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.
Calculate & Use Percentile Rank in Excel: 7 Ways
We will use the following dataset containing marks of different students of a college to demonstrate the examples of Excel Percentile Rank.
We have used the Microsoft 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.
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.
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.
Afterward, you will get the marks from the lowest value to the highest value.
➤ Enter the serial numbers of the marks in the Serial No. column.
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).
As a result, we are getting 6.5 as the Rank.
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)→ 6.5-6
Output → 0.5
(E10-E9)→ 80-71
Output → 9
E9+(E13-B9)*(E10-E9)becomes
71+0.5*9
Output → 75.5
So, we are getting the marks 75.5 as 65th percentile mark which is in between the marks of serial numbers 6 and 7.
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.
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%
➤ Press ENTER and drag down the Fill Handle tool.
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.
Similar Readings
- How to Rank Within Group in Excel
- How to Stack Rank Employees in Excel
- How to Create a Ranking Graph in Excel
- How to Create an Auto Ranking Table in Excel
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%).
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.
➤ Press ENTER and drag down the Fill Handle tool.
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.
Similar Readings
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%).
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.
➤ Press ENTER and drag down the Fill Handle tool.
Result:
After that, we are getting 10% for the lowest mark instead of 0% and 90% for the highest mark in lieu of 100%.
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.
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.
To get 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.
Use the following formula in the cell D15 to have 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.
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.
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.
To get 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.
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.
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.
Read More: Excel Percentile Rank Inc vs Exc
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.
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))becomesSUMPRODUCT(({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%
➤ Press ENTER and drag down the Fill Handle tool.
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.
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.
Download Workbook
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.
