How to Use PERCENTILE with Multiple IF Condition in Excel (3 Examples)

In this article, we will learn to use the PERCENTILE function with multiple IF condition in Excel. The PERCENTILE function is used to calculate the k-th percentile of values in a range. And if we want to use multiple conditions, then, we need to use the IF function inside the PERCENTILE function. Today, we will demonstrate the method to use the PERCENTILE function with multiple IF condition in Excel using 3 easy examples. So, without further ado, let’s start the discussion.

Excel PERCENTILE Function Introduction

In Excel, the PERCENTILE function is used to compute the k-th percentile of values in a range or array. For example, you can easily compute students who score above the 80-th percentile. Excel has introduced alternatives to the PERCENTILE function in the newer versions for a more accurate result.

• Syntax

PERCENTILE(array,k)

• Arguments

array: This is the first required argument. It is the range from where the percentile needs to be calculated.

k: The second argument denotes the k-th percentile. If you want to calculate 90-th percentile, then you need to type 90 in place of k.

Excel IF Function Introduction

The IF function checks a criterion or condition. Then, returns a value if it is TRUE or returns another value if it is FALSE.

• Syntax

IF(logical_test,[value_if_true],[value_if_false])

• Arguments

logical_test: This is the first and compulsory argument. Here, you need to enter the condition that you want to check.

[value_if_true]: It is an optional argument. In this argument, you need to type the value that your formula should return if the condition is TRUE.

[value_if_false]: Here, you need to enter the value that the formula should return if the condition is FALSE.

How to Use PERCENTILE with Multiple IF Conditions in Excel: 3 Examples

To explain the examples, we will use a dataset that contains information about the Marks obtained by some students on a test. The students are from different regions and the test was held in different years. We will try to use multiple conditions to calculate the percentile in the following examples.

1. Multiply Multiple IF Conditions inside Excel PERCENTILE Function

In the first example, we will multiply the IF conditions inside the PERCENTILE function. Here, we will calculate the percentile based on two conditions. We will show the percentile of the East region in the 2020 year in Cell I5 and the West region in the 2020 year in Cell I6.

Let’s follow the steps below to know the full procedure.

STEPS:

• In the first place, select Cell I5 and type the formula:

• Secondly, hit Enter to see the result.

• After that, drag the Fill Handle down to see the result in Cell I6.

• Select Cell I5 and I6.
• Now, go to the Home tab and select Percentage from the Number field.

• In the following, you will see the percentiles in percentage.

• Again, if you want to add more conditions, you need to add the condition in the formula by multiplying. Suppose, we have added Gender as another condition in Column E.

• To add the condition, just multiply it like the formula below:

• Finally, press Enter to see the result.

🔎 How Does the Formula Work?

• ($C$5:$C$11=$H5)

This is the first condition and it denotes that the Region will have to be the East.

• ($D$5:$D$11=$I5)

It is the second condition and represents that the Year will have to be 2020.

• ($E$5:$E$11=$J5)

This is the third condition and denotes that the Gender will have to be Male.

• IF(($C$5:$C$11=$H5)*($D$5:$D$11=$I5)*($E$5:$E$11=$J5),$F$5:$F$11)

Here, the IF function contains multiple conditions in the first argument and the range of values in the second argument. We have multiplied the conditions to get our desired result. The range of values is the obtained Marks.

• PERCENTILE(IF(($C$5:$C$11=$H5)*($D$5:$D$11=$I5)*($E$5:$E$11=$J5),$F$5:$F$11),0.8)

This formula calculates the 80-th percentile. That is why we have entered 0.8 in the second argument. It will provide the result if all the conditions are satisfied.

Read More: Excel IF Function with 3 Conditions

2. Use Nested IF to Apply Multiple Conditions inside PERCENTILE Function in Excel

Here, we will use the PERCENTILE function with multiple IF conditions in Excel in a different way. An alternative way of using the PERCENTILE function with multiple conditions is to use nested IF functions. Here, we will use the previous dataset. So, without any delay, pay attention to the steps below to know more.

STEPS:

• First of all, select Cell I5 and type the formula:

• After that, hit Enter to see the result.

• Next, use the Fill Handle to see the result in Cell I6.

• To show the results in percentage, select the cells.
• Then, go to the Home tab and select Percentage in the Number section.

• In the end, you will see results like the picture below.

🔎 How Does the Formula Work?

• IF($E$5:$E$11<>””,$E$5:$E$11)

This formula denotes that the Range of values is E5:E11.

• IF($D$5:$D$11=$H5,IF($E$5:$E$11<>””,$E$5:$E$11)

Here, the formula contains the condition of the Year and a nested IF of the Range of values.

• IF($C$5:$C$11=$G5,IF($D$5:$D$11=$H5,IF($E$5:$E$11<>””,$E$5:$E$11))

This formula contains the condition of the Region and nested IF formulas that denote the Year and the Range of values. In this case, the formula will check whether the region is East, then, the year is 2020, and then the Marks.

• PERCENTILE(IF($C$5:$C$11=$G5,IF($D$5:$D$11=$H5,IF($E$5:$E$11<>””,$E$5:$E$11))),0.8)

The PERCENTILE function computes the 80-th percentile. That is why we have entered 0.8 in the second argument. It will provide the result if all the conditions are satisfied.

Read More: Example of VLOOKUP with Multiple IF Condition in Excel

3. Combine PERCENTILE & IF Functions with Multiple Condition in Excel Table

We can also use the PERCENTILE & IF functions together with multiple conditions in an excel table. Here, the previous dataset is converted into a table. Again, we will try to calculate the percentile based on two conditions.

Let’s observe the steps below to know the procedure.

STEPS:

• Firstly, create the structure to see the percentile like the picture below.

• Secondly, select Cell I5 and type the formula:

• Thirdly, press Enter to see the result.

Here, this formula will calculate the percentile if the region is East and the year is 2020. This formula works the same as the formula in Method-1. Here, we are using two conditions. The representation is different because we are applying this formula in a table.

• After that, drag down the Fill Handle to see the result in Cell I6.

• Next, to convert the numbers into percentages, select Percentage in the Number field from the Home tab.

• Finally, you will see results like the image below.

🔎 How Does the Formula Work?

• Table1[Region]=$G5

This is the first condition and it means the region will have to be East.

• Table1[Year]=$H5

It is the second condition and means the year will have to be 2020.

• IF((Table1[Region]=$G5)*(Table1[Year]=$H5),Table1[Marks])

This formula multiplies the conditions in the first argument and the range of marks in the second argument. The multiplication of the conditions means all conditions must be fulfilled to get the result.

• PERCENTILE(IF((Table1[Region]=$G5)*(Table1[Year]=$H5),Table1[Marks]),0.8)

The formula calculates the 80-th percentile in a table named Table1.

Things to Remember

Here, we have used an array formula in the above methods. If the formula doesn’t work after pressing Enter, then you need to press Ctrl + Shift + Enter.

Download Practice Book

Download the practice book here.

Conclusion

We have demonstrated 3 easy examples of PERCENTILE with multiple IF Condition in Excel. We have used different examples and also discussed the example to calculate percentile in an excel table. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.