Download Practice Workbook
Example 1 – Utilize IF Function with OR and AND Statement in Excel to Analyze Financial Data
Steps:
- Enter the following formula in cell G5.
=IF(OR(C5>$C$12,D5>$D$12,AND(E5=$E$12,F5>$F$12)),"Flag","OK")- Press the Enter or Tab keys to see the output Flag.
- This represents the conditions are TRUE for the Production department.
- Use the AutoFill tool to obtain the rest of the outputs.
Formula Breakdown
- 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
Steps:
- Enter 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.
- Drag the AutoFill tool down to fill the rest of the cells in column G.
Formula Breakdown
- 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
- 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
Steps:
- Enter the following formula in cell G5.
=IF(OR(C5>$C$11,D5>$D$11,AND(E5=$E$11,F5>$F$11)),$G$12,$G$13)- Press Enter.
- Use the AutoFill tool to get the rest of the results.
Formula Breakdown
- 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.
Notes
- While using the formula, referencing an error cell will return a #VALUE!
- The formula’s evaluation values do not contain commas (,). Doing that will result in a #VALUE!
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)
