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.

**Table of Contents**hide

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

6.5-6`(E13-B9)`

â†’**Output**â†’ 0.5

80-71`(E10-E9)`

â†’**Output**â†’ 9

becomes`E9+(E13-B9)*(E10-E9)`

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

determines the rank of the mark in cell`EQ(D4,$D$4:$D$12,1)`

â†’**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)

counts the number of non-blank cells in this range`COUNT($D$4:$D$12)`

â†’**Output â†’**9

becomes`EQ(D4,$D$4:$D$12,1)/COUNT($D$4:$D$12)`

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

becomes`SUMPRODUCT(($B$4:$B$12=B4)*(D4>$D$4:$D$12))`

`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

counts the number of presence of the student`COUNTIF($B$4:$B$12, B4)`

â†’*Michael*in the**Name**column**Output â†’**3

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

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