The Anatomy of an Excel IF Function,
Part 3: Multiple Nested Conditions and Multiple Return Values in IF Statements
(page 3 of 3)
If the logical test is true , then the true value is entered (D) and that's the end of it. If the logical test is false, then the false value is returned:
"F"
This time, the game is definitely over.
Let's take another look at our pseudocode.
If B3 is blank, then leave D3 blank, otherwise if B3 is 90% or more, enter "A", otherwise if B3 is greater than or equal to 80%, enter "B", otherwise if B3 is greater than or equal to 70%, enter "C", otherwise if B3 is greater than or equal to 60%, enter "D", otherwise enter "F."
Now, let's use our pseudocode to write our formula (you will see why writing pseudocode for a complex IF statement is a valuable device):
=IF(B3="", "", IF(B3>=0.9, "A", IF(B3>=0.8, "B", IF(B3>=0.7, "C", IF(B3>=0.6, "D", "F")))))
Remember, parentheses must balance; for every opening parenthesis you must have a closing parenthesis. The easiest way to to this is to pay attention to the color coding of the parentheses. On my application, the main IF function that nests all other IF functions has a black opening parenthesis, therefore the last closing parenthesis should be black.
Copy the formula. Click on cell D3 and paste the formula into the Formula bar text box.
Use the fill handle to copy the formula to the cells below, D4:D8. Test your formula thoroughly. If there is an error, carefully review your formula and compare it to the formula in the graphic above.
In this tutorial, we learned how to nest several IF functions within an IF function, which provides us with several values that can be returned in cell D3. In Part 4, we used a named cell and nest the SUM function in an IF statement.