Excel Nested IF Function And How to Apply It In Your Excel Worksheets

Excel is far more than a mere spreadsheet application that allows you to store and manipulate columns of data. When Microsoft launched their applications, they included the power of visual basic for applications, known as vba, which allows a user access to a powerful set of visual basic programming commands within their office applications. The if statement within excel is an excellent example of how the power of vba can transform a spreadsheet into a powerful application.

This tutorial will show you how the if statement functions as was as how to nest if statements to create powerful calculations within your Excel spreadsheet. This tutorial assumes that you have a solid foundation in using Excel spreadsheets. It assumes that you know how to create a spreadsheet, how to add columns of data. It also assumes you have a basic understanding of how to manipulate the data within an Excel spreadsheet. If you want to get the solid foundation you need to follow this tutorial then Excel Course (Basic and Advanced), will teach all you need to know.

Before we begin a practical example of the nested if statement in excel, you need to know how the if statement works to begin with.

How the if function works

The if function is a function that allows you to compare a value and then to manipulate that value depending on the value. The easiest way to understand the if function is to compare the function to a real life if statement. If it rains we’re ordering in but if the weather is good we’ll go out. The if function in programming works exactly the same way as our real life statement. The program evaluates the condition – is it raining – and depending on whether the answer is true of false, the if function then performs a set of instructions.

For a solid foundation in functions for excel and how they work, the microsoft excel level 1 foundations course will teach your all you need to know quickly.

A nested if statement is simply an if statement that is declared within another if statement. Using our raining example – let’s add another if by saying that if we are going out then if it’s afternoon we’ll go for a picnic but if its evening we’ll go for pizza. Excel evaluates the statement in exactly the same as we would. It would first check if it’s raining. It would then move onto the next if statement and check whether it’s afternoon or evening. The most important thing about using an if statement in excel is to ensure that the program logic is indeed logical.

So let us create an example in excel which we can use to demonstrate how the nested if statement works.

Let us assume you have a spreadsheet of employees. For our example we need to work out the tax due for each employee. The tax depends on whether the employee is tax exempt or not and also depends on the income that each employee receives.

Our initial spreadsheet would look like this:

Now let us add our first if statement. We are going to tell the spreadsheet that if the employee is tax exempt then the taxes due are zero, but if not then the taxes due will be 50% of the total income. The if statement we would need to achieve this would look like this:

Excel provides the syntax needed for the statements you are creating or using. For more about how syntax works and how to use syntax within excel, Mastering MS Excel will teach you the basics of excel in a weekend.

If we copied the above formula to each of the cells the output of the spreadsheet would look like this:

But we haven’t quite finished yet because we also need to work out the different taxes due depending on the income the employee receives. For our example, we will assume that employees who earn more than fifty thousand dollars must pay tax at a rate of 50% but employees who earn less than fifty thousand must pay tax at a rate of 25%.

To set up this statement, we could express the statement as follows in ordinary English: If the employee earns more than fifty thousand then he pays tax at a rate of 50% but if he earns less than fifty thousand then he pays tax at a rate of 25%. By expressing your if statements in regular English, you can easily figure out what the statement in excel should look like.

The above statement works as follows. When Excel gets to the first if, it checks to see if the employee is tax exempt or not in column B3. If the answer in column B3 is yes then the program finds that the condition is true and it applies the formula C3 * 0 so that the person does not pay tax.

If however the employee should pay tax, then it moves onto the next statement. The next statement contains an if so the program evaluates whether the employees income contained in column C3 is greater than fifty thousand dollars or not. If the employee earns more than fifty thousand dollars then the program applies the first statement i.e. C3*0.5 which calculates a tax rate of 50%. If the employee earns less than fifty thousand then the program applies a tax rate of 25% using the formula C3 * 0.25.

Our spreadsheet containing the nested if statement to calculate taxes due at the variable rates would look like this:

You could add further nested if statements within those if statements as well, which will allow you to create extremely complex calculations.

As a final example. Let us add another if statement to our example. Let us suppose that if an employee earns more than eighty thousand dollars then his tax rate will be calculated at sixty percent. But if an employee earns less than eighty thousand but more than fifty thousand then his tax rate is calculated at 50%. And if an employee earns less than fifty thousand dollars then his tax rate will be calculated at 25%. This statement will contain three nested if statements and it would look like this:

The above function would result in our spreadsheet looking like this:

So let us take a quick look how the program evaluates the above statement to work out our tax rates for us. When the program begins the statement it starts with the first if statement. It checks whether the value in column B is “yes” or “no”. If the answer is yes, then the program multiplies the Total Income by zero and returns the answer and stops the statement. If the answer is no then the program moves onto the next part of the statement. In the next part of the statement the program once again evaluates the if logical test. If the program finds that the total income in column C is greater than fifty thousand dollars then if moves onto the next statement and here again it evaluates the if statement.

The most important thing to realize about the nested if statement is that as soon as the program returns a value, it exits the function, so it’s important to make sure you statements make logical sense. For more advanced excel programming and formulas, you can fast track your learning with the Microsoft Excel beginner and advanced training.