Introduction to IF Statements in Excel

by Linda Johnson, MOS

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 Second
Number 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:

=IF(A2>B2,"yes","no")

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:

=IF(A2>B2,1,2)

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:

=IF(A2>B2,"yes","")

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 Second
Number Number Formula  
2 50
13 20 =IF(A2>B2,"yes","no") 0
3  
12 2 =IF(A3>B3,1,2)
4  
44 325 =IF(A4>B4,"yes","")
5 =IF(A5>B5,"contact this salesperson","give this one a
100 10 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 Second
Number Number Result  
2 50
13 20 no 0
3 12 2 1 
4 44 325    
5 contact this
100 10 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!

unctions, some of the most useful tools in Excel, let you perform a variety of tasks automatically
within a spreadsheet. They return different results depending on certain circumstances, such as
whether a particular condition is true or false.

Excel includes a number of these functions that you can use to not only to make working with
spreadsheets faster, but also to help ensure your accuracy. These functions let you code the
decision-making process inside your worksheets rather than relying on the person who enters the
data to make the choice and enter the result.

To see how a decision-making function can work, consider a situation where you charge a flat
fee for shipping up to a certain value of invoice and a percentage of the invoice value above that.
Instead of manually checking to see whether a flat fee or percentage value is appropriate and
then entering the amount into a cell, you can have Excel do the work for you.

Using a function you can point Excel at the invoice total and it will determine whether to use the
flat fee or calculate a percentage value. Later, if the invoice total changes, the function will
recalculate and the shipping cost will be reassessed based on the new information.

There are a number of Excel formulas that work in a similar way by allowing you to create one
function to handle multiple situations. In this article we will look at three of them, the IF
function, the CHOOSE function and the VLOOKUP function.

IF Function

The IF function is useful in the scenario mentioned earlier. Let's

suppose we have a situation where the cost of shipping a product
is $4.95 for any amount of product up to and including $20.00 in
value and then 25 percent of the invoice price over that amount.
You can construct the IF function in such a way that it tests the
amount of the invoice and determines if it is $20.00 or less. If it Figure 1: This IF function
is, the flat fee goes in the cell, and if not a percentage value goes calculates shipping for our
in. The IF function does the calculation for you, assuming the shipping costs scenario.
invoice total is located in cell G15, as in the example shown in (Click for larger image).
Figure 1.

The function looks like this: =IF(G15<=20,4.95,G15*0.25)

The syntax of the function reads: =IF(test, calculation or value if true, calculation or value if
false)
There is one thing to be aware of when you use an IF function and that is that it will only operate
if you have a test that can be answered either True or False, or Yes or No. In simple terms, this
means that Excel can answer the question "Is it raining outside?"
because the answer is either True or False, Yes or No. However,
Excel cannot answer the question "What is the weather like
outside?" because this cannot be answered as True or False, Yes
or No. In our scenario, the question "Is the invoice total $20 or
less?" can be answered Yes or No.

IF Function with Multiple Tests

It's possible to specify multiple tests for an IF statement. For

example, consider in our previous example that there is an Figure 2: This more complex IF
exception to the shipping cost calculation in the case of shipping function uses an OR operator
to make two tests that
to California – California shipping is always $4.95, regardless of
determine shipping cost.
invoice cost.
(Click for larger image).

In this situation, we need to check not only the invoice amount

but also whether the parcel is being shipped to California. This
IF function will make the calculation assuming that a two digit state abbreviation is located in
cell G7:

The function looks like this: =IF(OR(G7="CA",G15<=20),4.95,G15*0.25)

This function says: if the item is being shipped to California or if the invoice total is $20.00 or
less, then the cost is $4.95 otherwise it is 25 percent of the invoice total. In this case we have
used the OR operator to manage the test because either or both the tests must be true. In other
circumstances you may have a need for a test where both situations must be true and in that
situation you would replace the OR operator with the AND operator.

CHOOSE Function

The CHOOSE function is an interesting function that lets you select an item from a list of items.
One simple way to see it at work is to use it to convert a date to a day of the week -- something
that Excel can't do.

When you use the WEEKDAY function on a date in Excel, it returns a number representing the
day of the week in the range from one to seven representing the days Sunday through to
Saturday. The problem with this is that most times, instead of a day number, you'd really prefer
to see a day name. In this case, you can combine the CHOOSE function with the WEEKDAY
function to convert the number into a day name.

This function applied to a date in cell A1 will return the corresponding day of the week it
represents:
The function looks like this:

Figure 3: The CHOOSE

function can be used to return
the name of a day from a date.
(Click for larger image).
=CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

In this case the CHOOSE function takes the day number that the WEEKDAY function returns
and turns it into the corresponding day of the week. The first variable entry in the function is the
number to use and, following this are the series of values that the function will return depending
on the first variable value. The CHOOSE function can return up to 29 different values.

VLOOKUP

One of the most useful functions, VLOOKUP lets you look up a value in the first column of a
table and to return data from the same row of the table but a different column.

For example, consider a situation where you have a table of data containing currency conversion
rates for a series of different currencies. Instead of having to scroll through the table looking for
the required conversion rate every time you need to calculate a conversion, you can have the
lookup formula do the work for you.

The best way to approach this task is to select all the data in your table and name that range by
choosing Insert > Name > Define and type a name for it. This
lets you use the table name in your lookup function rather than
having to remember the range details each time you need to use
it. This step isn't required but it will make your work easier.

You can now look up a value stored in a cell in the first column
of the table and return data from a different column. Table
columns are numbered with 1 being the leftmost table column Figure 4: Naming a data table
and then 2, 3 etc., as you move to the right. makes creating a lookup table
easier.
If your table is called CurrencyConversions, and if you're (Click for larger image).
looking up a conversion rate for a currency named in cell C12
and converting it into the currency listed in the 3rd column of the table, this function will do the
work:

The function looks like this: =VLOOKUP(C12,CurrencyConversions,3)

In the example worksheet in Figure 5, this formula will make a conversion using the currencies
entered in cells C12 and F12 and the amount in cell B12:
The function looks like this:
=VLOOKUP(C12,CurrencyConversions,IF(F12="AUD",2,IF(F12="CAD",3,
(IF(F12="EUR",4,IF(F12="GBP",5,6))))))*B12.

Notice here that a series of IF functions have been nested inside

each other to convert the currency name from cell F12 into a
column number so we can extract the conversion rate from the
table.

Figure 5: This VLOOKUP

When using the VLOOKUP function, you should note that it is
function, combined with some important that the values in the first column of the table are
nested IF functions, makes a sorted in ascending order for it to work.
currency conversion.
(Click for larger image). I've given you a brief introduction to some possible applications
for the functions IF, CHOOSE and VLOOKUP. You will find
that once you see their potential, you have lots of other ways you can apply these functions to
your work every day.