|
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:
| |
A |
B |
| 1 |
First Number |
Second Number |
| 2 |
13 |
20 |
| 3 |
12 |
2 |
| 4 |
44 |
325 |
| 5 |
100 |
10 |
| 6 |
3 |
50 |
| 7 |
40 |
100 |
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:
=IF(A2>B2,”contact this salesperson”,”give this one a raise”)
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:
=IF(A2>B2,”contact this salesperson”,$D$2)
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:
=IF(A2>B2,"contact this salesperson",(B2*50%))
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:
| |
A |
B |
C |
D |
| 1 |
First Number |
Second Number |
Formula |
|
| 2 |
13 |
20 |
=IF(A2>B2,"yes","no") |
500 |
| 3 |
12 |
2 |
=IF(A3>B3,1,2) |
|
| 4 |
44 |
325 |
=IF(A4>B4,"yes","") |
|
| 5 |
100 |
10 |
=IF(A5>B5,"contact
this salesperson","give this one a raise") |
|
| 6 |
3 |
50 |
=IF(A6>B6,"contact
this salesperson",$D$2) |
|
| 7 |
40 |
100 |
=IF(A7>B7,"contact
this salesperson",(B7*50%)) |
|
And your results would look something like this:
| |
A |
B |
C |
D |
| 1 |
First Number |
Second Number |
Result |
|
| 2 |
13 |
20 |
no |
500 |
| 3 |
12 |
2 |
1 |
|
| 4 |
44 |
325 |
|
|
| 5 |
100 |
10 |
contact
this salesperson |
|
| 6 |
3 |
50 |
500 |
|
| 7 |
40 |
100 |
50 |
|
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.
What’s next?
Like the title of this article states, this is just an “introduction” to
the IF statement, but it should get your newbie toes wet.
In a future issue of TechTrax, I will cover 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!
Linda
http://personal-computer-tutor.com
|
