You can use the following formula to perform a **Percentile IF** function in Excel:

=PERCENTILE(IF(GROUP_RANGE=GROUP, VALUES_RANGE), k)

This formula finds the kth percentile of all values that belong to a certain group.

When you type this formula into a cell in Google Sheets, you need to press **Ctrl + Shift + Enter** since this is an array formula.

The following example shows how to use this function in practice.

**Example: Percentile IF Function in Google Sheets**

Suppose we have the following dataset that shows the exam score received by 20 students who belong to either class A or class B:

Now suppose we’d like to find the 90th percentile of the exam scores only among students who were in class A.

To do so, we can type the following formula into cell F2:

**=PERCENTILE(IF(B2:B21=E2, C2:C21), 0.9)**

Once we press **Ctrl** + **Shift** + **Enter**, the 90th percentile of exam scores among students in class A will be shown:

From the output we can see the value at the 90th percentile of exam scores in class A was **93.2**.

To calculate the 90th percentile of exam scores for class B, we can simply change the value in cell E2.

The formula will automatically calculated the 90th percentile of exam scores among students in class B:

We can see that the value at the 90th percentile of exam scores in class B was **89.8**.

Note that in these examples we chose to calculate the 90th percentile, but you can calculate any percentile you’d like.

For example, to calculate the 75th percentile of exam scores for each class you can replace **0.9** with **0.75** in the formula.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Calculate a Five Number Summary in Google Sheets

How to Calculate Mean and Standard Deviation in Google Sheets

How to Calculate Deciles in Google Sheets

very useful. Important to note that arrayformula must be used (as shown in the screenshot).