Excel If Then Statements: Getting the Most Out of the IF Function

excelifthenWhen you really get to know the advanced functions in Microsoft Excel, one function that you will no doubt begin to use frequently is the IF function.  In automating your calculations and data manipulation, the IF function will give you some of the same features that professional developers often use to design programs. In other words, you will essentially use If…then logic statements to build powerful processes that solve your real-life business problems.

If you would like to begin learning this and other advanced features of Excel, a good place to start is an advanced online course in Excel. In this guide, we will learn more about what the IF function does, how to use it, and how to get the most out of it by including it within larger formulas.  

Background on the IF function

Like most of the functions you are familiar with in Excel, the IF function delivers an output to a single cell, as opposed to a range of cells (as would be the case for an array function).  In using it to process multiple entries throughout a spreadsheet, you construct it for a single cell and then copy it down to additional cells in a range.

In essence, the function processes a logical test and delivers a specified value based on whether that test is true or false. This guide will go on to explain what that means in greater detail.

Creating an if function

The syntax of an IF function is pretty simple, and really only becomes more complex when you begin to string functions together. In a simple form, the structure is this:

=IF(Logical Test, Value if True, Value if False)

And here is an example to show it in context.

A

B

C

1

500

250

=IF(250+B1>=A1,“OK”,“Low”)

2

220

In this case, the IF function goes in cell C1. The logical test consists of everything prior to the first comma within the function. So here, you are determining whether the result of 250 plus the value in B1 is equal to or greater than the value in cell A1.

Following the logical test, and after the first comma, you tell the function what value to return if the logical test is true. In this case, that will be a text string, which means you need to put it within quotation marks. So here, a true result will return the text “OK”.

After the return value for the true case, you need to supply the return value for a false result.  In this instance, that value will be another text string. Therefore, the procedure will be the same.

And finally, the function is completed by placing a closed parenthesis at the end. When you hit the enter key, your result should look as follows:

A

B

C

1

500

250

OK

2

220

Note that a common error you may see with the IF function is the #NAME error. When this comes up, it is because you have included text string values without placing commas around them.  A simple fix.

Applying the IF function to a range of values

In most cases, you will want to apply IF functions to a range of cells rather than just one.  This, of course, can be accomplished easily by copying the function. However, before you copy the function, make sure you have it prepared to work the way you want it to.  This means making sure you have defined absolute references where necessary.  Again, we can return to the previous example to show how this is done:

A

B

C

1

500

250

=IF(250+B1>=A1,“OK”,“Low”)

2

220

Let’s assume you want to apply this logical test to additional numbers, listed in column B, and you want to continually use 500 in cell A1 as the number for comparison. In that instance, you want to modify the IF function in cell C1 to contain an absolute reference to cell A1 before copying it down to the following cells.

As you may know from previous work in Excel, absolute references are set off by the dollar sign ($), and they need to be defined for both the row and column. Therefore, here we will change our function as follows:

=IF(250+B1>=A$1, “OK”,“Low”)

Alternatively, you can set an absolute value for both the column and row (i.e. $A$1) However, it is only necessary in this instance that the row remains constant.

If this is at all unclear, or you do not feel confident that you can define absolute references within your own Excel spreadsheets, you may want to revisit some Excel basics in an online course covering Excel Essentials.

Your next step is simply to copy your initial function down to the additional cells you want to apply it to.  In case you need a refresher on how to do this, the process is as follows:

1. Click on the cell containing the formula you want to copy down.

2. Hover the cursor over the lower right corner of the cell. Your cursor should make a plus sign shape when it’s in the right position.

excelifthen

3. Hold down the left mouse button and drag down to the additional cells you want to fill.

In this example, you are only copying the formula down one cell. However, in your own work, you can use this procedure to copy to as many additional cells as you need.

Combining multiple IF functions

On their own, IF functions can be very helpful.  However, when you begin nesting them inside of larger formulas, along with other functions, you open up many new possibilities.

Particularly with the IF function, it can often be helpful to chain multiple instances together.  This brings functionality to Excel that is very similar to If…then programming logic, and it is a great option for problem solving.

Let’s alter the original example to demonstrate how this works. Suppose you want to perform a number comparison with different outcomes depending on whether the result of an equation is higher than, lower than, or equal to a given number.

