Excel Nested IF Statements

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

IF Statement 

Now, I'll start with a very brief description of the IF statement, and then we'll take a look at an exercise where we'll work with multiple revenues and tax rates. So let's start. 

I'm going to type the number 1 in this cell, and then over to the right, I'm going to type an IF statement that's going to check the value of that cell. I want to see if G6 is equal to 1. If it's equal to one, then I will put in the result or the status "One" in that cell. 

So now if it's not equal to 1, then there's something I can definitely say about that cell, and I'm just going to say "Not One," close parenthesis, I'll press enter. No surprise. 

If I go over to that cell and change it to 2, we'll get "Not One." If I change it to 500, we'll also get "Not One." We'll only get "One" when we type 1. No big deal. 

Nested IF Statement 

Here is a nested IF statement. I want to figure out if that cell is equal to 2, not just 1. So if it's equal to 2, I would like it to say 2. 

So now I have three possibilities, either it's equal to 1, either it's equal to 2 or it's equal to something else. 

So I'm going to backspace over the false result because what I know to be true is that if G6 is equal to 1, we're gonna see 1. What I want to do is interrupt what's going to happen if it's not equal to 1. So at this point, and at the point where it's not equal to 1, I'm going to write another IF statement. I'm going to say if G6 is equal to 2, then I will say 2. 

So I'm going to continue to do what works. If it ain't broke, don't fix it. I'll enter a comma. Now, what if it's not equal to 2? So now we have two situations. If it's equal to 1, it'll say "One." If it's equal to 2, it'll say "Two." But now I need to account for the third situation, what if it's not equal to either? 

Well, this is where I can say or type in the result for false. So value of false is going to be, and what I can definitely say about that value, if it's not equal to 1 and it's not equal to 2, is "Not 2 or 2." I'll press enter. Not yet. I need to put in one more closing parenthesis. If I take a look, I see that the second IF statement has an opening and closing parenthesis, but I did not put in a closing parenthesis for the first IF. So don't forget to do that. You should see a black parenthesis at the end, not red. Now you're complete and you can press enter. 

1. Now we have 1. I'm curious to see what we get if we type in 2. OK, that's two possibilities. Let me check to see if I enter anything else. It works. 

So that's, in a nutshell, the nested IF statement. Let's take a look at a practical example. 

Example 1 

So here I want to tax someone who has revenue less than 500. I want to tax them at 5 percent. I want to tax anyone who has between 500 and 1,000, 10 percent, and over 1,000, 20 percent. 

So here I'll write the IF statement. Equal, IF this value is less than 500, comma, then I'm going to take that amount and multiply it by 5 percent. Now I need to lock that because I'm going to be auto-filling this down, and I don't want 5 percent to go to 10 percent, go to 20 percent, and eventually go to blank cells, so I'll press F4. 

Then I'll enter a comma, and I'm going to check for the next possibility. So if it's not true that it's less than 500, then I want to check to see if that amount is less than or equal to 1,000. Now, I know we're looking for a value between 500 and 1,000, but the way the IF statements work, they work based on hierarchy. So anything less than $500 will go through that funnel, and then anything that's left over, which is anything that's 500 or now less than or equal to 1,000, that will be caught in the second funnel. 

So I'm going to say if it's less than or equal to 1,000, I'm going to take that original amount and I'm going to multiply it by 10 percent, F4, comma. Just when you think it gets more difficult, it becomes really easy. 

If it's not less than 500, and it's not less than or equal to 1,000, then the only possibility is that it's over 1,000. In that case, I'll just take that amount and multiply it by 20 percent. I don't even need another IF statement. I'll press F4 to lock, and then I'll put in closed parenthesis, and that covers the second IF statement, and then I'll put in the last closing parenthesis and that covers the first IF statement. I'll press enter, and I get $5, and autofill this down, and I want to verify my information here. So this amount is $100. According to the IF statement, he should have multiplied it by 5 percent, so let me do that and see if I got $5. I did. 

Now this amount is between 500 and 1,000. Let me multiply that by 10 percent. Let me see if the IF statement got that right. Let me do that again. Equal, so $800, not $80, so there we go. I'm going to multiply that by 10 percent. I'll press enter. The IF statement got that right. Now, finally, $2,000 is definitely over 1,000. So that should have been multiplied by 20 percent. Let me see if the IF statement got that right. It did. So the IF statement was able to account for all three possibilities. 

Example 2

Now, the exercise we would offer to students in class is to determine the status of cases in stock depending on their amounts. So if we have between 0 and 3, then cases are considered low. If we have between 4 to 9, cases are considered OK, and if it's 10 or more, that's overstock. 

So I'll type in equal, IF this value is less than 4 — that's my shorthand for saying 0 to 3 — then the cases in stock are low, comma, otherwise, IF, and that's my second IF statement, that value is less than 10, then I'm going to say we're OK. We don't need to get anything. Now, the only other possibility is overstock, so I'll just enter a comma and just type "overstock," double quote, close parenthesis, close parenthesis, press enter, autofill down, and I get the statuses from my cases in stock. 

Now, these exercises could be a little difficult at first to wrap your head around, so please notice that if you go over to the appropriate row, you'll see a little plus sign. If you click that, there will be a hidden row that contains the answer so you can verify to see if what you wrote matches the answer in the hidden row. This also applies for the second exercise. 

Recap

So that is working with nested IF statements. You'll use nested IF statements to apply more than one logical test and therefore allow for an additional possibility, such as yes, no, or maybe in this case, OK, low and overstock.