Master the logic of Excel's nested if statements to efficiently handle multiple conditional outcomes. Learn through practical examples like assigning tax rates and inventory status based on specific criteria.
Key Insights
- Nested if statements enable users to test multiple conditions sequentially, demonstrated through creating logical checks for cell values (one, two, or neither) and returning appropriate descriptors ("one," "two," or "not one or two").
- Applied examples illustrate using nested if statements practically, such as calculating tax rates: revenues under $500 taxed at 5%, $500–$1,000 at 10%, and amounts over $1,000 at 20%.
- Another practical application includes categorizing inventory levels using nested if statements: low stock for 0–3 cases, adequate stock for 4–9 cases, and overstock for 10 or more cases.
This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
Nested IF statements. In this section, we're going to cover how you can nest multiple IF statements together to account for more than just two possibilities. 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 one 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 one.
If it's equal to one, then I will put in the result or the status "one" in that cell. Now, if it's not equal to one, then there's something I can definitely say about that cell, and I'm just going to say "not one." Close parentheses, I'll press ENTER.
No surprise. If I go over to that cell and change it to two, 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 one. No big deal. Here is a nested IF statement.
I want to figure out if that cell is equal to two, not just one. If it's equal to two, I would like it to say "two." Now I have three possibilities.
Either it's equal to one, equal to two, or 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 one, we're going to say "one." What I want to do is interrupt what's going to happen if it's not equal to one.
So at this point, and at the point where it's not equal to one, I'm going to write another IF statement. I'm going to say if G6 is equal to two, then I will say "two." 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 two? Now we have two situations.
If it's equal to one, it'll say "one." If it's equal to two, 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 one, and it's not equal to two, is "not one or two." I'll press ENTER.
Not yet. I need to put in one more closing parentheses. If I take a look, I see that the second IF statement has an opening and closing parentheses, but I did not put in a closing parentheses for the first IF.
So don't forget to do that. You should see a black parentheses at the end, not red. Now you're complete, and you can press ENTER.
Now we have one. I'm curious to see what we get if we type in two. Okay, 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. So here, I want to tax someone who has revenue less than 500, comma, I want to tax them at 5%.
I want to tax anyone who has between 500 and 1,000, 10%, and over 1,000, 20%. 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%.
Now I need to lock that because I'm going to be AutoFilling this down, and I don't want 5% to go to 10%, to go to 20%, 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.
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%. 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%.
I don't even need another IF statement. I'll press F4 to lock, and then I'll put in a close parentheses, and that covers the second IF statement. And then I'll put in the last closing parentheses, and that covers the first IF statement.
I'll press ENTER and I get $5. I'm going to AutoFill this down, and I want to verify my information here. So this amount is $100.
According to the IF statement, you should have multiplied it by 5%. 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%. Let me see if the IF statement got that right.
Let me do that again. Equal. So $800, comma, not $80.
So there we go. And I'm going to multiply that by 10%. 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%.
Let me see if the IF statement got that right. It did. So the IF statement was able to account for all three possibilities.
Now, the exercise we would offer to students in class is to determine the status of cases in stock depending on their amounts. If we have between 0 and 3, then cases are considered low. If we have between 4 and 9, cases are considered okay.
And if it's 10 or more, that's overstock. So I'll type 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 okay.
Now, the only other possibility is overstock. So I'll just enter a comma and just type overstock, double quote, close parentheses, close parentheses, press ENTER, AutoFill down.
And I get the statuses (status values) for my cases in stock. Exercises like this can be difficult at first, 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 containing the answer. You can verify if your answer matches. This also applies to the second exercise.
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, okay, low, and overstock.
