The Anatomy of an Excel IF Function
Part 2: Nesting an IF Function

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

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 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 graphic

In Part 1, we created a simple formula that returned a value of "Passed" if a student scored 60% or better on his exam or a value of "Failed" if he scored below 60%. But this function has a slight problem. In the above graphic, the test scores have not yet been entered for the last four students and yet the Result column is returning a value of "Failed" for these students. We want those cells to remain blank until a test score has been entered.

Our objective is to create function that will automatically return values in the Result column when the test scores are entered. When a test score is entered, the Result column will display "Passed" or "Failed". If a test score has not been entered, we want the cell that contains the formula in the Result column to return a blank cell value (in other words, to remain blank).

Discussion

Let's take another look at our formula in cell C3:

=IF(B3>=0.6,"Passed","Failed")

The two conditions we have considered are these:

  1. The value in B3 is equal to or greater than 60%.
  2. The value in B3 is less than 60%.

But there is a another condition we have not considered. Cell B3 could be blank. If cell B3 is blank, then we want cell C3 to remain blank until a test score is entered in cell B3.

page 1 | 2 | 3 | next page >>

Top of Page