The Anatomy of an Excel IF Function
Part 2: Multiple Nested Conditions and Multiple Return Values in IF Statements
By John Cellini
Published on July 19, 2006
Updated September 9, 2010
Before we begin, download the exercise file for this tutorial if you didn't download it for Part 1 (it's the same one) or continue with the worksheet from Part 2.
Let's take a look at the following worksheet:
In Part 2, we nested an IF function within an IF Function in cell C3 that enabled us to return one of three values: a blank cell , "Pass," or "Fail." If cell B3 is blank, then cell C3 remains blank. If cell B3 contains a value of 60% or more, then C3 returns a value of "Passed." If cell B3 contains a value less than 60%, then C3 returns a value of "Failed."
In D3, we need a formula that will return one of several values depending on the value in B3. We want to assign grades to our students. Here is the breakdown for grades:
- 90% or better = A
- 80% to 89% = B
- 70% to 79% = C
- 60% to 69% = D
- Less than 60% = F
Let's write our pseudocode. By the way, pseudocode is not a exact art. You may even choose to skip it. After you master IF functions, you probably will have no need to write pseudocode. But for now, I think it's an excellent way to quickly blueprint your IF function, especially if you are creating a complex function. Write your pseudocode as closely to the actually syntax as you can.
If B3 is blank, then leave D3 blank, otherwise if B3 is 90% or better, 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."
I highlighted the "if" words in dark red that indicate nested IF functions. There are 4 nested IF functions. We can nest up to seven conditions within a single IF function. Each nested IF function is a FALSE value for the logical test in the previous IF function.
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%.
page 1 | 2 next page >>