Master the Excel IF Function in 3 Easy Steps
The Excel IF function lets you incorporate “decision making” into your spreadsheets. Think about a restaurant offering take-out food. They would likely add a charge for any order being delivered to a customer’s home.
Now think about how that process might work. Imagine that there’s a checkbox on the form restaurant employees use to take orders. The box says “delivery requested”. If the box is checked (the condition is true), a $3.00 charge is added to the customer’s order. If the box is not checked (the condition is false), no additional charges are added.
This article will help you master the Excel IF function in 3 easy steps.
Step #1 – Understand the Syntax of the Excel IF Function
Although there aren’t any checkboxes in Excel’s IF function, it uses the same logic as the restaurant example presented above. Here is the syntax for the IF function:
Like every other function, the Excel IF function starts with an equals sign (=), followed by the function name and a series of arguments inside parentheses.
The Excel IF function accepts 3 arguments:
- logical_test – think of the logical test as a statement that you make about your data. Something like “the customer has requested delivery”.
- value_if_true – do this if logical_test is true. In the restaurant example, value_if_true amounts to “Add $3.00 to the customer’s order.”
- value_if_false – do this if logical_test is false. If the customer hasn’t requested delivery, no additional charge is added to the customer’s order.
Step #2 – Understand How to Enter the IF Function
Let’s work our way through a specific example. The spreadsheet (below) shows a variety of food orders, with a few of them being delivered to customers’ homes. To evaluate whether or not a delivery charge should be added to the order, we’ll use the IF function.
To enter the IF function, I’ll click in cell D4 and type =IF(C4=”YES”.
C4=”Yes” is the logical_test. Notice that I’m not asking a question. I’m not asking does C4 = “Yes”? I’m stating that C4 does =”Yes”.
Next I’ll type ,B4+$F$3.
B4+$F$3 is the value_if_true argument. It adds the $3.00 delivery charge to the food order, if logical_test is true. You should also note that $F$3 is an absolute cell reference. We’ll need to refer to this cell in each instance of the formula as we copy it to the other rows in the spreadsheet.
Lastly, I’ll type ,B4) and press ENTER.
B4 is the value_if_false argument. If logical_test is false, a delivery charge is not added to the order and the base cost of the order (in cell B4) is displayed in cell D4.
Once the function has been entered in cell D4, we can copy it to the remaining cells in D5:D8 using the fill handle.
Step #3 – Test for Multiple Conditions Using Nested Functions
What if the restaurant offered free delivery for orders over $10.00? We would need to test for multiple conditions by nesting Excel’s AND function inside the IF function. Check out the formula (below).
The AND function has been placed in the value_if_true position of our IF statement. Basically the function says “if C4=’Yes’ AND the cost of the order in cell B4 is less than $10, charge $3 for delivery. Otherwise, don’t add an extra charge”.
Now you know just enough about Excel’s IF function to be dangerous! Have you incorporated the Excel IF function into any of your spreadsheets? Let me know in the comments below!