By checking for a condition and delivering a result if it is TRUE or FALSE, the IF function enables us to logically compare a value to what we anticipate. What happens, however, if we need to test many conditions, where, for example, all conditions must be True or False (AND), or only one condition must be True or False (OR)? We can use OR and AND functions independently, but we will frequently see the IF function in combination with them. With that in mind, in this article, we learn 3 advanced examples to use IF function with OR and AND statement in Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
3 Advanced Examples to Use IF Function with OR and AND Statement in Excel
Before we dive into the examples, let’s first recap the most important part of the formula combination namely the IF function. Comparing thoroughly two or more logical conditions, the IF function returns TRUE or FALSE outputs. Furthermore, we will combine the OR and AND statements with the IF function with hands-on examples to understand better. To demonstrate, let’s take a dataset that represents the financial states of various departments of an organization. Here, we add the proper conditions in row 12 to implement the formula properly.
Example 1: Utilize IF Function with OR and AND Statement in Excel to Analyze Financial Data
The first example aims to analyze the financial data of the organization by combining the IF function with the OR and AND statements. Here, we will set four logical conditions. The first two conditions are in the OR statement and the other two are in the AND statement. Only one or both conditions can be true for the whole statement to be TRUE in the case of the OR statement. Alternatively, for the AND statement, both statements have to be true for the whole statement to be True. If the conditions are true we will flag the departments by displaying Flag. Otherwise, we will display OK. Let’s see the formula combination below to do the analysis.
Steps:
- Firstly, in cell G5, type the following formula combination.
=IF(OR(C5>$C$12,D5>$D$12,AND(E5=$E$12,F5>$F$12)),"Flag","OK")- Further, press the Enter or Tab keys to see the output Flag.
- This represents the conditions are TRUE for the Production department.
- Later, use the AutoFill tool to obtain the rest of the outputs.
- Thus, we obtain the results in our dataset with the combined formula.
- OR(C5>$C$12,D5>$D$12 indicates the statement is TRUE either the Budget is > 200,000 or YTD Spent is > 100,000.
- AND(E5=$E$12,F5>$F$12) represents the statement is TRUE if E5 is + Yes and Overrun% > 20% are true.
- IF(OR(C5>$C$12, D5>$D$12, AND(E5=$E$12, F5>$F$12)),” Flag”, “OK”) commands if the conditions are TRUE then display Flag otherwise OK.
Read More: How to Use IF with AND Function in Excel (Formula + VBA)
Example 2: Apply IF Function with OR and AND Statement to Distribute Performance Bonuses
The objective of the second example is to apply 3 conditions using OR and AND statements with the IF function for the distribution of performance bonuses. The conditions are if the Total sales are > 300,000 then give the employee a 1000 bonus. Also, if the Total sales are > 295,000 and Training is = Yes then give him the bonus. Otherwise, do not give any bonus. Follow the instructions for implementation.
Steps:
- To begin with, type the following formula in cell G5,
=IF(OR(E5>$D$11,AND(E5>$E$11,F5=$F$11)),1000,0)- Pressing the Enter key will display the output 0.
- Now, drag the AutoFill tool down to fill the rest of the cells in column G.
- Lastly, we obtain the desired result.
- OR(E5>$D$11 represents Total in column E has to be > 300,000 for the statement to be TRUE.
- AND(E5>$E$11, F5=$F$11) indicates the statement is TRUE if both Total > 295,000 and Training = Yes are true.
- IF(OR(E5>$D$11, AND(E5>$E$11, F5=$F$11)),1000,0) commands to print 1000 if TRUE otherwise 0.
Read More: How to Check If Cell Contains One of Several Values in Excel
Similar Readings
- How to Use Multiple IF Statements in Excel Data Validation
- Excel IF Statement with VLOOKUP for Multiple Conditions Range
- How to Use If Statement Based on Cell Color in Excel (3 Examples)
- Dynamic Data Validation List in Excel with IF Statement Condition
- How to Use IF Statement with Not Equal To Operator in Excel
Example 3: Create Gradesheet Calculators Using IF Function with OR and AND Statement
In the final example, we will create a grade sheet calculator by applying logical conditions using OR and AND statements combined with the IF function. The conditions are the same as in the first example. We will know more about this below. Therefore, let’s start following the instructions.
Steps:
- Initially, in cell G5, write the following formula.
=IF(OR(C5>$C$11,D5>$D$11,AND(E5=$E$11,F5>$F$11)),$G$12,$G$13)- Afterward, tap the Enter key.
- Next, use the AutoFill tool to get the rest of the results.
- OR(C5>$C$11,D5>$D$11 indicates the statement is TRUE either the Test-1 is > 80 or Test-2 is > 75.
- AND(E5=$E$11,F5>$F$11) represents the statement is TRUE if E5 is = Yes and Attendance% > 70% are true.
- IF(OR(C5>$C$11, D5>$D$11, AND(E5=$E$11, F5>$F$11)),$G$12,$G$13) commands if the conditions are TRUE then display Passed otherwise Failed.
- While using the formula, referencing an error cell will return a #VALUE! error.
- Moreover, keep in mind that the formula’s evaluation values do not contain commas (,). Doing that will result in a #VALUE! error.
Read More: How to Use If Then Else Statement in Excel VBA (4 Examples)
Conclusion
In conclusion, we have discussed some advanced examples of how to use the IF function with OR and AND statement in Excel with an example. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.
Related Articles
- How to Use Conditional Formatting If Statement Is Another Cell
- Excel IF Statement Between Two Numbers (4 Ideal Examples)
- How to Find Sum If Cell Color Is Green in Excel (4 Easy Methods)
- Prepare IF Statement Contains Multiple Words in Excel
- How to Use Wildcard with If Statement in Excel (5 Methods)
- Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)
- How to Use IF Statement with Yes or No in Excel (3 Examples)
