IF function in Excel Part 2

A couple of days ago, I received a question from a reader asking how to use the IF function in Excel for a particular scenario he needed to do at work. I thought other people might benefit from having a short tutorial on this, so I’m providing it here, divided into a few parts.

The first part (here) will go into the basic IF statement, and subsequent parts, such as how to use nested IF functions with other logical operators, will describe how to use it in slightly more complicated scenarios or with additional features. For example, the reader’s question had to do with using an IF statement given two conditions, e.g. if a number falls between two other numbers. I address those in the separate post in the link above.

But, let’s go over the basic IF function here, first.

Let’s say that you have a series of numbers in Excel like this that represent sales figures that 10 of your salesmen have made this quarter, in column B:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A4 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

	Sales
Salesman A	87925
Salesman B	100000
Salesman C	145000
Salesman D	200750
Salesman E	178650
Salesman F	99555
Salesman G	147000
Salesman H	213450
Salesman I	122680
Salesman J	92500

Ok, now back to the tutorial.

Next, suppose that you give them a bonus commission of 12% if they’ve exceeded a threshold value, say, $100K. Rather than going through each sales figure yourself and manually checking to see if that number is greater than $100K, and if so, calculating the commissions in another column (column C in this case), you can use the IF function to do this for you.

First, let’s put the 12% commission percentage in B1 so we can change it later if we want to, and the $100K figure in B2 for the same reason:

Here’s the syntax for the IF statement in Excel. You put the function in the cell or cells where you want the result. The IF function takes three inputs:

=IF(condition to test for, what to do if the condition is true, what to do if the condition is NOT true)

The first two arguments are required, and the last one is optional. If you leave out the part about what to do if the condition isn’t true, then Excel will return “FALSE” automatically. This can be useful in certain situations. In our case, just to avoid confusion, we’ll put in a specific action for what to do when the condition is not true.

So, in our case, we’d start in cell C5, where we want to calculate the salesman’s bonus, if any. The IF statement would be written as.

=IF(B5>$B$2, B5*$B$1, “No bonus”)

Like this:

Here’s what that says in plain English: If the number in cell B4 is greater than the number in cell B2, then multiply B4 by B1. If not, then write “No bonus” in the cell instead. Once you enter in this formula, the result will be printed in the cell. In this case, Salesman A didn’t make enough sales, so “No bonus” appears in the cell.

By the way, the dollar signs by $B$2 and $B$1 are simply an ugly way that Excel uses to keep a cell “fixed”. Later on, we’ll copy and paste the IF function we wrote in cell C4 and the formula will automatically adjust for each row. For example, if we paste the formula in cell C6, the formula will automatically change to:

=IF(B6>$B$2, B6*$B$1, “No bonus”)

If we hadn’t put “$” by B2 and B1, then these would have automatically adjusted as well when we pasted the formula in another cell. Of course, you could avoid all this and put 100,000 and 12% into the IF statement directly, but if you needed to change these parameters later on, it’d be much harder to do.

After copying and pasting the IF formula down the column, here’s our result:

And voilá. It’s done. Now you can format all the numbers and alignments nicely so it’s more readable.

If you have several rows of data, the IF statement is a lot easier a tool to use than going through each case manually! In an upcoming post, I’ll try to describe some other nifty things you can do with the IF statement.