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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
