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.

NOTE: In many cases, another Excel function would be more efficient than a complex IF formula. See the examples below.

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

- IF the Quantity cell is empty, leave the Total Amount cell empty.
- IF the Quantity cell is empty, or IF the Price cell is empty, leave the total cell empty. (Nested IF formula)
- IF the "Local" check box is checked, add a sales tax.

Often, another Excel function works better than
IF. Here are some examples of calculations where you **should NOT use
IF**:

- Converting students' numeric grades to letter grades -- use VLOOKUP
- Select an interest rate, based on the transaction date -- use HLOOKUP
- Show a specific value instead of an error result -- use IFERROR (Excel 2007 and later)

There are 3 parts (arguments) to the IF function:

- TEST something, such as the value in a cell.
- Specify what should happen if the test result is TRUE.
- Specify what should happen if the test result is FALSE.

In this example, the Total in cell E7 should only show an amount if the Quantity has been entered in cell D7.

These are the 3 parts (arguments) to the IF function for this example:

- TEST cell D7, to see if it is empty (
**D7=""**),. - If D7 is empty (TRUE), the cell with the Total formula will show nothing
(
**""**). - If D7 is not empty (FALSE), the cell with the Total formula will multiply
Price x Quantity (
**C7*D7**)

The completed formula is: **=IF(D7="","",C7*D7)**

In the screen shot below, cell D7 is empty (TRUE), so the IF formula in cell E7 also looks empty.

When cell D7 is not empty (FALSE), the Total cell shows the value of Price x Quantity.

You can nest one or more IF functions within another IF function, to create more options for the results. In this example, the formula has two IF functions, to check both the Price and the Quantity cells

- IF the Price cell is empty (""), the total will be empty ("")
- Then, IF the Quantity cell is empty (""), the total will be empty ("")
- Otherwise, multiply Price x Qty to calculate the Total

- When building nested IF formulas, the order in which the tests are listed might be important. See the section below -- the hardest tests must come first.
- Some nested IF formulas can be replaced by a VLOOKUP formula, for more flexibility. See the example of converting numeric grades to letter grades for students.

In this example, the orders are stored in a named Excel Table. As a result, there are structured references in the formula, that show the field names, instead of normal cell references.

For example, to calculate a simple total (Price x Quantity), this formula
would be used: **=[Price] *** **[Qty]**

In Example 1, only the Quantity cell was checked in the IF formula. Here is that formula from Example 1, written with structured references:

**=IF([Qty]="","",[Price]*[Qty])**

To check both the Price and Quantity cells, **another IF will be added**
to the existing formula:

**=IF([Price]="","",**IF([Qty]="","",[Price]*[Qty])**)**

- If the Price cell is empty (TRUE), the cell with the Total formula
will show nothing (
**""**) - If the Qty cell is empty (TRUE), the cell with the Total formula will
show nothing (
**""**) - If neither cell is empty, the total is calculated (
**[Price]*[Qty]**)

The results for each possible scenario are shown below.

When nesting IF functions, be sure to put the hardest tests first, or the results could be incorrect.

For example, in the following formula, there are two tests:

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

If the amount in cell B2 is 25000, the result would be "**Good**",
because it is greater than 20000.

However, if the tests are reversed, the formula would check for >=10000 first.:

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

Now, if the amount in cell B2 is 25000, the result of the formula would
be **"Average"**, because the results of that first test
are TRUE.

In this example, sales tax will be applied to local orders. On the order form, there is a check box that is linked to cell G11.

- For local orders, click the check box, to add a check mark, and the linked cell shows TRUE
- If the check mark is removed, the linked cell shows FALSE

The IF formula in the Tax cell checks the linked cell, to see if it contains "FALSE". If so, the tax is zero. Otherwise the subtotal is multiplied by the Tax Rate.

**=IF(G11=FALSE,0,E10*G12)**

Download the sample IF workbook

Last updated: May 1, 2016 11:05 AM