Shamima Sultana
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 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)
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)
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.
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)
- Rank IF Formula in Excel (5 Examples)
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 Calculate Rank Percentile in Excel (7 Suitable Examples)
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.
Related Articles
- How to Create an Auto Ranking Table in Excel (with Quick Steps)
- Ranking Data in Excel with Sorting (3 Quick Methods)
- How to Calculate Weighted Ranking in Excel (4 Ways)
- Create a Ranking Graph in Excel (5 Methods)
- Ranking Based on Multiple Criteria in Excel (4 Cases)
- How to Rank Within Group in Excel (3 Methods)
- Rank with Ties in Excel (5 Simple Ways)
