## 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, it returns one value if it is **TRUE **or 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

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.

### Method 1 – Multiplying IF Conditions Inside the Excel PERCENTILE Function

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

**Steps:**

- Select
**Cell I5**and insert this formula:

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

- Hit
**Enter**to see the result.

- Drag the
**Fill Handle**down to see the result in**Cell I6**.

- Select
**Cells I5**and**I6**. - Go to the
**Home**tab and select**Percentage**from the**NumberÂ**field.

- You will see the percentages.

- If you want to add more conditions, you need to add the condition in the formula by multiplying. We have added
**Gender**as another condition in**Column E**.

- To add the condition, 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)`

- 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

### Method 2 – Use a Nested IF to Apply Multiple Conditions Inside the PERCENTILE Function in Excel

**Steps:**

- Select Cell I5 and insert this 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)`

- Hit
**Enter**to see the result.

- Use the Fill Handle to see the result in
**Cell I6**.

- Select the cells.
- Go to the
**Home**tab and select**Percentage**in the**NumberÂ**section.

- 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

### Method 3 – Combine PERCENTILE and IF Functions with Multiple Condition in Excel Table

The previous dataset is converted into a table. We will calculate the percentile based on two conditions.

**Steps:**

- Create the structure to see the percentile like in the picture below.

- Select
**Cell I5**and insert this formula:

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

- Press
**Enter**to see the result.

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**. We are using two conditions. The representation is different because we are applying this formula in a table.

- Drag down the
**Fill Handle**to see the result in**Cell I6**.

- To convert the numbers into percentages, select
**Percentage**in the**Number**field from the**HomeÂ**tab.

- 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 the Practice Book**

**<< Go Back to Multiple IF Condition in Excel | Excel IF Function | Excel Functions | Learn Excel**