Nested IF Functions in Excel
2010-11-18
The simplest Nested IF Function is using one IF Function inside another. When you have more than a few choices, nesting more IF Functions can quickly get complicated and, quite frankly, there are better ways to make decisions with Excel. Having said that, I have a simple method to account for the different choices that can arise with nested IF Functions.
Two Conditions - Binary Outcome
The IF Function evaluates a logical test to either TRUE or FALSE. A binary outcome. One condition requires only one IF Function.
With two conditions that both evaluate to TRUE/FALSE you must consider all possible outcomes.
This chart shows there are four different outcomes for Condition 1 and 2. I've added a third column to determine what action to take for each outcome.
One Nested IF Function
In this example three actions are required, which means one nested IF Function, so the Action column has the following formula:
=IF(Cond_2=TRUE,"Action 1",IF(Cond_1=TRUE,"Action 2","Action 3"))
Which translates to: if condition 2 is TRUE then do action 1, or if condition 2 isΒ FALSE and condition 1 is TRUE then do action 2, or if condition 2 is FALSE and condition 1 is FALSE then do action 3.
How Many Actions are Required?
Having two conditions with binary outcomes doesn't mean there will automatically be three actions for these four possible outcomes. The chart above is designed for you to determine how many actions are required, and then construct the IF statement logic.
Two Nested IF Functions
There could be four different actions needed, which would require two nested IF Functions. Assuming each of the four conditions above has a different outcome, and labeling each Action 1 through 4 from top to bottom, then the following formula will work:
=IF(Cond_1+Cond_2=2,"Action 1",IF(Cond_1+Cond_2=0,"Action 4",IF(Cond_1=TRUE,"Action 2","Action 3")))
Which takes advantage of the fact that TRUE + TRUE = 2, FALSE + FALSE = 0, and either combination of TRUE + FALSE = 1.
Three Conditions - Binary Outcome
I rarely do more than two nested IF functions because they get complicated. Taking into account the binary nature each outcome, if there are N conditions, then there are 2N possible outcomes that must be accounted for with the formula logic.
I always put together a binary outcome chart when dealing with three conditions. Even if I don't use nested IF Functions for a sloution, all the outcomes must be considered.
I simply add a column for what action needs to be taken and look to see where the commonality lies for a starting point, then try and compose something to fit.
Excel 2003 can nest up to 7 IF Functions, and Excel 2007 and 2010 both allow 64 nested IF Functions. How anybody could do that many Nested IF's is beyond me.
If you're trying to nest more IF Functions than I've shown here, I wish you Good Luck.