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 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 create a nested IF, follow these steps:

First, make sure 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 functions you'll need. Here I've added IF statements directly to the table. These are simply for reference when I build the formula later.

The first column shows the IF functions needed when moving from low scores to high scores. The second column shows the IF statements needed when moving from high scores to low scores.

It's important that you work in one direction. For 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 for this student is not less than 60, 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. It sounds a little confusing, but this is the key to building a formula that uses nested IFs.

Once I've added the second 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 in the table for the value if false. In this case, that's the grade "A". The way this works, is if all previous IF statements return FALSE, the grade should be an "A."

To finish off the formula, I need to add one closing parentheses for each IF statement we've used. In this case, that's four closing parentheses.

When I copy the formula down, we'll get the correct grade for each score.

So 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. The additional IF statements are added as the value if false for the previous IF statement.

In another video, we'll look at how to make formulas that use nested IF statements easier to read.