Would you like to make your spreadsheets smarter? Excel’s IF function is an easy way to add logic and decision-making power. Learn how to use this versatile function to calculate discounts, allowances, or commissions based on different conditions or values in your data. This step-by-step guide includes a downloadable practice file.
Knowledge You’ll Gain:
- Understand the core concepts of the IF function and how it works in Excel.
- Confidently build basic IF formulas to make logical decisions in your spreadsheets.
- Apply the IF function to various scenarios, such as calculating allowances or partial payments
- Use the IF function with different data types, including text, numbers, and dates.
- Download and use the practice file to reinforce your learning.
Understanding the Excel IF Function
The IF function is one of the most straightforward and useful logical functions. It can fill cell items based on evaluating a condition, such as a cell’s content and logical operators. What’s appealing about the function is that it can be used with other functions to handle more complex scenarios. You might think of it as a formula building block, and you can find it in the Logical category.
The wizard-like dialog allows you to fill 3 Function Arguments or data elements, but you could use the formula bar once you master it. This is the easiest way to learn an Excel formula because you can see if it returns your expected result. For example, a line at the bottom left of the dialog reads “Formula result =.”
IF Function Syntax and Arguments
| Field | Definition |
|---|---|
| Logical_test | A test on a cell value that is either TRUE or FALSE. |
| Value_if_true | The value Excel will put in a cell if the test is true. |
| Value_if_false | The value Excel will put in a cell if the test fails. |
Despite not having Microsoft Excel, my parents routinely employed this type of IF logic when calculating my allowance. Their version read:
IF you empty the garbage, mow the lawn, wash the dishes, AND walk the dog, you get your total allowance.
And since I grew up in New England, this logic would change with the seasons to account for leaves and snow.
Constructing Your First IF Function
Although Excel can’t issue an allowance, it can calculate the correct amount using a logic test based on whether a cell met a formula condition.
For example, I could create a spreadsheet with the Task needed to get an allowance. A value would be applied toward the allowance if the Tasks were completed (TRUE situation). Nothing would be added if the Task wasn’t completed (FALSE situation).
These examples are noted by blue square labels [1] and [2] in the image below.
Using the example above, you might express the logic in the following way:
IF cell B2 = Y, then use the Rate value from cell C2 ($3.00) in D2.
IF cell B2 ≠ Y, then place $0.00 in cell D2.
As you can see in this example, the IF logical condition is TRUE or FALSE. And it pays to take out the garbage.
IF Function: Comparison Operators & Syntax
To help evaluate conditions, Excel uses a list of familiar operators. You probably know these as we probably used them in math class. These operators will be evaluated as logical “true” or “false”. In the table below, B2 and C2 in the Example column are cell references.
| Operator | Example |
| = (equals) | B2 = “YES” |
| < (less than) | B2 < 12 |
| > (greater than) | B2 > 112 |
| <= (less than or equal to) | B2 <= 12 |
| >= (greater than or equal to) | B2 >= 12 |
| <> (not equal to) | B2 <> C2 |
Entering IF Function Arguments (Step-by-Step)
- Click the spreadsheet cell where you wish to use the Excel formula.
- From the Formulas tab, click Insert function…
- In the Insert Function dialog text box, type “if“.
On Office 365, there is now a Logical button on the Formulas tab. You can select IF from the drop-down menu.
- Make sure your cursor is in the Logical_test text box.
- Click the spreadsheet cell you wish to evaluate. Excel will fill in the cell reference such as “B2”.
- Add the equals sign = and your desired value in quotes. For example =”Y”.
- In the Value_if_true field, type the value you want to be entered in your cell if B2 equals “Y”. In our example, I’ll click cell C3.
- In the Value_if_false: field, enter the value the cell should have if B2 does not have a “Y”. I’ll enter 0. I could leave it blank, but the cell would show “FALSE”.
- Review the dialog to see if the Formula result= value (label [1] below) is what you expect. If not, check to see if any errors show to the right of the fields (label [2] below).
- Click OK.
- Copy the formula to the other cells in your column.
Even though the Value_if_false field is optional, providing a value is best. Otherwise, Excel will use FALSE in the cell value.
IF Function With Numeric Values (Formula Example)
The above spreadsheet might have been Version 1 for my parents. A new incentive program would appear based on some parent/child negotiations and competitive neighborhood rates. I probably would’ve requested pay for partial chores. No doubt, my parents would counter with a penalty clause if something was less than half done.
Excel is flexible when it comes to IF statements and can evaluate more than a simple “Y” or “N.” For example, if we convert our previous Done? column to a % Done column with a number, we can accommodate these new requirements such as:
=IF(B2>0.5,B2*C2,-C2)
The new formula returns the allowance based on the % Done in Column B. If the chore completion number is greater than .5, a prorated amount is applied to the allowance.
A negative amount was applied to the allowance if the chore completion rate was .5 or below. Loosely translated, an “incomplete” performance costs money. You could also apply colors using conditional formatting.
Troubleshooting Excel IF Formula Errors
The IF function doesn’t always work as expected for several reasons.
- You don’t have quotes around a text string. For example, you used B2=Y instead of B2=”Y”. The quotes aren’t needed if you use the values TRUE or FALSE as Excel recognizes them.
- You have a data type mismatch. For example, your comparison operator was comparing a number in one cell, such as “twelve”, but the comparison cell had 12. However, Excel is forgiving if you evaluate number formats such as 1.5 versus $1.50.
As you’ve seen, this is a versatile and foundational function. Once you know how to use the IF function in Excel, you’ll use it in more scenarios. The two examples presented here were foundational. However, you can use IF functions to handle other transactions, such as sales tax, stock values, shipping charges, or fixing Excel DIV 0 errors.
Download the practice file below and experiment with the IF function using your data. With a little practice, you’ll be able to confidently apply this powerful tool in various scenarios and unlock the full potential of your spreadsheets. And if you have kids, why not let them build the Excel spreadsheet and give them a bonus for using the function.