This lesson is about nesting logical functions. In Excel, you can nest functions together to allow them to do more complicated work.
First let’s look at the anatomy of a function. All functions start with an equals sign, then have a function name. After the parentheses, functions are made of arguments, separated by commas. Arguments in brackets are optional arguments.

When you nest functions together, the function you enter as an argument must return the same kind of information as the argument in the formula.
In our IF statement, the first argument is a logical test. A logical test returns TRUE or FALSE. So a few examples of functions you can nest in your IF function to replace the logical test are AND, IF, OR, and NOT, because those are all functions that return TRUE or FALSE. Your optional arguments, value if true and value if false, return numbers or text. They can be replaced by almost any function.

Let’s look at an example of nesting IF statements together. In our last example, evaluating the color of the star with an IF statement, we were able to return “Blue” if the star was blue, and “Other” if the star was not blue. In this case we would like to evaluate the star and return either “Blue”, “Red”, “Green”, or “Other”. We will do this by nesting IF statements together. Our IF statement will begin with =IF, and then we’ll perform a logical test. In this case, the logical test will be is this star blue? Our value if true will be “Blue”. But if our star is not blue, we’ll need to do another logical test to see if it is red. We’ll start another IF statement, and our logical test will be is this star red? Our value if true would be “Red”. If the star was not red, we would need to do another logical test to see if the star was green. So our value if false would be another logical test. We’ll start another IF statement, and our next logical test will be is this star green? Our value if true will be “Green”. Our value if false will be “Other”. Once we’ve completed this, we’ll have to close parentheses on each of our IF statements. And this is what they would look like all together. In this case, because our star was yellow, our nested IF statements would return “Other”.

Now let’s look at some examples in Excel. Let’s start with an example of nesting IF statements together.

In our data we have a salesperson, their title, the amount of sales they’ve had for the year, and we want to give them a sales rating. Our sales rating is “Great” if their sales were greater than 75000, “Good” if their sales were between 50000 and 75000, “Fair” if their sales were between 25000 and 50000, and “Needs Improvement” if the sales were between 0 and 25000. Because we need to do more than one logical test to fit the sales into the correct sales rating, we’ll nest IF functions together.

Before we start, let’s talk about the order that we’re going to do our logical tests in. When you’re nesting IF functions together, you always want to start with the most exclusive group. It’s kind of like you have a fish tank with four different sizes of fish and you would like to separate those fish into four different tanks according to size. If you used your largest net first, you would catch all of your fish and would not be able to separate them out into size. If you start with your smallest net, you would be able to catch your smallest fish first. When they’ve all been caught, you can move on to one size larger, and you’ll catch the fish that are one size larger, and so on.

Let’s start our logical function. We’ll start in D3 by typing =IF and opening parentheses. Our logical test will be is Sales greater than 75000? I’ll use the left arrow to arrow one to the left to select C3, then type >75000. I’ll type a comma to move on to the next part of the formula. The value if true is “Great”. Because we’re returning text, it needs to be in quotes. I’ll type a comma to move on to the value if false. If our sales are not greater than 75000, then we need to do another logical test to see if our sales are greater than 50000. So for the value if false, I’ll type IF, and open parenthesis. My logical test will be is C3 greater than 50000. I’ll arrow one to the left to select C3, then type >50000. I’ll type a comma to move on to my value if true, which will be “Good”. That has to be in quotes. Type a comma to move on to the value if false. If the sales were not greater than 50000 then we’ll have to do another logical test to see if the sales were greater than 25000. I’ll type IF, open parentheses, and do my next logical test. That logical test will be is C3>25000. I’ll type a comma to move on to my value if true. My value if true will be “Fair”. Be sure to put that in quotes. Then I’ll type a comma. Now for my value if false, in this case if the sales are less than 25000, then they are in the “Needs Improvement” category. So I don’t need to do another logical test. I can just enter “Needs Improvement”. Be sure to put that in quotes. Now I’ll close my parentheses for that logical function, and notice I have two more sets of parentheses open. I also need to close those sets of parentheses. When I’m finished, I’ll press the Enter key on my keyboard.

