Categories
Excel

How to Use the IF Function in Excel

You can use the IF function in Excel to evaluate a condition in your spreadsheet. The function returns one value if the condition is true, it returns another value if the condition is false.

The diagram (below) illustrates an example where the IF function could be used.

Flow chart illustrating the typical process for an IF function
Flow Chart

If the quantity of an item falls below 20, an additional 15 items should be ordered. If there are 20 items or more, no action is required.

This IF Function is part of the Level 2 course when we do Microsoft Excel training. Most participants find it very useful.

Syntax

Here is the syntax for the IF function in Excel.

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

logical_test is the condition you want to test. Logical_test will return a true or false

value_if_true [optional] is returned if logical test evaluates to TRUE

value_if_false [optional] is returned if logical test evaluates to FALSE

The value_if_true / value_if_false parameters can contain numbers, cell references, or text (enclosed in quotes).

Examples

In the following example, cell C4 contains the function =IF(B4<20,15,0).

Remember: the IF function in Excel performs a logical comparison which evaluates to either true or false.

Here is a breakdown of how to use the IF function:

If the quantity in cell B4 is less than 20 (logical test), the function returns 15 (true), otherwise return a 0 (false).

IF function example
IF Function

Here is another example that uses the Excel IF function. The function =IF(B4<20, “Yes”, “No“) was entered in cell C4, then copied to the remaining cells in column C.

The IF function still evaluates to true or false.

The IF function returns “Yes” beside items where the quantity is less than 20 (true), otherwise return “No” next to items with a quantity of 20 or more (false).

Arguments containing text – “Yes” and “No” in the IF function – are always enclosed in quotes.

Simple worksheet containing items in a school store
IF Function with Text

Nested IF Functions

Do you need to evaluate different scenarios? The Excel IF function has you covered!

You can nest up to 64 IF functions.

A nested function is a formula where one function is contained entirely within another function. Understanding how this works with the IF function might be easier if we look at another flowchart.

The first part of the diagram (below) asks, “are there less than 10 items in stock?” If so, order 25 additional items. If there are more than 10 items in stock, we proceed to the next decision point which asks, “are there less than 20 items?” If so, order 15 additional items. Otherwise, do nothing.

Flow chart illustrating the process for a nested IF statement
Flow Chart for Nested IF Statement

Syntax

Do you remember the syntax for the Excel IF function? Here’s what it looks like, in case you need a refresher.

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

With a nested IF statement, the syntax would look like this:

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

Notice that the nested IF function is contained entirely within the value_if_false position for the outer IF statement. It is important that every opening bracket has a matching closing bracket when using nested functions.

Example

Let’s look at an example. In the spreadsheet below, I’ve entered the following formula in cell C4:

=IF(B4<10,25,IF(B4<20,15,0))

The first IF function says if the value in cell B4 is less than 10, display the number 25 in the order column otherwise evaluate the next IF statement (contained in the value_if_false position). The nested IF function says if the value in cell B4 is less than 20, display the number 15 in the order column – otherwise display 0.

Nested IF function
Nested IF Function

IF Functions and Cell References

In order to keep my examples as simple as possible, I’ve used numbers with IF statements. You can easily use cell references instead.

Here’s what I mean. In the example below, the IF function says if the value in cell B4 is less than or equal to the value in $A$11 then display the value from cell $B$11 in the order column – otherwise display 0.

References to $A$11 and $B$11 are absolute. As I copied the formula from cell C4 down to C5:C8, I didn’t want $A$11 or $B$11 to change. The dollar signs are needed to make sure those cells remain constant (absolute).

IF statement with absolute cell references
IF Statement with Cell References

Including the AND Function

What if you need to evaluate multiple conditions within an IF function? You can utilize the AND function, if two or more conditions need to be true. The AND statement returns TRUE if all arguments evaluate to TRUE, otherwise it returns FALSE.

Syntax

=AND(logical1, [logical2], …)

logical1 is the first condition to evaluate

logical2 [optional] is the second condition to evaluate

The AND function can contain up to 255 arguments, separated by commas.

Example

In the spreadsheet below, I’ve entered the following formula in cell D4:

=IF(AND(B4<=$A$11,C4=”Yes”),$B$11,0)

This IF function says if the quantity in cell B4 is less than or equal to the minimum specified in cell $A$11 AND the availability in cell C4 equals “Yes” then display the value from cell $B$11 in the Order column otherwise display 0.

Notice that both conditions must be true – the quantity in column B must be less than 20 AND the item has to be available – before an order can be placed.

IF statement incorporating the AND function
IF Statement Incorporating the AND Function

Including the OR Function

OK, last function… in this article anyway!

The OR function returns TRUE if any condition evaluates to TRUE, otherwise it returns FALSE.

Syntax

=OR(logical1, [logical2], …)

logical1 is the first condition to evaluate

logical2 [optional] is the second condition to evaluate

The OR function can contain up to 255 arguments, separated by commas.

Example

In the spreadsheet below, I’ve entered the following formula in cell D4:

=IF(OR(B4<=$A$11,C4=”Yes”),$B$11,0)

I made a small change to the spreadsheet. Column C indicates whether an item is on sale (available at a discount).

In this example, the IF function says if the quantity in cell B4 is less than or equal to the minimum specified in cell $A$11 OR the cell C4 equals “Yes” (discounted pricing) then display the value from cell $B$11 in the Order column – otherwise display 0.

Notice that either condition can be true – the quantity in column B is less than 20 OR the item is on sale – and an order can be placed.

IF statement incorporating the OR function
IF Statement Incorporating the OR Function

What’s Next?

Learning to use functions is a fantastic way to become more proficient in Excel.

If you’ve enjoyed reading about the IF function, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. XLOOKUP is quick and powerful tool form combining data from multiple sources.

See you next time!

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.