__Method 1 – __Using a Formula to Calculate Percentile Rank in Excel

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

The **Sort **dialog box will appear.

➤ Check the **My data has headers **option and select the following

**Sort by → Marks **(the column name on the basis of which we are sorting)

**Sort On → Cell Values
**

**Order → Smallest to Largest**

➤ Press **OK**.

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

We will get the rank of the 65th percentile mark.

➤ Use the following formula in the cell

**E13**

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

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

We are getting 6.5 as the Rank.

We will determine the corresponding marks at the 65th percentile by using the following formula

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

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

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

** Steps**:

➤ Type the following formula in the cell

**E4**

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

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

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, 100% means 100% marks are below this mark and (100-100)% or 0% marks are above this mark.

__Method 3 – __Using PERCENTRANK.INC Function to Calculate Percentile Rank in Excel

** Steps**:

➤ Type the following formula in the cell

**E4**:

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

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

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

** Steps**:

➤ Type the following formula in the cell

**E4**

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

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

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

** Steps**:

➤ Type the following formula in the cell

**D13**

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

**$D$4:$D$12 **is the range of marks, and 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)`

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

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)`

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

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

** Steps**:

➤ Type the following formula in the cell

**D13**:

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

**$D$4:$D$12 **is the range of marks, and 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, and 0 is for the 0th percentile.

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)`

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

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 that you can’t use 0 and 1 for determining the lowest and highest marks; you can use 0.1 instead of 0 and 0.9 instead of 1.

__Method 7 – __Using SUMPRODUCT and COUNTIF Functions for Conditional Ranking

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

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

We have different percentile rankings for the three subjects for different students: the Red indicating box is for Michael, the Blue indicating box is for Howard, and the Green indicating box is for Lara.

**Download Workbook**

**Related Articles**

- How to Rank Average in Excel
- How to Rank in Excel Highest to Lowest
- How to Stack Rank Employees in Excel
- How to Create a Ranking Graph in Excel
- How to Create an Auto Ranking Table in Excel

**<< Go Back to Excel RANK Function | Excel Functions | Learn Excel**