Did you know we can use a nested IF formula to extend the number of logical tests and therefore, possible outcomes? Simply put, this is multiple IF’s nested in the one formula.
Prior to Excel 2007 the limit of IF's you could nest 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!
In a previous tutorial we looked at the IF function (singular), 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.
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.
Nested IF Formula
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 solution would be to 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(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.
Too many nested IF's can result in performance issues. In this tutorial you can learn alternatives to nested IFs.