The Anatomy of an Excel IF Function
Part 3: Multiple Nested Conditions and Multiple Return Values in IF Statements

By John Cellini
iCellini Computer Training
Published on July 19, 2006

Before we begin, you can download the exercise file for this tutorial if you haven't downloaded it for the previous sessions or recreate the worksheet below (the row and column headings and the names of the students).

Let's take a look at the following worksheet:

Test Scores with Grades

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 , "Passed," or "Failed." 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. 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 functions within a single IF function. Each nested IF function is a FALSE value for the logical test in the previous IF function.

page 1 | 2 | 3 | next page >>

Top of Page