Nested If Function

A nested If formula contains an If function inside another If function. For example:

To calculate the dues, the formula in cell C6 should be:

=IF(B6>=$B$13,$C$13,IF(B6>=$B$14,$C$14,$C$15))

=IF(Condition,True,IF(Condition,True,False))

The False part of the first If function is everything from the second If through the first closing parenthesis. Notice that there are two closing parentheses, one for the first If function and one for the second If function.

This formula would be used because there are three possible answers whereas a single If function is used for two possible answers. If you had four possible answers, you would need a formula which had three nested If functions. You will always need one less If function than you have possible answers.

In the function above, first Excel compares the value in cell B6 (the age of the first person) to the value in cell B13 (25). If the value in B6 is greater than or equal to the value in B13, the value in cell C13 (the $50 dues) is placed in the cell and that's it. No further action is taken. However, if the value in cell B6 is NOT greater than or equal to the value in B13, then Excel looks to the False branch which is another complete If function. So then Excel compares B6 to B14 (21). If the value in B6 is greater than or equal to the value in B14, the value in cell C14 ($25 dues) is placed in the cell. If the value in B6 is NOT greater than or equal to the value in cell B14, then the value in cell C15 ($20 dues) is placed in the cell.

To practice this, download the following files. To download a file, simply click on the name of the file below. You will be given a dialog box asking where you'd like to save the file. Browse to the directory where you store all your Excel files, then click OK.

Once you've downloaded the file, open it in Excel. Then create the If formula in the appropriate cell and copy the formula down the column. When you have finished, return to this page and check your work by clicking on the answer links below. After viewing the answer, click the Back button on your browser to return here.

Download Grades2. After you complete the worksheet, click here to check your work: Answer to Grades2.

Download Commission. After you complete the worksheet, click here to check your work: Answer to Commission.

Now, try this one. It has 5 possible answers, so how many nested If's will you need?

Download Grades3. After you complete the worksheet, click here to check your work: Answer to Grades3.

There's really an easier way to do this when you have more than three possible answers. You can use a lookup function. You'll learn the lookup function in the next tutorial.

Quick Quiz: Write a formula for the following situation and then type the formula in a e-mail message and send it to me.

A student's grade on a test is in cell B5. Write a formula in cell C5 to curve that grade. If the student's grade is 60 or greater, add 5 points to his grade. If the grade is between 50 and 60, add 10 points to the grade. If the grade is below 50, make the grade 60.

Go to Payment and Future Value Functions.

Go back to The If Function.