Buzz
Using IF function with multiple conditions
Understanding how to nest multiple conditions with the IF function is crucial. Know its functionality and formula. The outcome will either be True or False.
Definition
Exploring Excel's versatile IF function for conditional comparisons, returning True or False based on a condition check.
Syntax: =IF(Condition is True, do something, else do something else)
The IF function with multiple conditions enables us to compute checks against various conditions, returning corresponding results. For instance, nesting IF functions with AND, OR, and other functions to match specified conditions.
Usage Notes:
In Excel, conditions are evaluated from left to right. If the first condition does not match, Excel immediately checks the second, third conditions, and so on. Conversely, if the first condition matches, Excel does not evaluate subsequent conditions but displays the result immediately.
Example of IF function with multiple conditions
Once you've grasped the essence of the IF function in Excel, let's dive into an example of its usage:
Suppose you need to classify students as excellent, good, fair, or poor based on certain conditions:
- Excellent students achieve scores above 349
- Good students score between 300 and 349
- Fair students score between 250 and 299
- Poor students score below 250
To rank students based on these criteria, you need to nest values into the IF function structure with multiple conditions as follows:
=IF(B2>349, 'Excellent', IF(B2>=300, 'Good', IF(B2>250, 'Fair', 'Poor')))
Using the IF function to rank studentsIf you still find the IF formula above difficult to understand, here's how Mytour explains it to you:
- First = IF(B2>349,'Excellent', meaning based on cell B2, if it exceeds 249, it will display Excellent
- Second = IF(B2>=300, 'Good', meaning based on cell B2, if the number is less than or equal to 200, it will display Good.
- Finally = IF(B2>250, 'Fair','Poor'), meaning based on cell B2, if the number is greater than 250, it will display Fair, otherwise, it will display Poor.
Combining IF function with multiple conditions
The IF function in Excel can also be combined with other calculations to yield the most accurate results. To understand this combination better, please continue to follow the following cases:
Case 1
You need to perform a calculation for the total price of goods. The price of each product will vary based on the quantity of goods. That is, the price of the product will depend on the given condition.
Consider the condition table below:
| Số lượng hàng hóa | Giá |
| 1 đến 109 | 20 |
| 110 đến 190 | 18 |
| 200 đến 499 | 16 |
| 500 đến 999 | 13 |
| Trên 1000 | 12 |
To perform the sum calculation, you need to combine multiplication in Excel with the IF function for multiple conditions. This means that Excel will automatically calculate the total based on the corresponding unit price for the quantity of goods sold that meets a certain condition.
=B8*IF(B8>=1000, 12, IF(B8>=500, 13, IF(B8>=200, 16, IF( B8>=110, 18, IF(B8>=1, 20, '')))))
If the quantity of goods you sell is 999, the result will be 1300, as shown in the image below.
Result when applying nested IF conditions to calculate the totalCase 2
If you already understand the IF function with multiple conditions well, you can certainly utilize advanced IF functions in scenarios like the following:
Instead of inserting a specific number into the combined IF function, you can insert values from any cell in Excel.
Change =B8*IF(B8>=1000… to =B8*IF(B8>=B6… This means the number 1000 will be replaced with data from a cell somewhere in Excel (B6).
Utilize advanced IF functions by using values from any cellAdditionally, you can also halt Excel's operation in certain cases when it exceeds the permitted conditions.
For example: When the total quantity of items exceeds 2000, the calculation won't be executed.
=IF(OR(B8>2000,B8<1), 'exceeds conditions', B8*IF(B8>=1000,12, IF(B8>=500, 13, IF(B8>=200, 16, IF( B8>=110, 18, IF(B8>=1, 20, ''))))))
Prevent cases of exceeding conditions using the IF functionCombining IF function with other functions
In some cases, you need to combine the IF function with other functions to solve the problem. Here are some commonly used combinations.
Combining IF with AND, OR functions
If you need to calculate bonuses for employees but prioritize female employees in technical or IT departments. Specifically, female employees in IT and technical departments will receive a bonus of $800 instead of $780 like other employees.
To solve this calculation, we'll combine the AND and OR functions in Excel similar to the image below:
Calculating bonuses for employees with specific priorities by combining the IF function with AND, ORCombining IF function with CONCAT/CONCATENATE
The combination of IF function with CONCAT/CONCATENATE makes it easy for users to connect multiple results together. To understand this combination better, please see the example below.
IF function combined with CONCATENATEOther multi-condition functions besides IF
Excel also has some functions that serve similar purposes to the IF function with multiple conditions. You can choose from the list of functions below to apply in certain cases because they have relatively simple formulas.
IFS function
The IFS function functions similarly to the IF function with multiple conditions. The result following each condition will be applied when the condition is true, making formula writing easier. Additionally, you'll encounter fewer errors like missing parentheses when using this function.
In case you want to fill in the discount percentage based on the number of Licenses (qualifications). The formula is as follows: =IFS([Condition1, Value if True1, Condition2, Value if True2, Condition3, Value if True3)
The IFS function has a simpler formula than the IF function with multiple conditionsVLOOKUP function
The VLOOKUP function replaces the IF function with 2 or more conditions in some cases as follows:
All conditions given must be numeric
All conditions must be continuous and in ascending order.
Case requiring determination of employee bonuses based on KPI.
=VLOOKUP(D3, $I$3:$J$10, 2,1)
Using VLOOKUP function to replace IF function with 2 conditionsSWITCH function
The SWITCH function is also a multi-condition function similar to the IF function with multiple conditions. The SWITCH function allows people to compare a value with other values. Based on a predefined list, it provides a result corresponding to the first value. If no value matches the condition, Excel will automatically switch them to the default value.
Formula:
=SWITCH(Value to convert, Value to match 1, Value to return if match 1, Value to return if no match)
Apply as shown in the image below:
Example of using the SWITCH functionThat's it! Mytour has guided you on using the IF function with multiple conditions. If you have any further questions, feel free to leave a comment for Mytour support. Thank you for reading the article.
