Use the IF function when you want to test something, and show one result if the test result is True, and a different result if the test result is False

Here are some examples of how you could use the IF function::

- If the sales total is $20,000 or greater, show "Good", otherwise, show "Poor".
- If no quantity is entered in cell D2, show an empty string "". Otherwise, multiply the quantity by the unit price.
- If no quantity is entered in cell D2, OR no unit price is entered in cell E2, show an empty string "". Otherwise, multiply the quantity by the unit price.

The IF function has three arguments:

**IF(logical_test, [value_if_true], [value_if_false])**

**logical_test**: Required. Any value or expression that can be evaluated to TRUE or FALSE.**value_if_true**: Optional. The value that you want to be returned if the logical_test argument evaluates to TRUE.**value_if_false**: Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE.

With the IF function, you test something, such as the value in a cell. In the second argument, you can specify the result if the test result is TRUE. In the function's third argument, specify the result if the test result is FALSE.

For example, this formula tests the sales total in cell B2, to see if it is $20,000 or greater.

- If B3 is $20,000 or greater, the test result is TRUE, and the formula result is "Good"
- If B3 is not $20,000 or greater, the test result is FALSE, and the formula result is "Poor"

**=IF(B2>=20000,"Good","Poor")**

You can nest an IF function within another IF function, to create more options for the results.

In the previous example, if the sales total in cell B2 is $20,000 or greater, the result is "Good", otherwise, the result is "Poor".

**=IF(B2>=20000,"Good","Poor")**

We can add another test, to see if the result is $10,000 or greater. If so, show "Average" as the result. If not, show "Poor" as the result.

On its own, the new IF formula would be:

**=IF(B2>=10000,"Average","Poor")**

To add this new test, to the existing formula, we'll nest this new IF statement into the formula, as the third argument, replacing the existing third argument.

All the red text from the new formula will replace all the red text in the existing formula:

**=IF(B2>=20000,"Good","Poor")**

becomes

**=IF(B2>=20000,"Good",IF(B2>=10000,"Average","Poor"))**

Each of the IF functions has three arguments, and the second IF function is the third argument for the first IF function.

NOTE: When nesting IF functions, be sure to put the hardest test first. In this example, there are two tests:

- B2>=20000
- B2>=10000

If the amount in cell B2 is 25000, it will pass both of the tests. However, if the test order is changed,

**=IF(B2>=10000,"Average",IF(B2>=20000,"Good","Poor"))**

The result of the formula would be "Average", because the results of the first test are TRUE.

Download the sample IF workbook

Last updated: June 7, 2015 8:02 PM