At times, my approach toward writing is somewhat wacky and even downright silly. That’s especially true in regard to this tutorial. So, not only will you learn how to use If statements in Excel 2003; you’ll also enjoy yourself in the process. In other words, I won't say things like....

To make your life a little simpler, I created a blank template that’s designed to help you follow along with each of the examples. You can download the template at your convenience. This tutorial is divided in to three brief sections. We’ll examine the template layout in part one. In part two, we’ll use an If statement to display the status of a company’s budget. Finally, If a company has gone over budget, we’ll learn how to display the actual over budget amount by using an additional If statement. As an added bonus, I also included a great video by Clayton Lock of ‘The Tutorial Library’. Mr. Clayton has been creating Excel based applications for over eight years and is extremely skilled in utilizing If statements. By the time you’ve completed this tutorial, you’ll be a bona fide Excel If statement inductee (I’ll try to make this as painless as possible).

The Excel table below contains the following columns:

• Company Name
• Budget for the Year
• Spending for the Year
• Budget Status
• Over Budget Amount

We need to determine whether or not a particular company has gone over budget. If a company has gone over budget, we want the words ‘Over Budget’ to display in the corresponding cell in column D. Conversely, if a company hasn’t gone over budget, we want the word ‘OK’ to display in the corresponding cell in column D (this approach is a bit simplistic but I’m a simple minded person – that didn’t come out right). Also, if a company has gone over budget, the over budget amount should be displayed in the corresponding cell in column E. For instance, Company A has gone over budget by fifty cents according to the table below; that information needs to be reflected in cells D2 and E2.

Quick If Statement Facts to Remember:

• If statements are used to return a value of “true or false”.
• If statements can be nested.
• When creating an If statement, text based responses or text strings must be placed within quotation marks. Calculation operations or number based responses aren’t placed within quotation marks (this will be explained in more detail later).
• If statements have three arguments.

The arguments are:

1. The Logical Test argument – The Logical Test argument is Excel‘s way of allowing us to ask a question. In this case, our logical test or question is ‘Is the value in column C (Spending for the Year) greater than the value in column B (Budget for the Year)?
2. The Value If True argument – The Value If True argument is Excel’s way of asking “What response do you want to display if the answer to your question is true?”
3. The Value If False argument – The Value If False argument is Excel’s way of asking “What response do you want to display if the answer to your question is false?”

For example, if you entered the following If statement into cell D2 of the template:

=IF(C2>B2,"Over Budget", "OK")

Upon pressing the enter key, cell D2 would exhibit the words “Over Budget” as displayed in the example below. This would be correct since Company A is over budget by fifty cents. Try if for yourself!

If you changed the value in cell C2 from $15.50 to $15.00, cell D2 would display the word “OK”. This would be correct since Company A would no longer be over budget (this example is not displayed in the table below).

In order to gain a better understanding, we’ll examine the previous If statement in more detail. It’s not necessary to re-enter the If statement, this is merely an explanation of the statement.

1). The beginning of the If statement is entered as follows (the beginning parenthesis always follows the statement IF):

=IF(

2). Thereafter, the Logical Test or question portion of the statement is entered:

C2>B2,

This segment of the statement is actually asking the question, ‘is the value in cell C2 greater than (>) the value in cell B2? The comma at the end of the segment tells Excel to go to the next step.

3). Next, the Value If True portion of the statement is entered:

“Over Budget”,

This part of the statement instructs Excel to display the words “Over Budget” if the Logical Test or question portion of the statement is true. Again, the comma at the end of the segment tells Excel to go to the next step. Because the words “Over Budget” represent a text based response or a text string, you must place the words within quotation marks.

4). Finally, the Value If False portion of the statement is entered (remember to add the closing parenthesis):

“OK”)

This final segment tells Excel to enter the word “OK” if the Logical Test or question portion of the statement is false. Again, because the word “OK” represents a text based response or a text string, you must place the word within quotation marks.

Now that you’re acquainted with how to use If statements in Excel, this would be a good time to ingrain the concepts. As I mentioned earlier, the following video was created by Clayton Lock of the ‘The Tutorial Library’ . While his approach is a bit different from my own, he does an excellent job of explaining the process (he's gonna steal all of my business if I'm not careful). I’m sure that you’ll glide through part three of this lesson after viewing this tutorial.

As we discussed earlier, if a company goes over budget, the actual over budget amount should be displayed in the corresponding cell in column E (Over Budget Amount).

For example, if you entered the following If statement into cell E2 of the template:

=IF(D2="Over Budget",B2-C2,0)

Upon pressing the enter key, cell E2 would exhibit the amount of “-$0.50” for Company A as displayed in the example below. This would be correct since Company A is over budget by fifty cents. Try if for yourself!

Because I believe in being anal, I mean thorough, we’ll examine the previous If statement in more detail as before. Again, it’s not necessary to re-enter the If statement, this is merely an explanation of the statement.

1). The beginning of the If statement is entered as follows (the beginning parenthesis always follows the statement IF):

=IF(

2). Thereafter, the Logical Test or question portion of the statement is entered:

D2=”Over Budget”,

This segment of the statement is actually asking the question, ‘does cell D2 contain the words “Over Budget? The comma at the end of the segment tells Excel to go to the next step.

3). Next, the Value If True portion of the statement is entered:

B2-C2,

This part of the statement instructs Excel to subtract C2 from B2 if the Logical Test or question portion of the statement is true. Again, the comma at the end of the segment tells Excel to go to the next step. Because B2-C2 is a calculation operation or a number based response, quotation marks aren’t needed.

4). Finally, the Value If False portion of the statement is entered (remember to add the closing parenthesis):

0)

This final segment tells Excel to assign the value of zero to the current cell if the Logical Test or question portion of the statement is false. Again, because 0 is a calculation operation or a number based response, quotation marks aren’t needed.

To see the Budget Status and Over Budget Amount for the remaining companies, simply copy the If statements from cells D2and E2on to the corresponding cells below. For instance, copy the If statement from cellD2on to cell D3; copy the If statement from cell D3on to cell D4, and so forth.

When you’re finished, your template should resemble the following:

You’ve primarily concurred the basics on how to use If statements in Excel 2003; awesome! If you have any questions or concerns, fill free to leave a comment and I’ll respond promptly.

Thanks!