Lesson 6 - Pages 238 - 242

This week you will have to depend mostly on the lecture and the discussion board for this most important concept. The textbook is presents this concept very briefly - just a few short exercises. We will be spending considerable time on this throughout the course so this week we just begin with the simplest version of the IF statement or the IF function.

Why use the IF function:

The IF function is by far the single most powerful and important function, not just in Excel but in any program. It allows the user to present the program or software a choice of two possible actions. The concept is simple. A question, or condition as it is sometimes called, is asked, or presented. The program tests to see whether the answer to the question is yes or no, or sometimes we say the condition is tested to see if it is true or false. If the answer is yes, or the condition is true, one action is taken, otherwise the other action is taken.

This conditional statement, as it is often called, allows for some very sophisticated maneuvering of your spreadsheet or other programs. You can imagine that when you are asked by your ATM "if you want another transaction?" somewhere in the ATM program there is a conditional statement setup to test if your answer = "yes" or your answer ="no". It is sometimes surprising to to users of MSWORD that even in that program the conditional statement exists. A very sophisticated use would be in a mail merge where you can test to see if the cistomer is a preferred customer so the mail merged letter prints a sales special for those customers, but not for others!

In Excel, there is very little that is done without the use of the IF function as you will surely see. Everything seems to be conditional. Consider these examples:

• If the employee's age is over 60, then benefits begin.
• If the season is winter, then resort prices are different.
• If the movie is new, then the DVD price is more.
• If the number of days is over 10, rental discount applies
• and on and on...
  

Syntax of the IF function:

The IF function always has three arguments, the first is the yes/no question or true/false condition, then the yes, or true option, and finally the no, or false option. Put another way, first you state your yes/no question, follow this with a comma, and then what you want Excel to do or display if the answer is "yes" to your question. Follow this with a comma, and then what you want Excel to do or display if the answer is no to your question.

Example:

=IF(A1="SINGLE","CALL ME AT 555-5555","NEVER MIND")

=IF(A1>21,35,15) - perhaps displaying the ticket price based on the age in the A1.

Note that whenever text is used in a function, it must be enclosed in quotes. However, when numerics are used, it must never be enclosed in quotes. Do not mistake named cells for text data. Named cells are never enclosed in quotes. They must be treated just as any regular cell address. Also note that in the second example, which is attempting to ask, if the age is over 21 then the ticket price is $35.00 otherwise it is $15.00, the $ does not go into the formula. If you want currency to appear, use the formatting tools for this.

In the second example above, the symbol > denotes the phrase greater than. This is called a relational operator. Here are the other relational operators that make it possible to ask every type of question:

Before we move on to trying out this important function, it might be a time to just mention that the IF can be as complex as you wish to make it. A more complex statement using functions inside the IF function, might look like this:

=IF(AVERAGE(A1:A10)>50,SUM(B1:B10),0)

We are not ready yet to attack this syntax just yet, but keep in mind, that placing functions inside other functions, is what makes Excel so powerful, and your ability to construct these type of formulas is what will make you indispensable to your employer, and the star of your workplace!

Using the IF

Below is a graphic showing the IF function in use to display a grade of Pass or Fail depending on the score. If we set the passing score at 60, you might use the formula shown below.

The formula is typed in once into cell B2 and copied down. The formula adjusts, as always, so the successive cells are tested individually. The results seem correct at first glance but focus on row 6. Is the passing score not 60? Why is the grade showing as "Fail"?

If can't figure it out, click here. The image will open in a new window or tab. Just close it to continue.

Choose your relationships carefully

When using relational operators, choose them carefully. >49 is NOT the same as >=50. There are a lot of numbers between 49 and 50 (49.1, 49.2 etc.)! This is especially significant when using dates. Imagine the trouble you could cause if you mistakenly said, in Excel speak: Benefit pay begins >59, meaning that checks should print when the employee is age 60 or higher. You would have mistakenly given the command to begin printing checks one second past midnight on the employee's 59th birthday as that would be technically greater than 59! So be precise. Think like a computer! Which brings us back to the troubleshooting issue above. When you correct the formula to use the >=, do not change it in row 6. It must be changed in the first formula, A2, and then re-copied! You do not know what the scores will be next time!

Well there is much more to say about the IF statement, and many renditions that we will explore over the course of the coming weeks. Although the few lines given to this most powerful functions in your book would indicate otherwise, you will soon agree that very little can be accomplished without it. You will also come to see that no matter what functions we use in the future, they are almost always used together with the IF function to make it more powerful!

But for now, lets leave it here and get you started putting this concept to use in yet another project. We will not leave this topic for some time yet, so the reading in the textbook will be a minimum for the next few weeks unless I assign dome unrelated technical topics such as printing or formatting and the like. For the main topics, which is always the functions, you will depend heavily on my lectures and the discussion board.

Take a look at the project for this week.

See you online!

Terry