Use the Excel 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. In Excel 365, there is also an IFS function, which can replace multiple newsted IF functions.
NOTE: In many cases, another Excel function would be more efficient than a complex IF formula. See the examples below.
The Excel IF function is helpful if you want to check something on a worksheet, then show result A or B. This short video shows the steps for setting up a simple IF formula. There are written steps below.
IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax:
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 result), so the IF formula in cell E7 also looks empty.
When cell D7 is not empty (FALSE result), the Total cell calculation shows the value of Price x Quantity.
If you need to do more than one logical test in a formula, you can combine multiple IF functions in one formula. This is called a nested formula, because the additional IF functions act as arguments, nested within another IF function.
In Example 1, the IF formula had one logical test -- is the Qty cell (D7) empty?
To check both the Price and Quantity cells, another IF function is added to the existing formula.
That creates a second logical test -- is the Price cell (C7) empty?
The results for different scenarios are shown in the screen shot below.
In the previous nested IF formula example, there were 2 logical tests:
Those tests were equally difficult.
In some cases, the logical tests in a nested IF formula have different levels of difficulty.
For example, we need to build a formula where product scores are grouped into the following rating categories:
|1||Good||80 or more||>=80|
|2||Average||60 to 79||B2>=60|
The 3 logical tests, above, are listed in order of difficulty:
To build the nested IF formula, with logical tests that have different levels of difficulty, it is important to put the hardest tests first, in order of difficulty.
Here is the sample data, with this formula in cell C7:
The score in cell B2 is 86, which passes the first logical test, so the result is "Good".
However, if the "Average" test is first in the formula, the result would be incorrect.
The score in cell B2 is 86, which passes the first logical test, so the result is "Average".
Here are a few important things to keep in mind, when building a nested IF formula:
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 cell E12 checks the linked cell (G11)l, to see if it contains "FALSE". If so, the tax amount is zero.
If cell G11 does not contain "FALSE", the subtotal in cell E10 is multiplied by the Tax Rate in cell D12, to show the tax amount.
In Excel 365, there is also an IFS function, which can be used instead of multiple nested IF functions. With the IFS function:
The IFS function has the following arguments in its syntax:
Note: Although you can enter up to 127 tests and their related values, Microsoft advises against nesting too many conditions. Limit the number of logical tests to just a few, or it will be difficult to build and maintain the IFS formula.
In this example, instead of using a nested IF formula, here's an IFS formula.
In cell E9, the following IFS formula checks 3 cells in an order form, to see if the product name, price and quantity have been entered.
Here's how this IFS formula works:
Below, you can see the results for this formula, in cells E9:E12
In Excel 365, you can use the IFS function, instead of multiple nested IF functions:
However, unlike the IF function, the IFS function does not have a final argument, for value_if_false
Without that argument, how can IFS formulas do the following?
As a workaround, to simulate the IF function's value_if_false argument:
For example, this formula has 2 logical tests, for Good and Average.
Any other scores, that don't pass the first two tests, would get a rating of "Poor", because they pass the final test
Question: If you need to test if one or more logical conditions are TRUE or FALSE, then show a result, is the IF function, or the IFS function, always the best solution to use?
Answer: No, not usually. If you need to do anything more than a simple True or False test, other Excel functions provide a more efficient solution to the problem.
Here is one example, where a VLOOKUP function is easier to set up and maintain, compared to a nested IF function formula, or an IFS function formula (in Excel 365).
In some workbooks, you might have a rating system, so you can change a number score to a letter grade. In the example shown below, if a product is tested, and gets a score of 85 or higher, it gets 3 stars -- the highest rating.
This IF formula, in cell D2, could calculate the rating:
Or, in Excel 365, this IFS formula could calculate the rating:
However, in both of those formulas, the rating levels are hard-coded into the formula. That could cause problems in the future, if the rating levels are ever changed.
Instead of using IF or IFS, you could set up a rating lookup table, with the lowest score for each rating, and the rating group.
Then, use this VLOOKUP formula, in cell D2, to calculate the rating:
In the future, if the rating levels are ever changed, just change the information in the RatingLookup table.
Here are a few more examples, where other formulas are more efficient than a nested IF function formula, or an IFS formula.
If your records are stored in a named Excel Table, you can use structured references in your formulas, instead of normal cell references.
For example, the following formula is in cell E7, to calculate the total price in that row:
This formula uses the table's column names, like [Price], instead of normal cell references, like C7.
Here are a few notes on normal and structured references:
To follow the examples in this tutorial, download the sample IF and IFS workbook. The zipped file is in xlsx format, and does not contain any macros.
Last updated: December 27, 2021 12:21 PM