PERCENTILE Excel Function - Formula, Examples, How to Use?

What Is PERCENTILE Excel Function?

The PERCENTILE Excel function returns the k^th percentile of the given range of values based on its position in a dataset. For improved accuracy, PERCENTILE() in Excel 2010 was replaced with PERCENTILE.INC(). Therefore, the PERCENTILE() is now available in Excel as a Compatibility function for backward compatibility.

The PERCENTILE Excel function is an inbuilt function so that we can insert the formula from the “Function Library” or enter it directly in the worksheet. For example, the below image shows two tables. The first table contains a candidate list and their height details. And in the second table, we can calculate the various percentiles below which the specified percentages of candidates in table one fall, based on their heights.

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Download Excel Template

Frequently Asked Questions (FAQs)

1. Does Excel have a PERCENTILE function?

Excel has a PERCENTILE function in the Formulas tab. Click Formulas → More Functions → Compatibility → PERCENTILE to insert the function in the required target cell.

2. How do I calculate the 95% percentile in Excel?

We can calculate the 95% percentile in Excel using the PERCENTILE().

For example, the below table shows the data for units of a commodity delivered each month.

Select cell B16, enter the formula =PERCENTILE(B2:B13,95%), and press the “Enter” key.

The output is shown above.

[Alternatively, we can enter the formula =PERCENTILE(B2:B13,0.95) to get the same results.]

3. How to use the PERCENTILE Excel function with multiple conditions?

We can use the PERCENTILE Excel function with multiple conditions using the IF() function.

For example, the below image contains a list of students and their details. We must find the aggregate above which the top 10% of the students have their aggregates, who are on the prefectorial board and are also rank holders, i.e., the 90^th percentile.

Select cell G2, enter the formula =PERCENTILE(IF((B2:B11=”Yes”)*(C2:C11=”Yes”),D2:D11),0.9)} and press Ctrl + Shift + Enter keys to execute the function as an array formula.

The formula will now be {=PERCENTILE(IF((B2:B11=”Yes”)*(C2:C11=”Yes”),D2:D11),0.9)}

The output is shown above.

[Output Observation: First, the IF() checks the arrays B2:B11 and C2:C11 for Yes values to determine students on the prefectorial board and who are rank holders. And so, the IF condition array becomes {0;1;0;0;1;0;0;0;0;0}, indicating that two students, Muriel Duncan and Drew Fisher, satisfy both the conditions. Thus, the IF() returns the corresponding column D values, 92 and 95.

Finally, the PERCENTILE() accepts the array argument as {92,95} and determines the 90^th percentile to return the value 94.7. The result shows that the top 10% of the students satisfying the above conditions have aggregates above 94.7%.]

Download Template