How to Use Multiple Logical Functions in MS Excel

Excel's IF function is the backbone of logical arguments to conditionally calculate data or assess conditions. This function supports nested IF functions to assess multiple criteria, but the result of nesting IF functions is often a complicated, unwieldy formula. A better option is combining multiple logical functions, such as the AND or OR functions, to reduce clutter and expand the number of comparisons.

IF Function

The IF function follows the format format =IF(condition1,action_if_true,action_if_false) and supports up to 64 nested IF functions for complicated comparisons. Nesting an IF function adds a complete IF function to one of the actions, such as =IF(condition1,IF(condition2,action_if_true,action_if_false),action_if_false).

Nested IF functions
credit: C. Taylor

As an example, to flag employees who made over $30,000 in sales in the first quarter in order to offer them a bonus, you can nest IF functions as =IF(C4=1,IF(D4>30000,"Bonus",""),""). This formula, however, quickly becomes confusing when you add more criteria, such as looking for the fourth quarter as well; in that case, the formula becomes =IF(C4=1,IF(D4>30000,"Bonus",""),IF(C4=4,IF(D4>30000,"Bonus",""),"")).

AND and OR Functions

The logical AND and OR </ahref="https:>functions assess up to 255 criteria and return either "True" or "False" using the format =AND(condition1,condition2,...). The AND function returns "True" if all conditions are true, but the OR function returns "True" if any of the conditions are true. Because the IF function uses logical results to choose an action, you can use the AND, OR and IF functions together to reduce the number of nested IF functions, thereby simplifying the formula.

Combining IF, NOT and OR functions
credit: C. Taylor

As an example, to flag an employee who made over $30,000 in sales in the first quarter, the IF function becomes =IF(AND(C4=1,D4>30000),"Bonus",""). However, if you wanted to look for the first or fourth quarter, you'd need to include an OR function as =IF(AND(OR(C6=1,C6=4),D6>30000),"Bonus","").

True, False and Not Functions

The TRUE and FALSE functions use the format =TRUE() or =FALSE(), but they have little use in Excel 2013, because the results are identical to simply entering True or False, respectively and were primarily included in Excel 2013 for compatibility reasons. The NOT function, however, is useful to reverse a logical argument using the format =NOT(condition), such as =NOT(2+2=4) to return "False."