|
The IF function in Excel is one that many Excel newbies don’t understand and, therefore, don’t use. But, this is one function that will make every Excel user’s life so much easier, so I thought I’d write a little introductory tutorial about it. What does an IF statement tell us?In a nutshell, the IF statement answers the question, “Is this true or false?”, then proceeds on some action based on this. For example, is the value in column A larger than the value in column B? Let’s use this data for our sample formulas:
The arguments in an If statement are as follows: Logical test – what are we asking? In this case, let’s say our logical test is “Is the value in Column A larger than the value in Column B?” Value if true – what do we want displayed if the answer to our question is “true”? In this example, let’s say we want the answer to be “yes” Value if false – what do we want displayed if the answer is “false”? In this example, let’s say we want “no”. How do we build an IF statement?In a formula, the arguments are separated by commas, so for this example, let’s put our formula in cell C2 and this is what it would look like:
This says, IF the value in A2 is greater than the value in B2, put
yes in C2 and if it’s not greater than B2, put no in C2.
NOTE: When you want text displayed in a cell, you must put the text in quotes in the formula. If instead of yes and no, you wanted to have numbers like 1 and 2, the second two arguments would not require quotes and would look like this:
You can put pretty much anything you like in the second two
arguments. As shown above you can put text or a number. You can also
tell Excel to leave the cell empty by using two quotes as your
argument (“”). Let’s say we want “yes” if it’s true, but if it’s
false, we want the cell left empty. Then our formula would look like
this:
You can do all kinds of things with text in IF statements. Let’s say
that we are evaluating the sales of our salespeople in order to see
if they should get a raise or be fired. If last year’s sales are in
A2 and this year’s sales are in B2, we can use an IF statement in C2
that says this:
Or, we can put cell references in the second two arguments. For
example, if we want the value if false to be something that is in
another cell, we can just put that cell name in the third argument.
Let’s say, cell D2 contains a bonus amount, we could use this
formula:
NOTE: the dollar signs in the reference to D2. This is because
we want this formula to ALWAYS refer to cell D2 no matter where we
copy the formula to. The dollar signs make the cell reference
absolute.
You can also nest other formulas within an IF statement. Let’s say if the number in cell A2 is not larger than the number in B2, we want to give this person 50% of what’s in B2. Our formula would look like this:
How do we repeat this IF statement on the rest of our rows?OK, so now you have the IF statement you want in cell C2 and want it repeated in cells C3 thru C7. All you need to do is click on C2 and grab the little fill handle in the lower right corner of the cell (a little black box you see when any cell is selected) and hover your mouse pointer over that fill handle until it looks like a skinny black cross, then just drag down to cell C7. Because the cells references in the formula are relative (as opposed to absolute with dollar signs), the cells referenced in the formula will upgrade relative to where you drag them. So, A2 in cell C2 becomes A3 when you drag the formula to C3, etc. Let’s look at how all of these IF statements look and their results. Using the examples above, your formulas would look something like this:
And your results would look something like this:
After you have your formulas in column C, try changing the numbers in columns A and B and you will see the results will change in column C. To see an interactive IF Statement at work, click here. What’s next?Click here to learn how to add multiple arguments when you want the IF statement to look at more than one “logical test” and/or you want more than one value if true or false. Happy formula making! |
|
.
