How to Use Multiple Logical Functions in MS Excel

By C. Taylor

Integrating multiple logical functions, IF, AND, OR, TRUE, FALSE, NOT, enables Excel 2013 to assess numerous criteria when calculating results.

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).

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",""),"")).

Tip

The IF function is more clearly understood by reading the formula as, "If [logical criterion] is true, then do [this]; otherwise, do [this] instead." You would read the latter example as, "If it's the first quarter and if sales are more than $30,000, then return 'Bonus'; otherwise, if it's the fourth quarter and if sales are more than $30,000, then return 'Bonus'; otherwise, return a blank space." Although clearer to read, even this format becomes unwieldy.

AND and OR Functions

The logical AND and ORfunctions 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

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","").

Tip

The AND function reads as, "If all of these conditions are true, return 'True;' otherwise, return 'False.'" Similarly, the OR function reads as, "If any of these conditions are true, return 'True;' otherwise, return 'False.'" In the latter example, the formula reads as, "If it's the first or fourth quarter and sales are more than $30,000, return "Bonus;" otherwise return a blank space." That's much clearer than the nested IF statements.

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."

Tip

The IFERROR and IFNA functions are helpful to evaluate and change entries that produce an error or "#N/A", respectively. They follow the format =IFERROR(condition,value_if_error) or =IFNA(condition,value_if_na).