The IF statement is 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 what we call a Nested IF statement.
In basic terms this is multiple IF’s nested in the one formula. Previous to Excel 2003 the limit of nested IF’s you could have in one formula was 7.
Excel 2007 onwards has increased this to 64 if you’re using more than a few nested IF’s in one formula, there’s most likely to be a more effective way to perform your calculation
Before we take on nested IF’s we shall 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.
Let’s look at a more complex problem that a nested IF would solve.
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.
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=”london”, if so enter 3%, if not see if B2=”liverpool”, and if so enter 4%, if not see if B2=”birmingham”, and if so enter 5%, if not see if B2=”southend”, and if so enter 6%, if not see if B2=”basildon”, and if so enter 7%, if not enter ”Missing”)
In Excel it would look like this:
=IF(B2=” London”, 3%,IF(B2=” liverpool”, 4%,IF(B2=” birmingham”, 5%,IF(B2=” Southend”, 6%,IF(B2=” basildon”, 7%,”Missing”)))))
In the above formula we’re telling Excel to put 3% in the cell if B2=” London”, 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.
Instead 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 Improve it
The formula as it is we 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=”London”, $J$2,IF(B2=”Liverpool”, $J$3,IF(B2=”birmingham”, $J$4,IF(B2=”Southend”, $J$5,IF(B2=”Basildon”, $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.
