Spreadsheet Design:
Using IF

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web



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 illustrationClick on the illustration. An IF function adds some text to cell E14 based on the value of the Final Average. 

Sample: Grades Calculator

IF function adds text


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: Design
    Analysis To subtopics
    What If...To subtopics
    Logical Tests To subtopics
    FootprintUsing IF
    FootprintConditional Formatting
    Sharing DataTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics


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

Icon Step-by-Step

Step-by-Step: Using the IF function

 Icon Step-by-Step

What you will learn:

to write an IF function
to use the Function dialog
to use cell names in a function

Start with: Class disk 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)

  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. Class diskSave 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. Name Box: TotalPointsSelect 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 .

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.

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

Icono: ProblemaNamed 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 Button: Paste function the Paste Function button. The IF dialog appears, showing the current arguments for the IF statement.

    Dialog: IF using cell references

    Dialog: Paste FunctionIf 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 

    Dialog: IF


    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.