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

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

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.

** Step-01**:

Before adding the serial numbers of these marks we have to sort the marks in

**(from smallest to highest value).**

*Ascending order*➤ 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**.

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

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

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

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

__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 1

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

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.

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.

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

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.

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

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

**, and**

*Chemistry***by using the**

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

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