We can represent this process with a simple flowchart, as follows (please excuse inconsistencies with standard programming flowcharting syntax):

So, if you follow the logic of this chart, you’ll see that we we can use two IF functions to deliver three possible outcomes. The second of these functions is set off by a “false” outcome for the first. If you apply that to an Excel formula, it takes the following form:

=IF(Logical test 1, Value if true, IF(Logical test 2, Value if true, Value if false))

So for the specific problem in this example, the formula is this:

=IF(B1+250=500,“OK”,IF(B1+250>500,“High”,“Low”))

Notice that the second IF function is taking the place of the “value if false” result for the first IF function. If you like, you can add an additional row to the example spreadsheet in order to show all three results.  Type the modified formula in to the first cell and then copy it down to the other two.

Since this process is so similar to the practice of building If…then functions in programming, you may benefit from an online course in programming fundamentals for non programmers.

Additional formulas with multiple IF functions

There are many instances where you can use IF functions as part of a larger formula.  These can involve multiple functions of different types, as well, rather than just a chain of IF functions. Virtually, the only limitation you will come up against is your own knowledge of available functions in the Excel program.  So, if you want a thorough understanding of the many functions, you can take an advanced Excel course that focuses on the functions and formulas in Excel.

Again, we can build some alterations in to this guide’s original example to show an example of a complex, multi-function formula.

Suppose we want to determine whether the result of an equation falls inside or outside of a given range. Rather than comparing the result to 500, we will compare it to the range 500-700.

To set up the spreadsheet for the formula, you simply need to add the upper limit of your range in cell A2, as follows:

A

B

C

1

500

250

2

700

220

The formulas, of course, will go in the C column.

Now, in order to compare the equation’s result to a range, we will need to use the AND function, as an addition to the IF function.

Here we will determine whether 460 plus the value in the B column is within the range in A1-A2. If it falls inside of that range, we will return the text string “OK” and if it is outside of the range, we will return “outside.” We will use the AND function, in addition to the IF function, complete this, and to prepare for this, we will need to think of it in slightly different terms. Essentially, we want to structure the logical statement as follows:

IF A+B>C AND A+B<D

THEN “OK”

ELSE “Outside”

The syntax of the AND function makes this just a touch more confusing, so let’s start by tackling that.  Ultimately, the function’s structure looks like this:

=AND(Logical test 1, Logical test 2,[…])

The result of this function will be TRUE or FALSE, depending on whether a value or  statement matches all criteria. It can contain more than two logical tests, as necessary.

Here, we are applying the function to see whether our equation fits within the range specified in cells A1-A2, so it will look like this:

And(460+B1>A$1, 460+B1<A$2)

Now, recall that this is not the extent of our formula.  Instead, it merely operates as the logical test within our larger IF function.  So our next step is to embed this function inside of the IF function that will give us our return values.

=IF(And(460+B1>A$1, 460+B1<A$2),“OK”,“Outside”)

When you plug this in to your spreadsheet and copy it down, you should see the following result:

Pulling it all together

With the sections, and the corresponding examples in this guide, you have learned how to do a simple IF function, chain multiple IF functions together, and use an IF function along with the AND function.  Now let’s do a final modification to our example to use all of the information in one shot.

This will build on the previous example, changing the outcomes so that there are three possibilities, rather than two.  So now, we will want to return the text string “OK” if the result of our equation is within the 500-700 range, the value “high” if it is above that range, and the value “low” if it is below it. Again, let’s add a third value in column B, so that we can see all three possibilities take shape.

A

B

C

1

500

250

2

700

220

3

20

We want the AND function to remain the same.  The change we will make is to add a second IF function to take the place of our “value if false” result within the initial IF function.  Here is the logic the formula will follow:

If A+B>C AND A+B<D

Then “OK”

Else If A+B>D

Then “High”

Else “Low”

Using the data in the spreadsheet, you can then complete the formula, which should look as follows:

=IF(AND(B1+460>A$1,B1+460<A$2),”OK”,IF(B1+460>A$2, “High”,”Low”))

Copying this down from your first row to the remaining ones, you should have one of each result.

Wrapping up

While the example built in this guide was made to be simple and adaptable, you hopefully see that Excel has the capability to solve many real-world problems by applying If…then logic. Whether used by itself, in a chain, or in combination with other helpful functions, the IF function is a great addition to your toolbag as you get to know Excel.