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.
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:
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.
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).
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.
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 -
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.
Advantage of using Cell Names:
Disadvantages of using Cell Names:
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.
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 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~