Nesting is a powerful tool, but once you combine more than a couple IFs, the logic can start to look like spaghetti quickly :) The trick is to keep things organized. In this video, Dave shows a painless way to build a nested IF formula.
How to create a formula with nested IFs.
In this video, I'll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions.
Let's take a look.
This worksheet shows a class of students with five test scores in columns D through H, and and an average in column I.
In column J we need to add a formula that calculates a grade based on the average. This can be done with nested IF statements.
When you need to create nested IF, follow these steps:
First, make the logic you need to implement is clear. In this case, I've made a table that clearly shows what score is needed for each grade.
Next, if you're new to nested IFs, list out the IF formulas you'll need. Here I've added IF statements directly to the table.
The first columns shows the IF functions needed when moving from low scores to high scores The second column shows the IF statements needed to move from high scores to low scores.
It's important that you work in one direction. In this example, let's work from low to high.
Now add the first IF statement. If we stop there, the formula returns FALSE, because the average is not less than 64 and we aren't supplying anything for value if false in the formula.
To continue, we need to add the next IF statement as the value if false in our first IF statement.
This is the key to building a formula that uses nested IFs.
Once I've added the 2nd IF statement, I need to add the third, as the value if false to the second. And so on.
In the last IF statement, supply the final value for value if false. In this case, that's the grade "A". That is, if all previous IF statements return false, the grade should be A.
To finish off the formula, I need to one closing parentheses for each IF statement we've. In this case, that's four closing parentheses.
When I copy the formula down, we'll get the correct grade for each score.
To recap, in this example multiple IF statements are arranged in order, testing scores from low to high.
Each IF statement contains a test, and a value if true, and a value if false. Additional IF statements as the value if false for the previous IF statement.
In another video, we'll look at how to make this kind of formula easier to read.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
Download 200+ Excel Shortcuts
I work as a financial analyst and your page is a leading one I use to find so important info for daily tasks like formula explanation, how to construct different formulas and so on. This is brilliant! You helped me a lot. - Sarunas