The If Function

There are times when you would like to have one formula in a cell in one situation, but if another situation occurred, you'd like to have a different formula in the same cell. For example, suppose you are building the following worksheet to compute the commission for your salespeople. If the amount of their sale for the week is over $3,000, they earn a 5% commission. But if their sales are $3,000 or less, their commission in only 3%. You would need one formula to compute commissions at 5% and another formula to compute commissions at 3%. Of course you will use the same worksheet each week, but if the salespeople sell different amounts each week, you'd have to write a formula for each salesperson each week. UNLESS you knew how to use the IF function.

commission.gif (4304 bytes)

An IF function looks like this: =IF(Condition,True,False)

Condition: A condition is test used to make a comparison between two values, labels, or formulas. A condition has the values, labels, or formulas being compared on the left and the right sides with a relational operator in the middle. For example, a condition might be: A1=5 In this example, you are testing to see if the value entered in cell A1 is equal to 5. Another example could be: B5>C4*2 In this example, you are testing to see if the value in cell B5 is greater than the value of C4 multiplied by 2.

There are six comparison operators that can be used in conditions:

Operator	Description
>	greater than
<	less than
>=	greater than or equal to
<=	less than or equal to
=	equal to
<>	not equal to

On each side of the condition, you may have a cell reference, a value, a label, or a formula. In most cases, the left side of the condition is a cell reference.

True/False branches: The True branch tells Excel what to place in the cell containing the IF function if the condition is true. The False branch tells Excel what to place in the cell if the condition is false. These branches can contain references to another cell, value, labels, or formulas.

Let's go back to our original example. Look at the commission worksheet again. We need to write a formula in cell C5 to compute the first salesperson's commission. Then we will copy this formula down the column. The formula in cell C5 will be =IF(B5>$B$14,$C$14*B5,$C$15*B5). The condition is comparing the first salesperson's sale in cell B5 to the amount he must make in order to get the higher commission. If the value is B5 is greater than the value in B14, then the True statement will be executed and the sales in B5 will be multiplied by the value in C14 which is 5%. If the value in B5 is NOT greater than the value in B14, then the False statement will be executed and the sales in B5 will be multiplied by the value in C15 which is 3%. Notice that absolute cell references are necessary for cells B14, C14, and C15 so these cell references will not change when the formula is copied down the column.

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, click 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 Tuition. After you complete the worksheet, click here to check your work: Answer to Tuition.

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

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

Hopefully, after completing these exercises, you now feel comfortable using the If function. If you'd like to read more, the textbook covers the If funtion on pages 7.13 through 7.16. Before moving on, do the following quick check exercise.

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 below 60, make the grade 60.

Go to the nested If function.

Go back tothe Round function.