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::
There are 3 parts (arguments) to the IF function:
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:
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
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:
To check both the Price and Quantity cells, another IF will be added to the existing formula:
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 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.:
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.
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.
Download the sample IF workbook
Last updated: May 1, 2016 11:05 AM