Excel Percentile Rank Inc vs Exc

Get FREE Advanced Excel Exercises with Solutions!

We often need to rank or sort the data in ascending or descending order based on various criteria. In Excel, we can do it using the PERCENTILE function. But for more customized 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.

Sample Dataset of Excel Percentile Rank Inc vs Exc

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


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

PERCENTILE.INC Function

Syntax

PERCENTILE.INC (array, k)

Basics of Excel PERCENTILE.INC Function

Summary

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

Return Value

The PERCENTILE.INC function returns the Kth Percentile from the given array.

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)

Basics of Excel PERCENTILE.EXC Function

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 Kth 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 Microsoft 365 to implement these examples.

Read More: How to Calculate Rank Percentile in Excel


Excel Percentile Rank INC vs EXC : 2 Suitable Examples

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)

Using Excel PERCENTILE.INC Function to Percentile Rank

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.

Using Excel PERCENTILE.INC Function to Percentile Rank


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.

Read More: How to Stack Rank Employees in Excel


Difference Between Excel PERCENTILE.INC & PERCENTILE.EXC Function

Here, you can see the difference between PERCENTILE.INC and PERCENTILE.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.

Difference Between PERCENTILE.INC & PERCENTILE.EXC Function

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.

Practice Section of Excel Percentile Rank Inc vs Exc


Download to Practice


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo