Excel: How to use PERCENTILE with multiple IF conditions – 3 examples

If you want to learn how to use PERCENTILE with multiple IF conditions in Excel, then you’ve landed on the right page.

When managing sales data in Excel, understanding how to leverage both PERCENTILE and IF functions can transform your data analysis. This article focuses on that sweet spot, discussing how to use these functions together to analyze sales performance across different regions and years.

So, without any further ado, let’s dive in!

Excel PERCENTILE and IF function basics

The PERCENTILE function in Excel helps you find the K percentile in a set of values. For instance, you’d use this function to see which Salespeople are in the top 10%.

• How to Use: PERCENTILE(DATA, K)
  • DATA: The set of numbers you’re looking at.
  • K: The percentile you want (like 0.9 for the 90th percentile).

On the other hand, the IF function lets you make decisions in your formulas, giving one result if a condition is true and another if it’s false.

• How to Use: IF(condition, [value_if_true], [value_if_false])
  • condition: This is the first and mandatory argument. Here, you need to enter the condition that you want to check.
  • value_if_true: What to show if the condition is True.
  • value_if_false: What to show if the condition is False.

Using PERCENTILE function with multiple IFs in Excel

For our examples, we’ll delve into a dataset detailing Annual Regional Sales Transactions. These records span various regions and cover different years, providing a rich backdrop for applying multiple conditions in our percentile calculations.

Apply multiple conditions with Nested IF in Excel’s PERCENTILE function

For our first method, we’re going to combine the PERCENTILE function with multiple IF conditions in a new way by using nested IFs. This technique allows us to layer several conditions together in our sales data analysis. Let’s jump right in and break down the steps, making everything clear and straightforward.

• Select the cell where you want to get the PERCENTILE (in this case, it’s I5)
• Enter this formula =PERCENTILE(IF($C$4:$C$14=$G5,IF($D$4:$D$14=$H5,IF($E$4:$E$14<>””,$E$4:$E$14))),0.9) and hit Enter.

• Now Autofill to get the PERCENTILE for West 2021.
• In the end, you’ll see the results shown below.

Here’s how the formula works:

1. IF($C$4:$C$14=$G6, …): It checks each cell in the range C4:C14 to see if it matches the region specified in cell G6.
2. IF($D$4:$D$14=$H6, …): Within the region-matched cells, it further checks each cell in the range D4:D14 to see if it matches the year specified in cell H6.
3. IF($E$4:$E$14<>””, $E$4:$E$14): For cells that match both the region and year, it ensures that the sales amount cells in E4:E14 are not empty, including only the non-empty cells in the result.
4. PERCENTILE(…, 0.9): From the filtered list of sales amounts (non-empty cells that match the specified region and year), the formula calculates the 90th percentile.

By using 0.9 as the second argument in the PERCENTILE function, the formula determines the sales amount below which 90% of the filtered dataset lies. This effectively targets the top 10% of sales figures, meeting all specified conditions.

Multiplying nested IFs in Excel’s PERCENTILE function

Here, we’ll multiply multiple nested IFs inside the PERCENTILE function to calculate percentiles based on two conditions. We’ll find the percentile for sales in the West region for 2021 in Cell I5 and for the North region for 2020 in Cell I6.

• Select the cell where you want to get the PERCENTILE.
• Type this formula =PERCENTILE(IF(($C$5:$C$14=$H5)*($D$5:$D$14=$J5),$F$5:$F$14),0.9) and hit Enter.

• Now Autofill to get the PERCENTILE for North 2020.
• In the end, you’ll see the results shown below.

To include additional conditions in the formula, you simply integrate them by using multiplication. For instance, if we introduce ‘Gender’ as a new condition in Column E, you would adjust the formula accordingly to incorporate this extra criterion. To add the condition, just multiply it like the formula below.

=PERCENTILE(IF(($C$5:$C$14=$H$5)*($D$5:$D$14=$I$5)*($E$5:$E$14=$J$5),$F$5:$F$14),0.9)

Here’s a simplified breakdown of how the formula works:

1. IF(($C$5:$C$14=$H$5): This part of the formula scans cells C5 to C14 to find those matching the region specified in cell H5.
2. ($D$5:$D$14=$I$5): Within the matching regions, it checks cells D5 to D14 for the year specified in cell I5.
3. ($E$5:$E$14=$J$5): Additionally, it applies another condition to match another criterion (e.g., gender) specified in cell J5 within the range E5 to E14.
4. ($F$5:$F$14): It selects the corresponding sales figures from F5 to F14 for all cells meeting the above conditions.
5. PERCENTILE(…, 0.9): Lastly, the function calculates the 90th percentile for the sales amounts that passed all the filters.

By using 0.9 as the second argument in the PERCENTILE function, the formula identifies the sales amount below which 90% of the filtered dataset lies. This effectively targets the top 10% of sales figures, meeting all specified conditions.

Using PERCENTILE with IF for multi-condition Excel analysis

We can use PERCENTILE and IF together in an Excel table to work with more than one condition. Turning our dataset into a table, we’ll show how to figure out percentiles based on two conditions.

• Let’s start by making our table structure as shown below.

• Then select the cell for PERCENTILE and type this formula =PERCENTILE(IF((Table[Region]=$G5)*(Table[Year]=$H5),Table[Sales Amount (000$)]),0.9) and hit Enter.

Here, this formula will calculate the percentile if the region is West, and the year is 2021. This formula functions similarly to the one we used in the second method, applying two specific conditions to our analysis. The only difference here is in how we use it within an Excel table, tailoring our approach to fit the table format.

• Autofill to get the results for North 2020 by dragging down the Fill Handle.
• Finally, you will see the results shown below.

Here’s how the formula works:

1. Table[Region]=$G5: Checks if the ‘Region’ column in the table matches the value in cell G5 (e.g., ‘East’).
2. Table[Year]=$H5: Checks if the ‘Year’ column in the table matches the value in cell H5 (e.g., ‘2020’).
3. IF((Table[Region]=$G5)*(Table[Year]=$H5),Table[Sales Amount (000$)]): This formula combines the conditions using multiplication, ensuring both conditions are met. It selects the ‘Sales Amount (000$)’ values from the table where both conditions are true.
4. PERCENTILE(IF((Table[Region]=$G5)*(Table[Year]=$H5),Table[Sales Amount (000$)]),0.9): This calculates the 90th percentile from the ‘Sales Amount (000$)’ values that meet the specified conditions in the table.

So, the formula effectively identifies the 90th percentile of sales amounts for entries in the table that belong to the ‘East’ region and are from the year ‘2020’.

Conclusion

We’ve presented three straightforward examples illustrating the usage of the PERCENTILE function with multiple IF conditions in Excel. Each example offers a unique scenario, demonstrating practical applications. Additionally, we’ve provided detailed explanations for calculating percentiles within an Excel table.

If you want to learn more about Excel, give these guides a read:

• How to find last column with data in Excel
• How to sort a bar chart without sorting data in Excel
• What is a Pivot table in Excel and how do you make one?