IF Statements

Logical Tests Overview

IF Statements and other essential functions are covered extensively in our NYC Excel training classes. For those outside New York, find and compare the top Excel classes near you or Excel classes online.

Now, I'm going to start off by doing a quick review of the TRUE FALSE function. This forms the basis of the IF statement because the IF statement starts with a logical test, and the TRUE FALSE statement is that logical test.

For instance, someone might say that cell C5 is equal to 27. So I want to test that out with a TRUE FALSE statement. I will type in equal, I will select the cell C5, and I will check to see if it's equal to 27. Now when I press enter, Excel will give me a result of either true or false. That is true.

Someone disagrees and thinks cell C5 is equal to 29. So I'm not just going to take their word for it. I’m actually going to test this out, so I'll type equal, I'll select C5, and I'll check to see if it's equal to 29. Excel will render a verdict and tell me whether or not that's true or false, and Excel says that is false.

Now this is a very simple function. Someone could arguably say, well, I don't see why I would need to use that, because I can simply look at the sheet and tell that cell C5 is equal to 27.

That is true. But let's say you wanted to compare several values.

If I go over to columns M, N and O, I might want to check to see if one value is equal to each other if I'm comparing two lists. Now for the first value, it's not a mystery. They are exactly the same.

But if I don't want to compare each and every value down several rows, I can simply use the autofill handle here and quickly see which values are true and which ones are false. Then I may be able to filter them and get a subset of that information.

Not only can you check to see if cell references are equal to each other, you can also check to see if one value is greater than another. So I’ll backspace here, and I want to check to see if M3 is greater than N3. I'll press enter.

Again, the obvious answer is false. But take a look at what happens when I autofill this down. Where the values in List 1 are greater than the values in List 2, that easily stands out for me to see because I see true.

What other kinds of comparisons can you make besides equal to and greater than? You can also check to see if values are less than, greater than or equal to, less than or equal to, or not equal to.

The IF Statement

So how does this work with the IF statement?

Well, I want to check to see if something is true or false, and based on that result, I can enter a value if true or a value if false. So I can just enter a value. I can also enter a calculation if I wanted to. That's taking it a step further.

Exercise 1

Let's take a look at our first exercise, exercise one.

Someone wants to find out if these employees are working overtime. There are three employees, but maybe we have 3,000 employees. We can create a simple IF function to check the hours and see if those hours are overtime or not.

So we'll start out with equal IF. Now, what I'm comparing are the hours, so I'll start by selecting Joe's hours. I want to see if those hours are greater than 40. If they are greater than 40, then the answer to the question “Overtime?” is yes. Again, if you're using text, you'll always use double quotes inside of a function when you're working in Excel.

Now, if that value is not greater than 40, then the answer to the question “Overtime?” is no. I'll press enter, and now I can double-click on the autofill handle and check everybody's status, and I can see Ruth is the only employee in this table that is working overtime.

An example that we would have our students take on is one where we might want to check the meeting attendance of 10 people at a staff meeting. The way I can tell someone was present is if I look at their present column, if they have a Y in their status, then that means they were present, and if there is not a Y there, that means they're absent because the only other possible option is N.

I'll type in equal, IF this value to the left is equal to the letter Y, then I'm going to say that person was present. Now if that value is not equal to Y, then I'm going to say that person was absent. I’ll press enter, I get my result, I'll just double-click, and there you go.

The nice thing about this is if the values ever change for the value that's being compared, the IF statement automatically updates. So if Sue actually didn't attend the staff meeting, if I just change her present status from Y to N, the IF statement automatically updates. So you write the formula once, and then it continues to work for you after the fact.

IF ERROR Example

We'll take a look at one more type of IF statement. This is called IF ERROR.

IF ERROR is perfect for a situation where you have a formula that sometimes returns a correct value and sometimes returns an error message.

Now, the problem is not the formula, because on some occasions it does return a value. It's just that in certain situations, maybe there are no values to calculate. That's not necessarily a fault of the formula, but there is no data to calculate. Therefore, the formula will return an error message.

Instead of seeing an error message, what I might like to do is create an IF ERROR function that checks to see if there is a value or if there is an error. Now, if there's an error, I can have a substitute value or placeholder in place of the error message.

First thing we need to do is be very clear about what the formula is. So here D53 divided by E53. I'm going to go into the cell over to the right. I'll type equal, and I'll say IF ERROR.

Now this is why I need to be clear about the formula. I'm going to select cell D53, and I'm going to divide it by E53. If that gives me a value, fine.

Now this is where I interrupt and say, if I'm about to get an error message, show me something else. The something else I would like to see is nothing. So I'll enter in two double quotes, close parentheses and press enter. I see nothing now. I'll autofill down. But I do see something when the formula is about to give me a value.

That's how you can use IF ERROR to make your work look neater.

If I wanted to improve upon this, here's another option. Maybe I want to be a little bit more descriptive about why we're not seeing anything in that cell. I can simply type “no values” because that's the situation. There are no values for me to divide. So if I press enter, this is a little bit more descriptive. It also accounts for the error and is a lot easier for someone to look at.

Recap

So those are IF statements and also the IF ERROR statement in situations where you might get an error.