Logical functions
There is a matching Excel file that you can use to follow along and practice, which you can download from here
IF statements are one of several logical functions that are available in Excel. It’s an extremely powerful tool for a variety of tasks, most notably for assigning categories to your data based on certain criteria and for some data cleanup functions that require looking for patterns. Essentially they allow you to do one thing if your criteria is true, and another thing if your criteria is false. Later, we’ll talk about nested IF functions that allow you to use multiple criteria.
A basic IF statement consists of:
1) What we’re going to measure as being either true or false
2) What to do if it’s true
3) What to do if it’s false
=IF(criteria, true, false)
Basic IF()
Use “BasicIF” worksheet
This has salary data from the St. Paul police department. The chief has just announced that everyone is getting a 1% raise, but all will get a minimum raise of $350 (if 1% of their salary is less than $350).
For the story, I want to figure out how much additional money this is going to mean (the total of the “raise” column) based on the current workforce, and then generate a new salary for each individual.
The crux of our formula is this: If 1% of the person’s salary is less than $350, then the amount of their raise will be $350. If not, then it will be 1% of their salary.
Here's our formula. The part in italics is the "criteria" portion -- it is calculating what 1% of the salary would be and asking if it's greater than 350. The part in bold is the "true" portion -- it's calculating what the raise would be. And the last part, just the 350 standing by itself, is telling it to drop the number 350 in this cell if 1% of the salary is less than or equal to 350.
=IF(F6*0.01>350, F6*0.01, 350)
Nested if()
Use the NestedIF worksheet
This data on NFL games shows us the scores for the home team and the visit team, but it doesn’t tell us who won the game.
Since it’s possible for an NFL game to end in a tie (and several did in 2018), let’s first look at what we’d need to do to figure out if a game ended in a tie. Then we’ll put that together with another IF statement to create a new field that tells us which team won the game, or if it ended in a tie.
Let’s just look at the formula we’d need to figure out a tie.
=IF(f2=g2, “tie”, “not tie”)
The criteria portion is simply – “Is the home score equal to the visit score?”
Then the true portion tells it to put the word “tie” in the new field.
And the false portion says to put the words “not tie” in the new field.
But we ultimately want a single field that either says “tie” (for the games that tied), or it has the name of the winning team.
To get that, we need to “nest” two IF statements together.
First, we need to determine if the game ended in a tie. If that’s true, it will put the word “tie” in our new field.
If it’s false, Excel will evaluate our second IF statement – if the home score is greater than the visit score, then grab the name of the home team; if not, then grab the name of the visit team.
In other words, the second IF statement is the “false” portion of our first IF statement.
Here’s what it looks like….
=IF(f2=g2, “tie”, if( f2>g2 , d2, e2) )
Let’s do one more nested IF statement on this worksheet. I want a column indicating whether the home team won the game, the visit team won the game or that it ended in a tie.
So our new column will have either “home”, “visit” or “tie” as the values.
=IF(f2=g2, "tie", if(f2>g2, "home", "visit"))
IF() to fill in blanks
use worksheet called “Copy down”
I use this quite frequently when I get data that looks more like a report, listing something on one row (in this case, a team name) and then the things that fall under it (i.e. the players) on subsequent rows. For data analysis purposes, we need every row to identify which group it falls into.
The trick is that you need to have a pattern to follow. In this example, the pattern is that the B column is always blank on the lines where the team name is listed. And it’s not blank anywhere else.
So this formula is going to look to see if the B cell is blank:
=IF(b2= “”, a2,c2)
Then it’s going to put the contents of A2 (in this case, “Arizona Cardinals”) in the field if it finds it to be true. If it’s not true, it looks to the cell directly above (c2) to essentially copy down the team name.
Re-arranging data with if()
Use worksheet called “crime”
One of the most common situations where I use IF statements is to rearrange data that comes to me in a “report” fashion or has some other problem that makes it difficult or impossible to do even simple things like sort or PivotTables. This tutorial will show you how to use IF() functions to get it into shape.
Combining other functions:
Now that you know the basics of an IF statement, you can jazz it up with all kinds of other functions. You just place the function as either the criteria, the true part or the false part. Of course, you can use multiple functions in the same IF statement if necessary.
Examples (I’ve just made these up!):
If a date (located in b2) is for a Monday, then put the word Monday in a new cell, otherwise do nothing:
=if(weekday(b2)=2, “Monday”, “”)
If a date (located in b2) is equal to another date (located in c2), then put the word “Same” in the new field, otherwise calculate the difference in months:
=if(b2>c2, “Same”, datedif(b2,c2, “m”))
Using a wildcard search:
You can use the SEARCH function to look for a word or symbol contained within other text, however it gets a little tricky to make it work properly. You have to add the ISERROR function. If you want to get in this deep, I recommend checking out the help file on these functions. Otherwise, here’s a quick hit to get you started:
This example assumes you have a list of cities and states and you want to flag all of the ones that are in Texas. In this case, the state name is written out in full.
So if it finds Texas, this formula instructs it to put an X in the C column, otherwise leave it blank.
=IF(ISERROR(SEARCH("*Texas*",B4,1)>0)=FALSE, "X","")
The criteria part of this stretches from the ISERROR all the way to the FALSE. The ISERROR is necessary because it will give you an error message if it doesn’t find the word. It’s the only way you can instruct the computer to do something in the false portion of your answer (even if that means just leaving it blank).
The following portion:
SEARCH(“*Texas*”, b4, 1)
If used alone, this portion will return a 1 if it finds the search term and an error message if it doesn’t. So then you need to add the IF portion to give it two options. By adding the ISERROR and the =FALSE, you can sidestep the error message.