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

IF function adds text
|
 |
 Where you are: JegsWorks >
Lessons >
Numbers
Before
you start...
Project 1: Excel Intro
Project 2: Excel Basics
Project 3: Format & Arrange
Project 4: Groups & Formulas
Project 5: Design
Analysis

What If...
Logical Tests

Using
IF
Conditional
Formatting
Sharing Data
Summary
Quiz
Exercises
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 |
Start with:
trips35.xls (saved in
previous lesson)
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)
- Open trips35.xls and select the
Bonus sheet, if
necessary.
- Select cell G9, which shows the Bonus amount.
- 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 .
-
Save
as trips36.xls .
- Test the formula by changing numbers for the trips to reduce
the Total Points below 50. Test values that give exactly 50 points also.
- 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.
-
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.)
- Select cell C14 and name it MinimumPoints
-
Select cell C16 and name it BonusRate
-
Select cell G4 and name it Sales .
- Select cell G9, name it Bonus .
Advantage of using Cell Names:
- The formula actually explains itself if you choose the names wisely.
Disadvantages of using Cell Names:
- 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.
-
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.
-
Replace the arguments, using the new cell names:
Logical test: TotalPoints>=MinimumPointsValue 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.
|