The Anatomy of an Excel IF Function,
Part 3: Multiple Nested Conditions and Multiple Return Values in IF Statements
(page 2 of 3)
Notice in the second nested IF function the logical test is "B3 is greater than or equal to 80%." Well, 95% is greater than 80%; would that be assigned a "B" grade? You might think that the logical test should be "B3 is greater than or equal to 80% and less than 90%." It's useful to understand how Excel reads an IF statement.
Excel reads an IF statement from left to right. Excel looks for a TRUE outcome from a logical test (for example, B3>=0.9). Once a TRUE outcome is found, Excel stops reading the statement and enters the TRUE value in the cell. If the logical test is false, then Excel enters the FALSE value in the cell unless the FALSE value is an embedded IF function, in which case another logical test is performed. Let's take it step-by-step.
Excel evaluates the first logical test:
B3 is blank.
If this is true, then Excel enters a blank value in D3 (cell D3 remains blank) and that's the end of it. Excel does not bother with reading the remainder of the statement. If the logical test is false (meaning a test score was entered in B3), then a false value is returned. However, the false value is an embedded IF statement, so Excel evaluates the next logical test:
B3 is greater than or equal to 90%.
If the logical test is true (for example, if 95% was in B3), then the true value is entered in cell D3 (A) and the rest of the statement is not read. If the logical test is false, then the false value is returned, which happens to be another embedded IF function. Excel evaluates the next logical test:
B3 is greater than or equal to 80%.
If the logical test is true (for example, if 86% was in B3), then the true value is entered in cell D3 (B) and Excel is finished reading the statement. The only possibility at this point in our IF statement is for a value less than 90% because the previous logical test eliminated all values 90% or greater. Therefore, there is no need to indicate in our logical test that the value must be greater than or equal to 80% and less than 90%. All we need to test for are values greater than or equal to 80%.
If the logical test is false, then the false value is returned, which happens to be another nested IF function. Excel evaluates the next logical test:
B3 is greater than or equal to 70%.
If the logical test is true , then the true value is entered (C) and the game is over. If the logical test is false, then the false value is returned, which happens to be yet another embedded IF function. Excel evaluates the next logical test:
B3 is greater than or equal to 60%.
page 1 | 2 | 3 | next page >>