So the sales for Judy Williams are Great. I’ll double click on the fill handle on the cell to fill my formula down.

Let’s look at another example of nesting logical functions together. In this case if a salesperson has the title Rep and their sales are greater than 75000, they get an extra 2500 bonus. We want our formula to return 2500 if the title is Rep and the sales are greater than 75000. If both those criteria are not met, we don’t want to return anything. We know that we need to use an IF statement in this formula because we want to return something other than TRUE or FALSE. Because we have two criteria that need to be met, we’ll be using an AND statement, too.

So for this formula, we’ll start with =IF and open parentheses. Now, our logical test is actually two logical tests – is the title Rep? and are the sales greater than 75000? So for our logical test, we’ll enter an AND statement. I’ll type AND, and open parentheses. Notice that the screen tip below the formula turns into the screen tip for an AND function. Our first logical test will be is title equal to rep? So I’ll type B3=”rep”. Type a comma. Our second logical test will be are sales greater than 75000? So I’ll select C3 and type >75000. We could put in more logical tests if we needed to, but we only have two criteria, so we’ll go ahead and close the parentheses on the AND statement. Now you can see that our screen tip below is for an IF statement. Notice that logical test is bold in my screen tip. That means I’m still on the logical test part of my formula. I need to type a comma to move on to the value if true argument. The value if true will be 2500. We’ll type another comma to move on to the value if false. Here’s something we haven’t covered before – if you do not want your formula to return anything, you can just open and close quotes without putting anything in between. Now that the last argument in our formula is finished, we’ll close parentheses and hit the Enter key. For our first example, even though the sales criteria was met, the title criteria was not met because our salesperson is a manager. If we double click on the fill handle to fill this down, you can see that we did have some salespeople who met the criteria and will receive the bonus.

Now we’ll look at one more example of nesting IF statements together. In this case we have a more complicated bonus structure. For each salesperson, if their sales amount is greater than or equal to 100000, they will receive a bonus that is 15% of their sales. For each salesperson whose sales are greater than or equal to 40000 but less than 100000, their bonus amount will be 12% of their sales. And for each salesperson whose sales amount is less than 40000, they will receive a bonus that is 7% of their sales.

So to calculate the bonus for each of our salespeople, we’ll start with =IF and open parentheses. Our first logical test will be is the sales amount greater than or equal to 100000? Our value if true will be the bonus amount times the sales. In this case our bonus amount is 15%. Because I have 15% already in a cell, I can select that cell as part of my value if true in the formula. I’ll arrow down to the cell that contains 15%, and I’ll need to make this an absolute reference, so I’ll hit F4 on my keyboard to put a dollar sign before the row number, and a dollar sign before the column letter. Now I need to calculate 15% of the sales amount, so I’ll type an asterisk, which is the sign for multiplication, then I’ll select cell C3, which is the cell that contains the sales amount. Type a comma. Then, my value if false will be another logical test. We’ll have to ask, is the sales amount greater than or equal to 40000? So I’ll type IF and open parenthesis. I’ll select the sales amount cell and type >=40000. Type a comma, and I’ll select the cell that contains the bonus percentage. I’ll press F4 on my keyboard to make this an absolute cell reference, then type an asterisk and select the sales amount cell. So now my value if true is 12% times sales. I’ll type a comma to move on to my value if false. Now we know that if our sales are not greater than 40000, then they must be less than 40000. So we can select cell G14, which contains our bonus percentage. Press F4 on the keyboard to make it an absolute reference, type an asterisk for multiplication, and then select the sales cell. Now I can close both sets of parentheses and press the Enter key on my keyboard. I will select the cell with my formula in it and double click on the fill handle to fill down.

This concludes the tutorial for nesting logical functions. Thank you for watching.