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.

**Table of Contents**hide

## Download Practice Book

Download the practice book here.

## 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**.

## 3 Examples of PERCENTILE with Multiple IF Conditions in Excel

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:

`=PERCENTILE(IF(($C$5:$C$11=$G5)*($D$5:$D$11=$H5)*,$E$5:$E$11),0.8)`

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

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

- 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:** **How to Use Multiple IF Condition in Excel (3 Examples)**

### 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:

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

- 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:** **Excel If Function with Multiple Conditions (Nested IF)**

**Similar Readings**

**Excel VBA: Combined If and Or (3 Examples)****Excel VBA: If Then Else Statement with Multiple Conditions (5 Examples)****Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)****VBA IF Statement with Multiple Conditions in Excel (8 Methods)**

### 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:

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

- 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**.

**Read More:** **How to Use Multiple If Conditions in Excel for Aging (5 Methods)**

## 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**.

## 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.