Nested IF(S): A Guide to Decision Tree Logic in Excel
What is a nested IF statement, and why is it useful?
A nested IF statement is when you have two of more IF statements within the same formula. An example of this would be something like:
=IF(A1 > 10, “High”, IF(A1 > 5, “Medium”, “Low”))Which reads out as “If the value is greater than ten, it is high. If it is greater than 5, it is medium, otherwise it is low.”
Which may seem simple enough at first. But then how would you take the following statement and build a nested statement with it?
“For a salesperson to be considered ‘Tier 1’ they must have sold for more than twice the average sales of all salespeople, and have increased their client base by at least 10% from last year. For them to be considered ‘Tier 2’ they must have sold for at least as much as the average salesperson, and have increased their client base by at least 10% from last year. For them to be considered ‘Tier 3’ they have to have achieved at least one of the two conditions needed for a ‘Tier 2’ salesperson. All other salespeople are ‘Tier 4’.”
Being able to transcribe these requirements into a single cell formula will allow you to minimize the risk of error, and increase transparency about how decisions are being taken.
Where this becomes a challenge is in the fact that there are multiple ways you can format this statement with IF’s. Moreover, all those ways are technically right! There is no problem with using any formula as long as the desired results are achieved
What I want to instil is a set of guidelines to follow which will aid in making sure that your statements always work, and that there are no holes in the logic applied.
Building decision tree logic in Excel
Decision tree logic is normally displayed as branches stemming from an origin that display all potential outcomes to a situation through a set of segmented branches. The sum of the potential to achieve all outcomes is 1, or 100%, as probability states. That being said, it is not necessary to know these probability when building the tree.
Here is what the decision tree for the above described situation:
Lucidchart decision tree showing tier system potential outcomes
The decision tree was built using LucidChart, a free online charting tool with resources for hundreds of different types of shapes and tools for all your needs.
As we can see from the diagram, there are clear divisions between the metrics. The way we are going to order and describe our IF statements will pave a path from least likely occurrence to most likely. This will ensure that we do not leave gaps in our logic, and that we account for even the most unlikely of situations.
Let’s start by listing the final outcomes by their branches from top to bottom.
Salesperson (SP) achieves > 2xAvg sales and >= 10% customer growth
Gets placed in Tier 1
SP achieves > 2xAvg sales and < 10% customer growth
Gets placed in Tier 3
SP achieves > Avg sales and >= 10% customer growth
Gets placed in Tier 2
SP achieves > Avg sales and < 10% customer growth
Gets placed in Tier 3
SP achieves <= Avg sales and >= 10% customer growth
Gets placed in Tier 3
SP achieves <= Avg sales and < 10% customer growth
Gets placed in Tier 4
We can assume that it is fairly difficult for someone to achieve greater than double the average sales of the entire team, this would make it the most restrictive property relating to sales. If we do not know more about the industry, we cannot ascertain if a 10% growth rate is high or low, therefore we will give it a neutral 50/50 chance.
This would mean that achieving Tier 1 status (the most difficult sales objective and its growth metric) is the least likely to occur. Tier 2 is the next most likely as it lowers the requirements for sales to half of its original metric. Tier 3 is next most likely as only one of the two requirements is necessary. This leaves Tier 4 as being the easiest to achieve, which makes logical sense as even a salesperson with no sales to date will fall into this category.
Given the information we just plotted, we will define our formula in the same way. Starting with Tier 1, and moving down to Tier 4.
Let’s continue by listing the expressions we will be using to define each tier in Excel
Tier 1: AND( Sales > 2xAVG(Sales), Growth > 1.1*LY Clients)
Tier 2: AND( Sales > AVG(Sales), Growth > 1.1*LY Clients)
Tier 3: OR( Sales > AVG(Sales), Growth > 1.1*LY Clients)
Tier 4: AND( Sales <= AVG(Sales), Growth <= 1.1*LY Clients)
Finally we are ready to build the formula which will both encompass all potential outcomes, and filter the data from the most restrictive requirements to the most general.
Depending on the version of Excel you use, you will either need to nest these steps within multiple IF statements, or use the IFS statement in newer versions.
Here are how both formulas would look depending on which function you choose to implement it with.
IFS
=IFS( AND( Sales > 2xAVG(Sales), Growth > 1.1*LY Clients), “Tier 1”, AND( Sales > AVG(Sales), Growth > 1.1*LY Clients), “Tier 2”, OR( Sales > AVG(Sales), Growth > 1.1*LY Clients), “Tier 3”, AND( Sales <= AVG(Sales), Growth <= 1.1*LY Clients), “Tier 4”)
IF (Nested)
=IF(AND( Sales > 2xAVG(Sales), Growth > 1.1*LY Clients), “Tier 1”, IF(AND( Sales > AVG(Sales), Growth > 1.1*LY Clients), “Tier 2”, IF(OR(Sales > AVG(Sales), Growth > 1.1*LY Clients), “Tier 3”, “Tier 4”)))
This example highlights how sometimes newer functions may not result in the shortest versions of a formula, but might be easier to understand when read by someone who is not fluent in Excel.
Let us take both formulas and write them out logically as they are written
IFS
If SP has sales greater than twice the average, and has increased their customer base by over 10% then they are tier one.
Else if SP has sales greater than the average, and has increased their customer base by over 10% then they are tier two.
Else if SP has sales greater than the average, or has increased their customer base by over 10% then they are in tier three.
Else if SP does not have sales greater than the average, and has not increased their customer base by over 10% then they are in tier four.
IF (Nested)
If SP has sales greater than twice the average, and has increased their customer base by over 10% then they are tier one.
Else if SP has sales greater than the average, and has increased their customer base by over 10% then they are tier two.
Else if SP has sales greater than the average, or has increased their customer base by over 10% then they are in tier three.
Else SP is in tier four.
While both statements can read similarly, the need for increased brackets in a nested IF statement, and the constant shifts between being the output of a true or false condition make it a much less enticing formula to read through for a more novice user.
Never assume that you will always be the one to need this solution, or that you will be the one to modify it as times change. A solution, especially one with a more complex logic structure, should be as simple to read as possible for users of all levels.
Tips and tricks for building boolean logic into Excel
Always start with the outcome that has the most stringent requirements, and move to the most generic
Write out your requirements as IF (and/or) THEN ELSE statements in their entirety
Don’t have a ‘catch all’, explicitly state what is required for the most general outcome
Test for each potential outcome, as well as any values are used as cutoff points in your formula
Do not wrap your formula in an IFERROR until you have completed step 4
The output of your IFERROR statement should not be able to break formulas later down the line, use a value which will not cause further issues
If your formula take up more than three lines in the formula bar, there might be a better way to get it done
And the most important tip of all is practice, practice, practice! You can create any scenario with any ridiculous requirements you want, and learn how to solve them! Nesting IF statements, or using IFS, or SWITCH statements can always be challenging. There are so many creative and intriguing ways to manipulate them to your advantage when problem solving.