Excel Conditional Logic Functions - IF, AND, OR NOT and IFERROR

Find out more about Excel Logical Functions in our Excel 2007: Advanced course.

You can use conditional logic in a formula to return a specific result depending on whether a certain test, or condition, is met. If the condition is true, one result will be calculated. If the condition is false, a difference result will be displayed.

Microsoft Excel 2007 provides several logical functions you can use for conditionally evaluating a calculation: IF, AND, OR, NOT, and IFERROR.

The IF function

The IF function evaluates a condition, or logical test. If the condition is true, the function returns a specific value. Otherwise, it returns another value. The syntax of the IF function is:

IF(logical_test,value_if_true,value_if_false)

In this syntax, logical_test is the criterion you want the function to evaluate,

value_if_true is the value to be returned if the condition is true, and

value_if_false is the value to be returned if the condition is false.

Creating nested functions

You can use nested functions to perform more complex calculations. A nested function serves as an argument of another function; in other words, it’s contained within another function. For example, an IF function can contain other IF functions as arguments. It can also contain the OR, AND, or NOT functions.

The OR, AND, and NOT functions

OR, AND, and NOT are also functions you can use to conditionally evaluate a formula. While you can use OR, AND, and NOT by themselves, they are more helpful if used within an IF function. You can use the OR, AND, and NOT functions within an IF function in order to determine whether multiple conditions are true, whether some conditions are true, or whether a condition is not true. The syntax for these functions, when nested within an IF function, is described below.

The OR function

With the OR function, only one of the conditions named needs to be true for the specified result to be displayed. Here’s the syntax:

IF(OR(logical1,logical2),value_if true,value_if_false)

The AND function

In the AND function, all conditions specified must be met for the value_if_true result to be returned.

IF(AND(logical1,logical2),value_if_true,value_if_false)

The NOT function

The NOT function reverses the value of its argument. In other words, you state the result you want if the condition specified is not true.

IF(NOT(logical),value_if true,value_if_false)

Nested IF functions

You use nested IF functions to evaluate multiple conditions. For example, use a second IF function as the value_if_false argument of the first IF function.

The IFERROR function

Explanation You can use the IFERROR function to check a formula for errors and to replace Excel’s default error message with a message you specify. For example, if you try to divide a number by zero in a formula, the error message #DIV/0! appears in the cell by default. You can replace this message with your own by using IFERROR.

The syntax for the IFERROR function is:

=IFERROR(value,value_if_error)

where value is the argument you want to check for an error, and value_if_error is the message you want to display if an error is found. If no error is found in the formula, the result of the formula is displayed.

Find out more about Excel Logical Functions in our Excel 2007: Advanced course.