Categories
Microsoft Excel

How to Use the IF Function in Excel

The IF function lets you 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.

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

This flowchart illustrates the typical process used by the IF function. 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.

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

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

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). It can be translated like this: If the quantity in cell B4 is less than 20, display 15 in the Order column, otherwise display a 0.

IF function example
IF Function

Here is another example. The formula =IF(B4<20, “Yes”, “No“) was entered in cell C4, then copied to the remaining cells in column C. The IF statement displays “Yes” beside items where the quantity is less than 20 and “No” next to items with a quantity of 20 or more. Notice that arguments containing text – “Yes” and “No” in the IF statement – are 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 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 statement 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 IF function? Here’s what it looks like, if 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

Microsoft Excel is a registered trademark of Microsoft Corporation. onsite-training.com contains articles and educational content related to Microsoft Excel but is not affiliated with Microsoft in any way.

By Michael Belfry

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