Contextures

How to Use Excel IF Function Examples

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.

Introduction

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

  1. IF the Quantity cell is empty, leave the Total Amount cell empty.
  2. IF the Quantity cell is empty, or IF the Price cell is empty, leave the total cell empty. (Nested IF formula)
  3. 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:

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

How the IF Function Works

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

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

if function checks for empty cell

Example 1: Check for Empty Cell

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:

  1. TEST cell D7, to see if it is empty (D7=""),.
  2. If D7 is empty (TRUE), the cell with the Total formula will show nothing ("").
  3. 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.

if function checks for empty cell

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

if function checks for empty cell

Example 2: Nested IF Functions

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

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

Notes on Nested IF Formulas

  1. 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.
  2. 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.

Formula in Named Excel Table

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]

total price formula

Check for Empty Cells

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

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

The results for each possible scenario are shown below.

total price formula

Order of Nested IF Functions

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

Harder Test First

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.

Easier Test First

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.

Example 3: IF Function With Check Box

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)

total price formula

Download the Sample File

Download the sample IF workbook

 Get Excel News


Custom Search

 

30 Excel Functions in 30 Days

 

 

 

 

Last updated: June 5, 2016 10:40 AM