Excel Percentile Rank Inc vs Exc

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.

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.


Download to Practice


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

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

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


Similar Readings


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)

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.


Similar Readings


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.

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


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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

ExcelDemy
Logo