Nested IF: IF used multiple times in ONE formula
Before you dig in to this article, make sure you are familiar with the Excel IF Statement.
What does it do?
As per the heading, we’ll refer to this as NESTED IF from here onwards. In brief summary, the Excel NESTED IF function performs a user instructed logical test, if that test is TRUE, it performs an action, if its FALSE, it does another action. Learning how to nest IFs will open up your spreadsheet to an advanced level and rally expand your horizons!
Syntax of this function explained
This is one of the most useful in Excel. When harnessed and nested (using multiple IF functions in one), you can have one very powerful multi levelled formula in ONE cell that performs an astonishing number of logical tests.
As per Excel, it defines this part of the syntax as “any value or expression that can be evaluated to TRUE or FALSE.”. This is where you make your test of a cell, see below under Examples for more detail.
As per Excel, it defines this as “the value that’s returned if Logical_test is TRUE, if omitted, TRUE is returned. You can nest up to seven IF functions”
There is a limit of 7 nested functions, but only up to Excel 2007. Since then, it’s been increased to 64 IF statements in ONE formula. I’ve yet to get there! If you start getting into 15 levels and above you might want to revisit your methods in how you create the logical tests.
As per Excel, it defines this as “the value that’s returned if Logical_test is FALSE, if omitted, FALSE is returned.
It’s here where NESTING typically begins and we’ll make this clear by looking at a simple example and visualising how the process tree works when following it through.
Ok, let’s start get started with an example
Basic NESTED IF
Before we begin, let’s look at how the process flow works below. On the top, we have the single levelled IF compared to the multi levelled on the bottom. Each time a FALSE is found we can perform ANOTHER logical test.
Write the NESTED IF function in cell C3 to test cell B3 to see if it finds a TRUE result for one of the logical tests, the function will perform each logical test in order from left to right, if it is TRUE, then displays the text for that logical test, if it FALSE, then perform the next logical test until the very end.
Remember, when asking a function to display text, you must enclose the text with quotation marks.
Try replicating the below in stages. Small steps first.
- Start typing out as per below, when you get to the value_if_FALSE, excel will lend a hand and show a comments box to prompt you for the next part, shown in bold below
2. This is where you start to NEST the IFs. IF B3 is 1 then show ’Very Poor’, if not, then do another IF, you’ll notice the comment box has jumped along, in this linear format Excel will align the comments box with the IF statement you are currently in. This is a great point to note when trying to fix faulty formulas and gives you some direction when building any formula.
3. Continue this pattern until the very end, making sure to add the add another permutation as the final FALSE value and noting that the comments box has moved again. We’ve now reached the end of the permutations. All is left is to close the formula off with a parenthesis.
4. Where there’s open brackets, you need to close them all off, so let’s get started with one. As you add one, you’ll notice it changes colour and matches it to the next open parenthesis immediately left of it. Excel will colour code each parenthesis to help you spot errors, another helpful hand.
5. As you add each closed parenthesis, the comment box will jump back all the way until you can close no more, so keep adding them until the yellow comment box disappears
6. All done!
Getting More Advanced with our Nesting
Using our formula example, we can translate this into the process flow shown below. Note the legend key, middle left.
NESTED IF TIPS
There are a few great practises in addition to other mentioned above, you should adopt as habit to make your NESTED IFs, easy to read, maintain, problem solve and work in the desired way.
This formula below is hard to read on one line.
It’s very good formula writing practise to keep a logic to your layout. In the example above, not only is each statement on its own line, we’ve also started with B3=1 and worked through to B3=6. To organise into single lines, place the cursor in front of each IF statement then hold Alt and press Enter, repeat all the way down and you should see a pattern. As you progress into more complex NESTED IFs you’ll be thankful you did it this way! I really wish someone had told me about this earlier!
Note, however, the comment box that appears when building each IF statement doesn’t align in the way it does when the formula is on several lines, so you need to sometimes flip between the two depending on whether you’re building or fixing.
Another tip, and one I use frequently is to add an alert to the final FALSE value, this is to capture any additional values that might be added to the worksheet. You might not need to, but it’s worth it when needed. In the example above the formula has been written to test logic up to and including a score of 5. So, say your score system goes up a notch, this will alert you to an error, combine that error with conditional formatting to highlight the text/cell colour red and your becoming a fully-fledged dynamic Excel formula expert!
Ok, so that’s one basic nested, let’s look at a similar one but use the logical test symbols we looked at in the single IF, and keeping to the same format of single lines and in a logical order.
Combine with operators
As a refresher, the symbols we looked at can be found below, we’ll look at just GREATER THAN, LESS THAN, EQUAL TO and NOT EQUAL TO.
Let’s look at some of these in examples by using a sample set of data below.
In the above example, we are tasked with finding the commission % and working out the commission £.
The formula has been constructed using the same logical flow. Here we’ve instructed the formula to output the % associated with the sales once they fall into one of the categories in the table. Then it’s just a case of calculating the commission in £’s.
Once you master nested IFs you’ll really open yourself up to a much wider range of complex formulae. You can nest all sorts of formulas together to make one truly awe-inspiring calculation. It takes a bit of practise, start off small, and experiment. You’ll make some mistakes along the way, but you’ll learn from these experiences and be better for it.