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.
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.
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).
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.
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.
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.
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).
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.
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.
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!
