Everyone knows how versatile the IF statement is in a scripted program, but did you know you can use much of the same logic inside of a cell in Excel?
A basic definition of an IF statement in a program is that it lets you output something specific based on the results of several inputs. You could perform entirely different calculations based on the output of some other calculation. You could perform conditional formatting. You can even base your output on string searches of input cells.
If this sounds complicated, don’t worry. Let’s take a look at a few creative ways you can use IF statements in Excel.
What Is an IF Statement in Excel?
When most people think of using an IF statement in Excel, they think of VBA. This is because an IF statement is usually logic that’s used in the world of programming. However, you can use this same programming logic right inside the spreadsheet cell itself.
When you type “=IF(” in the cell, you’ll see what the syntax of your IF statement needs to look like to function correctly. The basic requirement is just a “logical test”. By default the output to the cell will be TRUE or FALSE, but you can customize that by including additional parameters in the function.
How a Basic IF Function Works
First, let’s take a look at a basic IF function. In the example spreadsheet above, I have four activities that I log related to my car. I record the date when any of four events happen: an oil change, car repair, registration, or insurance renewal.
Let’s say if the “Repaired” column contains a “YES”, then I want the Event Type to have “REPAIR”. Otherwise it should be “NON-REPAIR”. The logic for this IF statement is very simple:
Filling the entire column with this formula returns the following results:
This is useful logic, but in this particular case it doesn’t really make much sense. All someone has to do is look at the “Repaired” column to identify whether or not that date involved a repair.
So, let’s explore some more advanced IF function statements to see if we can make this column a little more useful.
AND and IF Statements
Just like in a regular program, sometimes in order to examine two or three conditions that depend on one another, you need to use AND logic. The same is true here.
Let’s define two new event types: Planned, or Unplanned.
For this example, we’re going to focus on just the Oil Change column. I know that I usually schedule my oil changes on the 2nd day of every month. Any oil change that isn’t on the second day of the month was an unplanned oil change.
To identify these, we need to use AND logic like this:
The results look like this:
This works great, but as you can see there’s a slight logical flaw. It works for showing when oil changes occur on expected dates — those turn up as “Planned”. But when the Oil Change column is blank, the output should also be blank. It doesn’t make sense to return a result in those cases because no oil change ever took place.
To accomplish this, we’ll move on to the next advanced IF function lesson: nested IF statements.
Nested IF Statements
Building upon the last function, you’ll need to add another IF statement inside the original IF statement. This should return a blank if the original Oil Change cell is blank.
Here’s what that statement looks like:
Now the statement is starting to look a bit complex, but it really isn’t if you look closely. The first IF statement checks if the cell in the B column is blank. If it is, then it returns a blank, or “”.
If it isn’t blank, then you insert the same IF statement we used in the section above, into the False part of the first IF statement. This way, you’re only checking for and writing results about the date of the oil change when there was an actual oil change that took place. Otherwise, the cell is blank.
As you can imagine, this could get terribly complex. So when you’re nesting IF statements, always take it one step at a time. Test individual IF statement logic before you start nesting them together. Because, once you have a few of these nested, troubleshooting them can become a real nightmare.
Now we’re going to kick up the logic just a notch. Let’s say that this time what I want to do is return “Yearly Maintenance” if an oil change or repair combined with registration or insurance is done at the same time, but just “Routine Maintenance” if just an oil change was done. It sounds complicated, but with the right IF statement logic it isn’t hard at all.
This kind of logic requires the combination of both a nested IF statement and a couple of OR statements. Here’s what that statement will look like:
=IF(OR(B2="YES",C2="YES"),IF(OR(D2="YES",E2="YES"),"Yearly Maintenance","Routine Maintenance"),"")
Here’s what that the results look like:
It’s remarkable the kind of complex analysis you can perform just by combining various logical operators inside of nested IF statements.
Results Based on Value Ranges
It’s often very useful to convert value ranges into some kind of text result. This could be as simple as converting a temperature from 0 to 50 degrees F into “Cold”, 50 to 80 as “Warm”, and anything over 80 as hot.
Teachers probably have the most need for this logic because of letter scores. In the following example we’re going to explore how to convert a numeric value to text based on just such a range.
Let’s say a teacher uses the following ranges to determine letter grade:
- 90 to 100 is an A
- 80 to 90 is a B
- 70 to 80 is a C
- 60 to 70 is a D
- Under 60 is an F
Here’s how that kind of multi-nested-IF statement will look:
Each nest is the next range in the series. You just need to be very careful to close out the statement with the right number of parenthesis or the function won’t work correctly.
Here’s what the resulting sheet looks like:
As you can see, this allows you to represent any number in the form of a descriptive string. It will also update automatically if the numeric value on the sheet ever changes.
Using IF-THEN Logic Is Powerful
As a programmer, you already know the power of IF statements. They let you automate logical analysis into any calculation. This is very powerful in a scripting language, but as you can see it’s just as powerful within the cells of an Excel spreadsheet.
With a little bit of creativity you can do some very impressive things with IF statement logic and other formulas in Excel.
What kind of unique logic have you come up with using IF statements in Excel? Share your own ideas and tips in the comments section below!