Explanation
The goal is to calculate the correct invoice status (“OK”, “Paid”, or “Overdue”) using the following rules:
- If there is an “x” in the “Paid” column, return “Paid”.
- If there is not an “x” in the “Paid” column, and if the age is less than 31 days, return “OK”
- If there is not an “x” in the “Paid” column, and if the age is not less than 31 days, return “Overdue”
This problem can be solved by nesting one IF function inside another.
Calculating age
To calculate invoice age in days, the formula in cell E5, copied down, uses the TODAY function :
=TODAY()-C5
This works because Excel dates are just serial numbers. At the time of this writing, the date is May 20, 2023. In Excel’s date system, this is the number 45066. The due date of March 30, 2023, is the number 45015. Excel evaluates the formula above like this:
=TODAY()-C5
=45066-45015
=51
IF function
The IF function in Excel runs a test, then performs one action if the result is TRUE, and a different action if the result is FALSE. The generic syntax for IF looks like this:
=IF(test,true_result,false_result)
The first argument is the logical test, and the second argument is the result (or calculation) to return when the test is TRUE. The third argument is the “else” — the value or calculation to return if the result of the logical test is FALSE.
IF paid
In the example shown, the first thing to check is if the invoice has been paid or not. We do this by checking the Paid column for an “x” as seen below. Notice that both “x” and “Paid” are enclosed in double quotes because they are text values:
=IF(F5="x","Paid")
Translation: If F5 equals “x”, return “Paid”.
At this point, we have not provided anything for the value if false. This means the IF function will return FALSE when F5 is empty. If this was the only thing we were checking, we might want to provide an empty string ("") like this:
=IF(F5="x","Paid","")
In Excel, an empty string will not display anything. It will look like an empty cell.
IF overdue
The remaining task is to check the age of the invoice. To extend the formula to check for an overdue status, we use another IF formula like this:
IF(E5<31,"OK","Overdue")
Translation: If E5 is less than 31, return “OK”, else return “Overdue”.
Notice the value 31 is a number and, therefore, not enclosed in quotes. This formula will return “OK” for any age below 31 and “Overdue” for any age over 30. This number can be customized as desired.
The final step in the problem is to combine the two IF formulas above. We do this by starting off with the first IF:
=IF(F5="x","Paid")
Then we extend the formula by nesting the second IF inside the first IF:
=IF(F5="x","Paid",IF(E5<31,"OK","Overdue"))
Translation: If F5 equals “x”, return “Paid”. Else if E5 is less than 31, return “OK”, else return “Overdue”.
Notes
- The technique of placing one IF inside another is called " nesting ." You will sometimes hear a formula like this called a “Nested IF formula”. This page has many examples .
- Remember to enclose text values inside IF in double quotes (""), but do not quote numbers or operators. See How to use the IF function for more details.
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 .
