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.
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).
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",""),"")).
Video of the Day
AND and OR Functions
The logical AND and OR
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."