The IF built-in function is more involved than the others - such as SUM and AVERAGE. Between the ScarletApps help features, what we covered in lecture, and other material covered in recitation and also, given we learned about the IF statement in BASIC, it should not be too difficult for you to use. But in case you are having some trouble, I am putting this further info here.

One of the built-in functions is called IF. It differs from some of the other ones you learned in that it takes three arguments. That is, it looks like this:

IF(part1, part2, part3)

Notice the three parts must be separated by commas. Now, IF -being a built-in function- is used inside of equations. Sometimes it is the whole equation and thus you could put

=IF(...)

into a cell. (The equal sign as you know, marks it as an equation. You, of course, would fill in the "..." with the three parts I will talk about soon.) In more advanced cases, the IF might be part of some bigger equation such as:

=A1*B13+IF(...)/15

You shouldn't have to do this for this class, but be aware that the IF function, like all built-in functions, can merely be one part of a much bigger equation, if you so chose.

Now, what are those three parts?

Part1: This is a condition. Just like in BASIC it can be an "equation RELOP equation". However, just remember we are in spreadsheets now. So there are no variables. Rather you use cell references. Examples for this part could be:

• B1 > 15
• $C$1 < D1
• 100 <= A14
• C55+D55 = Z99 + SUM(B3:B10)

This last one is much more complex than you will need. You will usually do something simple like the first one.

Part 2 and Part 3: These will both be equations. The only difference is you will not mark these internal equations with equal signs. (The whole IF is already marked as an equation by the initial "=".) What is an equation? Well you already know this because you should be using them in other cells already. Things like:

• =15
• =B1 + B2
• =AVERAGE(Z1:Z99)
• =A14 /6 + $C$1 * D16

Except as I just mentioned, when you put these as Part 2 and Part 3 of an IF, you will leave out the "=".

Great. So know you know what an IF statement looks like. What does it do?

Simple, it does exactly what it does in BASIC. It decides between the two ways of doing something. To be precise, the IF in spreadsheets decides between two ways of calculating something. That is, you use it when the value of a cell can be calculated in one of two ways depending on other factors. The condition (part 1) is checked to see if it is true or false. If it is true, the first equation (part 2) is used to calculate what to display in the cell. Otherwise, since the condition is false, the second equation (part 3) is used.

Examples:

I. Let's say, in cell A15 you have placed a person's salary. In cell D15 you want to calculate the amount of taxes they should pay. They pay 40% taxes if their salary is over $50,000. Otherwise they pay 10% taxes. What equation can you put in cell D15 to calculate the taxes? Here it is:

=IF(A15>50000, A15*.4, A15*.1)

Notice, the first part is a condition (equation relation equation). The second and third parts are both equations (lacking the equal signs). What happens if A15 had $75,000 in it? Well the condition here would be true and thus the first equation (a15*.4) would be used to calculate 40% of what is in A15. This result would show in the cell where this equation was typed. (D15). That is exactly what we want. Notice, however, if A15 had $20,000 in it, the condition would be false and thus the last equation would be used - calculating 1O% taxes.

II. How about the same example, but in this case a person earning less than $50,000 pays NO taxes at all. You could use:

=IF(A15>50000, A15*.4, 0)

In effect, the second equation (used when the condition is false) is "=0" which is indeed what we want to be displayed.

III. Here is another example, suppose cell E7 lists what Department a person works for. All people who work for "Accounting" get a $1000 bonus, while everyone else in the company gets $600. What equation can you put into Cell F7 to calculate this person's bonus. The following works fine:

=IF( E7="Accounting", 1000, 600)

IV. One last thing, sometimes you want to decide between two words (or letters) that you want to appear in a cell. Somehow, in the equations you need to have a way to indicate that something is text. You do this by placing quote marks around it. Let us say you had a spreadsheet to keep track of sales at various football games in a stadium that holds 45 thousand people. The current number of tickets sold is in cell B99. Let us say Column F is entitled "Sold Out (Yes/No)". How can you get one of the words "Yes" or "No" in cell F99. The answer is you use an IF statement there. Here it is.

=IF(B99=45000, "Yes", "No")

See the TAs or send me email if you need more help.

Good luck,

-Jt