Advanced Excel Tutorial: Think Like a Programmer and Use Nested IF Statements

If you get to know Excel well enough, you will start to use some sophisticated strategies that are very capable of solving complex problems. In fact, you will even be applying some of the same logic used in computer programming to your spreadsheets.

This doesn’t necessarily mean building VBA programs or macros. Even functions you can design within Excel use a lot of the same techniques that software developers employ in regular practice, and they can take on some heavy duty data crunching that you may have thought would require some more specialized software.  In fact, if you get advanced Excel functions down and really find you enjoy the problem-solving involved in this, a course on programming fundamentals might be a way for you to develop your skills and your career

One of the best examples of a function that has more to it than meets the eye is the IF function. This tool allows you to build out the same kind of if…then statements used extensively in software development, if you know how to use it. So read on to learn to use this great feature, and if you would like to understand more about how functions can be applied to solve complex problems, dig in to an Excel course that surveys multiple functions.

Step 1: Understand the Basic IF statement

When you plug a single IF statement in to a cell, you are asking excel to return a value based on whether specific criteria is met. You can determine the values to be displayed for both a true statement and a false one. The basic format is pretty simple. It is this:

= IF(Criteria, Value_if_true, Value_if_false)

And you can take a look at a straightforward example to illustrate this. Let’s build an IF formula to put in the F column of the following table:

For rows two and three, we will simply want to know whether the sum of the values in columns C and D equal the value in column E. So in cell F2 you can input the following:

=IF(C2+D2=E2,”Okay”,”Try again”)

Since 2+2 is obviously not five, you will see the response come back “Try again.” But if you copy the formula to cell F3, you will see that it uses the “Value_IF_True” response to give us “Okay.”

If you are at or entering an advanced level with Excel, that simple function will likely be review.  If not, you may want to take a step back and get a refresher in a course that will give you an Excel workout before you move forward.

Step 2: Using nested IF functions

You can build a basic algorithm using IF statements, which is where this function begins to operate a lot like programming logic. A good way to start learning this is to think about assigning letter grades to students scores. Start with the following list of scores:

You need to apply a letter grade to each of these. Obviously, this would be simple to do by hand, but let’s say you want to put something in place that you can use to automate it for a very large number of students. In this case, you can connect IF statements, structuring the formula so it will keep trying statements until the criteria matches.

This has everything to do with the Value_if_False part of the statement. You want to structure your function so that each IF statement stands in for this value, and that the program can keep trying options until it finds a true value or gets to the end of the chain.  Here is the formula you use, broken down in lines so you can more clearly see its logic:

=IF(A1>=89,”A”,

IF(A1>=79,”B”,

IF(A1>=69,”C”,

IF(A1>=60,”D”, “F”))))

When you type this in to cell B1, you want it all on one line, so remember to use a space instead of a hard return between each if statement. Also, notice that the function requires four closed parentheses at the end, one for each if statement. Keep in mind that without the correct number of parentheses, the function will fail. This is one of the most common reasons, in fact, that a function does not work properly in excel.

You should see the correct value returned for the A1 score if you have structured this statement correctly. You can then copy the formula through the rest of the rows you need to assign a letter grade.

Step 3: Using the nested IF statement with other functions

So you used the IF statement to determine grades. Now let’s say you have been tasked with assigning a different point score to those grades. You could rewrite your IF statement, but you can also use the IF statements you created as part of a larger formula. Here you can embed it within a vlookup.

Add the following table to your spreadsheet, so you can use vlookup on it to find the point scores corresponding to each letter grade:

Once you have that completed, you will simply embed your IF statements in to the vlookup. With a placeholder for your IF statement chain, it will look like this:

vlookup( [IF_Statements], D1:E5, 2, TRUE)

and with the IF components included, it will read as follows:

=vlookup(IF(A1>=90,”A”, IF(A1>=80,”B”, IF(A1>=70,”C”, IF(A1>=60,”D”, “F”)))),D1:E5, 2, TRUE)

If you completed this correctly you should now have the scores corresponding to the grades in your column. Move on to apply this function and other advanced level techniques, and if you feel you are ready, you can start on a course covering Excel VBA, where programming skills will really start to come in to play!