Using the IF Function

There are many instances when using Excel where we need to write a formula that produces one of two different results, depending on some condition. For example, consider the following version of our cylinder analysis worksheet:

Suppose we wish to add a "Class" item in column F indicating whether the cylinders are standard or overweight. This can be done by entering the following formula in cell F7 and copying it down to the end of the table:

=IF(E7<35000, "Standard", "Overweight")

Resulting in the following:

Syntax

The syntax of the IF function is as follows:

= IF(logical_test, value_if_true, value_if_false)

The logical_test argument needs to be a conditional expression that returns a TRUE or FALSE value. Conditional expression are typically formed with one of the following operators:

If the conditional expression evaluates to true, the value_if_true argument is used. Otherwise, the value_if_false argument is used. These arguments can any type of expression, including constants, cells references, or formulas. Here are some additional example formulas that use the IF function:

=IF(A4<>0, 1/A4, "Error - Divide by Zero!")

=IF(B4<=$D$2, -2.3*G4/4, -3.9*G4/4+6)

=IF(units="Metric", "[m/sec], "[ft/sec]")

Compound Conditions

Sometimes we need to utilize compound conditional expressions with the IF function. But we need to be very careful when doing so. For example, suppose we want to represent the following mathematical expression:

0 ≤ x ≤ 5

in an Excel formula and "x" is stored in cell B5. It would be tempting to use the following conditional expression:

0<=B5<=5

for the first argument in the IF function. However, this creates a useless and incorrect expression that will always return TRUE, regardless of the contents of cell B5. This is because a compound expression like this is evaluated one operator at a time from left to right. In other words, the first part of the expression:

0<=B5<=5

will be evaluated first. The result of this evaluation will be True or False, depending on whether or not B5 is greater than or equal to zero. This result is then compared against the rest of the expression. In computational terms, True and False evaluate to 1 or 0, respectively. Thus, if B5 is greater than or equal to zero, the expression simplifies to:

1<=5

otherwise (B5>0), it simplifies to:

0<=5

Both of these statements will then evaluate to True, regardless of the value of B5. In other words, the original expresssion is equivalent to:

(0<=B5)<=5

which is fundamentally different from the mathematical expression we are trying to emulate. To solve this problem correctly, we need to use the AND function as follows:

AND(0<=B5, B5<=5)

This function returns True if both statements are true. Otherwise it returns False. Likewise, there is an OR function that returns True if either or both of the two expressions evaluate to True.

Nested IF Functions

It is possible to nest multiple instances of the IF function. For example:

=IF(A4>=18,"Adult",IF(A4>12,"Teen","Child"))

The second IF function is only evaluated in the first condition is False. There are three possible outcomes in this case: "Adult", "Teen", and "Child".

Sample Workbook

The workbook used in the first example shown above can be downloaded here:

cylinders3.xlsx

Exercises

You may wish to complete following exercises to gain practice with and reinforce the topics covered in this chapter: