Excel 2007 – Nested IF’s Explained

In a previous tutorial we looked at the IF statement, which is one of the most versatile functions in Excel, but on its own you’re limited to only one of two outcomes.  That is, if the answer to the question I am asking is true, do this, if not, do that.

To extend the number of possible outcomes we can use a Nested IF statement.  Simply put, this is multiple IF’s nested in the one formula.  Previous to Excel 2007 the limit of nested IF’s you could have in one formula was 7.  Excel 2007 has increased this to an outrageous 64.  I say outrageous, because in most cases if you’re using more than a few nested IF’s in one formula, there’s most likely a more efficient way to perform your calculation.  So don’t get carried away nesting!

Before we dive in to nested IF’s I want to recap our singular IF statement example to remind us how the logic works:

=IF(The number of units in column D is >5,Then take the Total $k x 10%, but if it’s not > 5 then take the Total $k x 5%)

So, if the answer to the question is true, you get outcome 1, and if answer is false you get outcome 2.

Now let’s take a look at a more complex problem that a nested IF would solve.

In our spreadsheet below I’d like to enter the commission for each row in column G.  The commission rates are different for each region.  I’ve listed the different rates in columns I and J so it’s easier to follow…..and later we’ll make the formula more dynamic using this table, but let’s walk before we run.

In Excel language our Nested IF statement would read:

= IF(logical_test, value_if_true, IF(logical_test, value_if_true, IF(logical_test, value_if_true,  IF(logical_test, value_if_true,………….so on and so on up to 64 iterations)

Let’s translate it into English by applying it to row B of our spreadsheet:

=IF(B2=”Central”, if so enter 3%, if not see if B2=”East”,  and if so enter 4%, if not see if B2=”North”,  and if so enter 5%, if not see if B2=”South”,  and if so enter 6%, if not see if B2=”West”,  and if so enter 7%, if not enter ”Missing”)

In Excel it would look like this:

=IF(B2=”Central”, 3%,IF(B2=”East”, 4%,IF(B2=”North”, 5%,IF(B2=”South”, 6%,IF(B2=”West”, 7%,”Missing”)))))

In the above formula we’re telling Excel to put 3% in the cell if B2=”Central”, if not move on to the next IF statement and so on.  In the last IF statement, IF(B2=”West”,7%,”Missing”), we tell Excel to enter the word ‘Missing’ in the cell if all previous IF’s are false.

Alternatively we could instruct Excel to enter ‘0%’ or anything else we like in place of ‘Missing’.  Or, if we left this argument out altogether Excel would enter the word ‘FALSE’ for us by default.

Let’s make it better

With the formula the way it is we’d have to manually update the percentages for each region if we wanted to alter them.  And then copy and paste the revised formula down the column.  A better formula would link to the table in columns I & J.  Then if we updated the percentages in column J, our formula in column G would dynamically update.

If we linked our formula to column J for the percentage rates it would look like this:

=IF(B2=”Central”, $J$2,IF(B2=”East”, $J$3,IF(B2=”North”, $J$4,IF(B2=”South”, $J$5,IF(B2=”West”, $J$6,“Missing”)))))

If we wanted to change a rate we’d simply change the rate in column J and it would dynamically update our formula in column G.

You could take it one step further and link the IF statement to the region names as well, but I’ll let you play around with that when you download the practice spreadsheet.

You’ll notice that the references to the rates in column J have $ signs prefixing the column letter and row number.  These are absolute references.  You can learn about absolute references in a previous tutorial and discover how they can improve your productivity in Excel.

I know I said at the beginning that you shouldn’t use more than a few Nested IF’s, and I’ve broken that rule here for the purpose of my example.  In reality I would use the VLOOKUP in this scenario as it’s a simpler formula for both the user to interpret later on, and for Excel to compute.

Have you signed up for our Free Microsoft Office Online Training yet? There’s over 10 hours of free video tutorials on Excel, Word and Outlook.  Get instant access, no catch, no credit card required.

Did you like this tutorial, or could it have been better? Let us have your comments below so we can improve. Remember to share the knowledge with your friends and colleagues.  Click the icons below for Twitter, facebook, Stumbleupon and many more.

Tagged as: excel nested if statements, excel nested ifs, nested ifs