Search:
Advanced search
|
Browse by category:
|
Contact Us |
How to Use Simple IF Statements in Excel |
|||||
How to Use Simple IF Statements in Excel.How Does an Excel IF Statement Work?There are three key parts to a successful IF statement:
An Excel IF statement begins with =IF( .The official Excel documentation shows the structure of an Excel IF statement:
In plain English, here's how I think of using an IF Statement:
Simple enough? If this still seems a bit vague, let's move onto our very first example of how to use an IF statement, to see how it works. Writing Your First IF StatementFor this example, use the tab titled "Check for Blank" in the example workbook. We'll start off with the simplest possible IF statement example. In this case, we're just going to check to see if a cell is blank and print a message. Let's say that we're creating a sign up list for our office holiday party. We need to write a formula to determine who to remind to sign up for a dish. At the beginning, our data is a simple list of names and what they've signed up to bring: Even though I can see that some cells are blank, let's write an IF formula to print out who we need to remind in column D. My IF formula should check if the cells in column C are blank, and print a reminder to the person if they've not signed up yet. Here's what I wrote:
Let's dissect this formula in four parts:
And finally, here's the results when we finish the formula and pull it down: After writing the IF statement and pulling it down, each cell in column D outputs the correct value. Perfect! Notice that when the adjacent cell in column C is blank, Excel prints that we need to remind that person to sign up. We could filter for Column D now and then send them a reminder via email. This is a simple example for how we can use if statement formulas in Excel to give our spreadsheets some logic. Let's look at some others. Testing for ValuesUse the Test Values tab in the example workbook for this exercise. So far, we used an IF statement to see if a cell contained no text. Now, let's use an IF statement to test for a numerical value. In the first example, we checked to see if the cells in column C contained a specific bit of text. Notice that we used the equals sign to see if a cell was equal to a blank. We can use any of the math operators to test for values, such as:
Let's say that we need to take inventory of our warehouse. We sell our product in batches of 10, so every batch we count should have exactly that amount. Let's write a formula to check for it. In the data below, I've gone out and taken stock of products in my warehouse. I need to write a formula in column E to check and make sure that we have exactly 10 of each product. We should print a warning message if we don't have 10 of each item. In column E, here's the formula that I'll write:
Let's dissect the formula:
Needs attention follow-up action result. In the screenshot above, you'll see how I used the formula to add warning messages for each of my inventory counts. If the value in column D isn't exactly 10, Excel will print "needs attention" in column E. How to Use IFERRORThere's a special IF statement formula built into Excel for fixing errors in your formulas. The IFERROR formula is similar to an IF statement, but catches a wide array of errors and replaces them with more useful data. According to Microsoft's documentation, here are errors in your spreadsheet that IFERROR can catch and revise:
To use IFERROR, bracket an existing formula with an IFERROR formula, and then provide a fallback value. This is how I think of using IFERROR:
Let's walk through an extremely useful case for IFERROR. An IFERROR ExampleUse the IFERROR tab in the example workbook for this section. One place that you'll run into errors is with the VLOOKUP formula. If you're using a VLOOKUP to match values, there's a chance that your lookup table won't contain a match. When that happens, you're going to get an "N/A" error in your spreadsheets. In the spreadsheet below, I've got a list of freelancers working for me on a job. I've got their company listed, and want to know what each person is doing. For most of the freelancers, I have a lookup table to the right that's pulling in their roles. This spreadsheet has some N/A errors because my lookup table is missing matches. I've already written a VLOOKUP to pull in the job role, which matches the companies in column B with my lookup table:
Here's the problem: several of the companies aren't in my lookup table, which is why we have these nasty N/A errors. Let's replace them with something more elegant. The IFERROR that we'll want to write will follow this structure:
In my case, I'm going to replace the errors with a reminder to contact my HR manager to check what the freelancer is working on. Here's my proposed formula:
This formula looks complex, but just remember: all that we're doing is bracketing an existing VLOOKUP with a replacement value. We simply surround our current formula with an IFERROR and give Excel a fallback value. I've replaced the N/A errors with a much more useful and readable "Contact HR" text. In short, IFERROR is a special IF statement that Microsoft designed for our convenience. It automatically catches formula errors and outputs a replacement value. Recap and Keep LearningIF statements are a method to add conditional logic to your spreadsheets. In this tutorial, you learned to use the formula and some possible uses for it in your spreadsheets. No matter how long I use Excel, I keep finding out that I have more to learn. If you want to keep learning how spreadsheets can be used, here are some follow-up tutorials:
If you have an Excel IF statement issue, let me know in the comments. Or, if you have ideas on how to use these formulas to share with other readers, the comments are open. Cr. business.tutsplus.com/tutorials/how-to-use-simple-if-statements-in-excel--cms-27819 |