Explanation
The goal is to assign a grade to each score in column C according to the rules in the table in the range F4:G9. One way to do this in Excel is to use a series of nested IF functions. Generally, nested IFs formulas are used to test more than one condition and return a different result for each condition.
Testing more than one condition
If you need to test for more than one condition, then take one of several actions, depending on the result of the tests, one option is to nest multiple IF statements together in one formula. You’ll often hear this referred to as “nested IFs”. The idea of nesting comes from embedding or “nesting” one IF function inside another. In the workbook shown, we are nesting IF functions to assign grades based on a score. The rules for assigning each grade can be seen in the table below:
| Score | Grade |
|---|---|
| 0-63 | F |
| 64-72 | D |
| 73-84 | C |
| 85-94 | B |
| 95-100 | A |
To create a nested IF formula that reflects this logic, start we can start either at the bottom of the scale, or the top. In this example, we start at the bottom. The first condition to test is if the score is below 64:
=IF(C5<64,"F"
If the result is TRUE, we return “F”. If the result is FALSE, we move into the next IF function. This time, we test to see if the score is less than 73:
=IF(C5<64,"F",IF(C5<73,"D"
If TRUE, we return “D”. We don’t need to worry about scores less than 64 anymore, because that case is already handled. If the result is FALSE, we move to the next condition, which checks if the score is less than 85:
=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C"
If TRUE, we return “C”. If FALSE, we move to the next condition, which checks if the score is less than 95:
=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B"
If TRUE, we return “B”. At this point, the only condition to check is if the score is between 95 and 100. However, by process of elimination, we know that if the score has not yet passed any tests, it must be between 95 and 100. So, we only need to provide “A” in the last IF for the value_if_false argument, and close up the formula with 4 parentheses, one for each IF function. The final formula in D5 is:
=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))
You can see that it’s important in this case to move in one direction, either low to high, or high to low. This allows us to return a result whenever a test returns TRUE, because we know that any previous tests already returned FALSE.
Making nested IFs easier to read
By their nature, nested IF formulas can be hard to read. To make a nested IF easier to understand, you can add line breaks inside the formula to “line up” the tests and results line this:
=
IF(C5<64,"F",
IF(C5<73,"D",
IF(C5<85,"C",
IF(C5<95,"B",
"A"))))
The line breaks do not affect the formula. The Excel formula engine will ignore them.
IFS function
Another way to solve this problem is with the IFS function, which can handle multiple conditions without nesting. The equivalent formula with IFs is:
=IFS(C5<64,"F",C5<73,"D",C5<85,"C",C5<95,"B",C5>95,"A")
The IFS function doesn’t have a “default” value to return after all tests have run, so we need to use another logical test for the last category of scores, scores above 95. One workaround is to use TRUE as the last test, which you can see in the formula below.
=IFS(C5<64,"F",C5<73,"D",C5<85,"C",C5<95,"B",TRUE,"A")
Notes
- VLOOKUP can sometimes be used to replace complicated nested ifs .
- This article has many more examples of nested ifs with alternatives .
Explanation
This formula relies on a technique called “nested IFs” to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here .
The formula in this example is purposely more verbose than necessary to “show” all possible options and results in a way that is easier to understand and maintain. The trick is to structure the formula with line breaks to show each IF on a separate line along with the “true result” for that IF. The “false result” is the following IF statement. Notice the final false result will “catch” any case that fails all previous tests.
Essentially, we are using line breaks to build a “table” that the human eye can easily read. In addition, we are using the AND function to run more than one logical test at a time to limit the number of IF functions. The AND function inside each IF function tests both color and value.
Note you can use Alt + Enter to enter new lines in the formula bar. You’ll need to expand the formula bar vertically to see more than one line at a time.
More conditions
This formula approach can be expanded to evaluate more options. The AND function can handle more logical tests, and you can combine the AND function with the OR function if needed. You could also replace AND and OR with boolean logic . Finally, you can also use the IFS function in later versions of Excel to reduce nesting.
Result as calculation
Although the example above shows a numeric result for each set of options, the formula can be customized to run a calculation instead by replacing hardcoded values with any standard formula expression.
