The IF function is quite powerful as a standalone function. However, when combined with AND/OR functions, it becomes much more powerful, allowing you to test multiple conditions at once.
IF Statements Explained
An IF statement is used to evaluate a condition and output a value depending on whether the condition is true or false. IF formulas are the foundation of logic in Microsoft Excel and allow you to check if data items meet a certain criterion during data analysis and interpretation.
The IF function has a basic syntax:
=IF(condition_to_test, value_if_true, value_if_false)IF statements can be combined with other functions for more detailed analysis and complex reporting.
AND/OR Logical Functions
The AND/OR functions are capable functions on their own, but they can also be used as logical operators for more complex logic.
The AND function is used to determine if all conditions in a logical test are true, while the OR function is used to check if at least one condition in a logical test is true.
The AND function returns TRUE only if all the conditions in the formula are met. If any condition is FALSE, the result will be FALSE. It follows the syntax:
=AND(condition1, [condition2], ...)The OR function returns TRUE if any one of the conditions is true. It only returns FALSE if all conditions are false. The syntax for the OR function is:
=OR(condition1, [condition2], …)
Just as in Boolean logic, Excel has a third logical function, NOT, that returns the opposite truth value of the condition, i.e. returns FALSE when the condition is TRUE and returns TRUE when the condition is false.
The AND and OR functions can take up to 255 different conditions.
In general, the AND function is used when all conditions must be met, while OR is used when at least one condition being met is sufficient.
How to Use IF Statements with AND/OR Conditions
A simple IF statement allows you to test only one condition. In contrast, a nested IF statement—where multiple IF statements are included in one formula—evaluates from the top down, stopping at the first true statement and disregarding the others. To test for two or more criteria, you should use the AND or OR functions nested within an IF statement.
A nested IF/AND statement follows the syntax:
=IF(AND(condition1, [condition2], ...), value_if_true, value_if_false)The nested IF/OR statement has a similar syntax:
=IF(OR(condition1, [condition2], ...), value_if_true, value_if_false)Example 1: Employee Bonuses
As mentioned earlier, a combined IF and AND/OR statement becomes useful when you need to check for multiple criteria, such as determining whether an employee has earned a bonus based on their sales performance, employment status and the number of referrals they’ve made.
Let’s say a small business lists its employees and their statistics in the following spreadsheet:
There is a $500 bonus for all full-time employees who have made over $3000 in revenue and at least one referral. Everyone else gets $200. To determine which employee gets what, I would use the following formula:
=IF(AND(B2>3000, C2="Full-Time", D2>0), 500, 200)
To be more generous, I could modify the formula to use the OR function. I could then award a $500 bonus to every employee who brought in over $3000 or made at least three referrals, regardless of their employment status. Here is what the resulting formula would look like:
=IF(OR(B2>3000, D2 >= 3), 500, 200)
Example 2: Promotions
Employee promotions are another scenario in which the nested IF/AND and IF/OR functions could be useful.
Let’s say the same business wants to promote some of its full-time staff who have met certain criteria, namely a minimum of two years in service and a performance rating of at least 3 on a 5-point scale. They could use the following formula:
=IF(AND(C2="Full-Time", F2>2, G2>=2), "Yes", "No")
A more lenient promotion policy could promote all employees who meet only one of the predefined criteria using the formula:
=IF(OR(F2>2, G2 >= 2), "Yes", "No")
Nested IF and AND/OR functions are quite powerful and unlock a new world of analysis and data reporting. You can use them for conditional formatting in Excel and nest multiple AND/OR functions for even more complex logic. However, you should keep in mind nesting makes formulas harder to debug and maintain.
