Did you want Working with Numbers: 2007,2010,2013,2016  or español

The  IF function is a real help when you would like to have your sheet behave in different ways depending on the values involved.

For example, in the Grades Calculator below, when the Final Average is under 70, the worksheet shows something special to warn of a low grade. It shows something different if the grade is below 60, a failing grade.

Click on the illustration. An IF function adds some text to cell E14 based on the value of the Final Average.

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 4: Groups & Formulas

Search
Glossary

Appendix

### Formula with the function IF

The IF function produces one of two possible values, based on whether a comparison statement is TRUE or FALSE. A value can be a number, text within double quotes, a cell reference, or a formula.

Syntax of an IF function:

=IF(logical comparison, value if TRUE, value if FALSE)

More than 2 values: You can nest up to 7 IF statements to handle more than two values. For example, you can show three different results by using an IF statement as the "value if False" part.

=IF(E13>100,"Extraordinary",IF(E13=100,"Perfect",E13))

The statement above shows 3 different results, depending on whether E13 is greater than 100 ("Extraordinary"), equal to 100 ("Perfect"), or less than 100 (value in E13).

## Step-by-Step: Using the IF function

 What you will learn: to write an IF function to use the Function dialog to use cell names in a function

A bonus is not awarded to a travel agent unless the Total Points is 50 or larger. The Bonus Calculation table should not even calculate the bonus if Total Points is less than 50. You can use an IF function to handle this situation.

### Formula: IF

The IF function gives you one of two possible values, based on whether a comparison statement is TRUE or FALSE. A value can be a number, text within double quotes, a cell reference, or a formula. The general form of an IF function is -

=IF(logical comparison, value if TRUE, value if FALSE)

1. Open trips35.xls and select the Bonus sheet, if necessary.

2. Select cell G9, which shows the Bonus amount.

3. In the Formula bar write  =IF(D11>=C14,G4*G8,0)

Be careful. Punctuation is critical in a formula! Parentheses enclose the three arguments for the IF function. The language you are using makes a difference. In English the parts are separated by commas. In Spanish, for example, semicolons separate the parts.

What it means: If Total Points (in D11) is larger than the minimum number of points required for a bonus (in C14), the cell will calculate the bonus by multiplying the Bonus Rate (in G4)  by Sales (in G8). If Total Points is not large enough for a bonus, the cell value is zero. In Currency format you will actually see  \$ -  instead of  0 .

4. Save as  trips36.xls .

5. Test the formula by changing numbers for the trips to reduce the Total Points below 50. Test values that give exactly 50 points also.

6. Undo your changes but not the formula change.

### Cell Names

The formula in cell G9 would make more sense with words. Who can remember which cell references are which? You will name the cells used in the formula and rewrite the formula to use the names. You could use names throughout the workbook. Be careful to type in the Name Box and not in the Font box. You will create your formula using the Formula dialog. That will help with your punctuation.

1. Select cell D11. Click in the Name Box, type  TotalPoints , and press ENTER to name the cell. (Remember: you cannot use a space in a cell or range name.)

2. Select cell C14 and name it  MinimumPoints
3. Select cell C16 and name it  BonusRate
4. Select cell G4 and name it  Sales .
5. Select cell G9, name it  Bonus .

• The formula actually explains itself if you choose the names wisely.

• You do not see the cell reference that matches the cell name.
• You must spell the name correctly.

Label each named cell and use the same words. For example, instead of Minimum points for bonus in cell B14, you could use Minimum Points, which is nearly the same as the name for cell C14 MinimumPoints. It can help you later when you've forgotten exactly what you did.

Named the wrong cell: Suppose you named the wrong cell. You cannot just name the right cell with that name. The name is already in use! You cannot delete it from the Name Box list directly. Frustrating! There is a way, however, to correct your mistake.

From the menu select Insert | Name | Define.... A dialog appears that lists all the named cells in the entire workbook. Select the name that was given to the wrong cell and change its reference in the bottom text box. Or you can delete the name from the list with the Delete button.

### Formula: IF Dialog

Excel knows that some of us are challenged when it comes to proper spelling and punctuation of formulas. There is a dialog method for creating functions that lets you just fill in the blanks. Such a relief!

You will use the dialog to re-write the formula for Bonus to use the new cell names. Formulas make so much more sense with words.

1. While the cell Bonus is selected, click on the Paste Function button. The IF dialog appears, showing the current arguments for the IF statement.

If the cell does not already have a formula in it, a dialog appears that lists all the functions that Excel knows. You can select the function you need from the list.

2. Replace the arguments, using the new cell names:
Logical test:  TotalPoints>=MinimumPoints

Value if true:  Sales*BonusRate

Value if false:  0

Notice that the dialog show you the current value of each of the arguments to the right of the textbox for that argument. This can help you debug your formulas before you leave the dialog. It only works if you know what those values should be right now. If some of the cells used don't have values yet, you will get errors. It is better to define and name cells before writing formulas that refer to them.