Based on the dataset various times we need to **rank or sort** them from least to greatest. In Excel, we can use it using the **PERCENTILE** function. But for better **ranking Excel has PERCENTILE.INC and PERCENTILE.EXC functions**. In this article, I’m going to explain the **Percentile** rank **INC** vs **EXC** in Excel.

Here, I’ve taken a dataset of some **Students** along with their **Marks** to **Rank** them based on their **Marks** while I’ll use both functions.

In this article, I will show you the various differences between Excel **PERCENTILE.INC** and **PERCENTILE.EXC** Functions.

## Download to Practice

## Basics of PERCENTILE.INC and PERCENTILE.EXC Functions in Excel

### PERCENTILE.INC Function

__Syntax__

**PERCENTILE.INC (array, k)**

__Summary__

The Excel **PERCENTILE.INC** function includes first and last values from the array. By including the values **0 **& **1** it returns the **K**^{th}**PERCENTILE** of the used dataset. Here, the value of **k **varies from **0** to **1**.

__Return Value__

The **PERCENTILE.INC **function returns a result which is calculated **Percentile **of **K**.

__Arguments__

Arguments |
Required/Optional |
Explanation |
---|---|---|

array |
Required | It is a range of data or array from a dataset of numeric values which determine the corresponding standing |

k |
Required | It is the value between 0 to 1. Here it includes 0 & 1. |

### PERCENTILE.EXC Function

__Syntax__

**PERCENTILE.EXC (array, k)**

__Summary__

The Excel **PERCENTILE.EXC** function does not include first and last values. This function excludes **first** & **last** values from the array. By excluding the values **0 **& **1** it returns the **K**^{th}**PERCENTILE** of the used dataset. Here, the value of **k **varies from **0** to **1**.

__Return Value__

The **PERCENTILE.EXC **function returns a result which is calculated **Percentile **of **K**.

__Arguments__

Arguments |
Required/Optional |
Explanation |
---|---|---|

array |
Required | It is a range of data or array from a dataset of numeric values which determine the corresponding standing |

k |
Required | It is the value between 0 to 1. Here it excludes 0 & 1. |

__Version__

*The PERCENTILE.INC & PERCENTILE.EXC function is available for Excel 2010 and later.*

*I’m using Excel Microsoft 365 to implement these examples.*

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

**Similar Readings**

**How to Rank Within Group in Excel (3 Methods)****Rank with Ties in Excel (5 Simple Ways)****Rank IF Formula in Excel (5 Examples)**

## Examples with Excel Percentile Rank INC vs EXC

### 1. Using Excel PERCENTILE.INC Function

Here, I’m going to use the **PERCENTILE.INC** function to calculate the **Rank** of **Students** based on **Marks**. As I’m using the **PERCENTILE.INC** function so it will include the values **0 **and **1**.

Let me explain the procedure to you,

- To begin with, select the cell of your choice to place the
**resultant**value. - Here, I selected cell
**F5**. - Now, type the following formula in the
**F5**cell or into the**Formula Bar**.

`=PERCENTILE.INC($C$5:$C$13,D5)`

Here, in the **PERCENTILE.INC** function, I selected the range **$C$5:$C$13** as an **array** and selected cell **D5** as **k **which is **0.9**. In the **array**, I used an **absolute reference **as I’m going to use this range for further values of **k**.

- Now, press
**ENTER**to get the**Rank**of the**Students**based on their**Marks**.

- Here, you can use the
**Fill Handle**to**AutoFill**formula for the rest of the cells.

### 2. Using Excel PERCENTILE.EXC Function

Now, I’m going to use the **PERCENTILE.EXC** function to calculate the **Rank** of **Students** based on **Marks**. Here, it will exclude the values **0 **and **1**.

Let me explain the procedure to you,

- To start with, select the cell of your choice to place the
**resultant**value. - Here, I selected cell
**F5**. - Now, type the following formula in the
**F5**cell or into the**Formula Bar**.

`=PERCENTILE.EXC($C$5:$C$13,D5)`

Here, in the **PERCENTILE.EXC** function, I selected the range **$C$5:$C$13** as an **array** and selected cell **D5** as **k **which is **0.9**. In the **array**, I used an **absolute reference **as I’m going to use this range for further values of **k**.

- Now, press
**ENTER**to get the**Rank**of the**Students**based on their**Marks**.

Here, you can see the **Rank **is **95** for **PERCENTILE.EXC **whereas the **Rank **is **91** for **PERCENTILE.INC**.

- Here, you can use the
**Fill Handle**to**AutoFill**formula for the rest of the cells.

**Similar Readings**

**How to Stack Rank Employees in Excel (3 Methods)****Excel Formula to Rank with Duplicates (3 Examples)****How to Rank Average in Excel (4 Common Scenarios)**

## Difference Between Excel PERCENTILE.INC & PERCENTILE.EXC Function

Here, you can see the difference between **Percentile Rank INC** vs **EXC**. For the same **marks**,** Students** are getting different **Ranks**.

While using the **PERCENTILE.INC** function, **Adam Smith** ranked **91** whereas by using the **PERCENTILE.EXC** function he ranked **95**. The **rank **is higher for the **PERCENTILE.EXC** function as it excludes the values that fall within **0 **and **1**.

The main reason behind it is the value of **K**.

Here, the **PERCENTILE.INC** function is a **partially less precise** **algorithm **because this function **includes** the **values 0 and 1** when it calculates the **percentiles**. This function is the same as the **PERCENTILE** function.

In case you have a large set of datasets you can use the **PERCENTILE.EXC** function. This function will only work if **K** is between **1/N** and **1-1/N**, where **N **denotes the **element **number in an array otherwise it will cause errors.

Whenever you calculate the feasible **non-integer rank** then it differs from the **PERCENTILE.EXC** and **PERCENTILE.INC** function.

PERCENTILE.INC |
PERCENTILE.EXC |
---|---|

The equation PERCENTILE.INC function follows is K*(N-1)+1 |
The equation PERCENTILE.EXC function follows is K*(N+1) |

If you have a small set of datasets, you can use the PERCENTILE.INC function. |
If you have a large set of datasets you then use the PERCENTILE.EXC function will be more accurate. |

Won’t show an error while exceeding the data limit in interpolation. |
Exceeds of data limit while interpolation will show #NUM error. |

## Things to Remember

- The
**PERCENTILE.EXC**function causes**#NUM!**Error if the value of**K**is**less than equal (<=) 1/(N+1)**and also if the values of**K**are**greater than equal (>=) N/(N+1)**.

## Practice Section

Here I have provided a practice sheet for you to practice the difference between the functions.

## Conclusion

I tried to explain Excel **Percentile** rank** INC vs EXC**. This explanation will help you to understand the difference between these two functions. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.