PERCENTILE.INC Function in Excel – Inclusive Function

In the previous blog, we learned the basics of PERCENTILE.EXC Function. In this blog, we are going to implement an essential Statistical Function, which is PERCENTILE.INC Function in Excel. Both the PERCENTILE.EXC and PERCENTILE.INC Functions were introduced in Excel 2010.

So let’s start learning the formula.

When to Use PERCENTILE.INC Function in Excel?

The PERCENTILE.INC word stands for Percentile Inclusive. The PERCENTILE.INC Formula calculates the percentile value for a given set of numerical values. We can get the value of the k^th percentile with this formula.

We call it PERCENTILE.INC Function because it includes all the values of k in the range from 0 to 1.

What are Percentiles?

The percentile value is the value under which the given percentage of data falls. For example, let us suppose that for any data set, the value of the 90^th percentile is 70.

This would imply that 90% of the values in the data set are either less than or equal to 70.

The Percentiles can be used in situations like this:-

• What Marks a does competitor need to be in the top 5% of the competitors? This can be concluded by calculating the 95th percentile. The 95th percentile value separates the bottom 5% of competitors from the top 10% of competitors.

Syntax and Arguments

=PERCENTILE.INC(array,k)

The following inputs are required by the PERCENTILE.INC Formula.

• array – This is the range of cells containing numerical values. We can also pass the data set in the form of direct values in brackets separated by a comma.
• k – This is the percentile that we want to calculate, Its value lies between 0 and 1 inclusive. We can also pass the direct percentage like 90%. It would be the same as 0.9

Examples to Implement PERCENTILE.INC Function in Excel

In this part of the blog, we will look at some examples to learn the PERCENTILE.INC Function.

Example 1 – Simplest Example to Implement PERCENTILE.INC Formula

Let us suppose we have the following set of numerical values in our data set.

We want to calculate the value of the 90th percentile. Use the following PERCENTILE.INC formula.

=PERCENTILE.INC(A2:A11,90%)

As a result, the formula returns the value of the 90th percentile as 25.

Explanation –We have passed the numerical array A2:A11 as the array input of PERCENTILE.INC Function. This range contains our data set for which we want to get the percentile. k argument is supplied as 90% which tells the function to calculate the 90th percentile. We can also write 0.90 as k argument.

The 90^th percentile is 25 which implies that 90% of the values in the data set are either less than or equal to 25.

Example 2 – Getting Percentile for Competition Results

Here we are taking the resultant marks of competition as follows:-

We want to calculate the marks that a competitor needs to score to be in the list of top 5% of competitors. Use this PERCENTILE.INC Function Formula.

=PERCENTILE.INC(A2:A17,0.95)

As a result, the formula returns 91.25 marks as the 95th percentile value.

Important Points about PERCENTILE.INC Function

We should always remember the following points about PERCENTILE.INC Function.

• The function was introduced in Excel 2010. For earlier versions, use the PERCENTILE Function.
• The Formula returns a #NUM! error in any of two situations-
  • If the array supplied is the empty array
  • value of k is either less than 0 or greater than 1.
• If the supplied value of k is not the multiple of 1/(n-1) then the values are interpolated to determine the k^th percentile.

Explanation – The 95th percentile value separates the bottom 95% of the competitors from the top 5% of the competitors. We have passed the marks range A2:A17 as the array argument. The k is 0.95 for getting the 95th percentile.

So a competitor needs to score more than 91.25 marks in order to be on the list of top 5% competitors.

With this, we have come to an end.

Thank you for reading.