Original subtitles

In the previous couple of lessons, we started to explore the usage of if to make logical decisions,

and in this lesson, I want to move your knowledge on a little bit and start talking about nested if

statements.

Now you might be thinking to yourself, what on earth are nested if statements?

Well, basically what they are are if statements inside other if statements.

So let's take a look at an example of what I mean.

Now, in this table of data, again, we just have some employee data, so we have employee names,

location information department, the date they were hired, the number of years they've been at the

company, their salary and then the job rating.

And it's this column here that is going to be important in this particular lesson because what we're

trying to do here is we're trying to dish out end of year bonuses based on that employee's job rating

and notice.

I have another little table on the right hand side, which basically sets out the bonus amount assigned

to each job rating.

So if they have a job rating of five, which is the best in this case, they're going to get a bonus

of $3000 if their job rating is four.

It's going to be 1500 three, 900 to 100, and if it's one or zero, they're going to get nothing.

So what I'm having to do here is basically I need to perform multiple logical tests because I want the

formula to look at this cell and say if it's five, the bonus is this amount, if it's for the bonus,

is this amount, if it's three, the bonuses, this amount.

So on and so forth.

And this is where nested if statements come in.

And I will say these can get quite complicated, but when you break them down, they're actually pretty

logical.

So let's take a look at how we would construct this.

Now, the result that I'm actually looking for in column age is I'm basically just wanted to output

the amount of the bonus.

So 3000, 1400, 900, so on and so forth.

I don't want it to say bonus or no bonus at this stage.

So we're going to taipan equals f and open our bracket.

So we want a first logical test.

So we're going to say if the job rating in Sochi four is equal to five, which we have listed in Seoul

J4.

Remember, I could have hardcoded this in and typed five.

But we want to try and get away from hard coding in values and use the cell reference instead.

Now I'm going to lock this cell because I don't want it to move.

Now, if that is true, they're going to get a bonus of 3000.

So I simply want to select the cell and again, make sure that I lock it comma.

Now, normally at this stage, I would then go and type in the value if false.

But I need to add in more logical statements, so I'm going to go straight into my second if.

And this is where we get the terminology nested ifs because you have if statements inside other if statements.

And I basically go through this process again.

So if the job rating is equal to four this times, the J5 lock the cell.

If that's true, they're going to get a bonus of 1500.

Lock the cell comma next.

If statement.

If the job rating is equal to three, then they're going to get a bonus of 900

if the job rating.

Is equal to two.

Then they're going to get a bonus of 100.

And then the final if and then we have a final if.

The job rating now I could put here equal to one.

But if they've got a zero, I also want it to include that.

So what I'm going to say is if it's less than or equal.

To one.

Then they're going to get nothing.

And then the value of false is going to be zero.

Now that is a really long formula and it's a little bit easier to see in that formula bar.

Now when you're constructing formulas like this, remember you need to close off as many brackets or

parentheses as you've opened.

So I've got quite a few there.

If I count them, I think I've got about four or five.

Let's just add four onto the end here.

When I hit enter, if I haven't got enough, which I haven't in this case, Excel is going to automatically

correct me.

So I'm going to say yes to select the correction.

Let's double click to copy this formula down, and I should find that everything in here works.

So the job rating of one is going to give a result of nothing, which is correct a job rating of four.

They're going to get 1400 job rating of five 3000 job rating of two 100.

So on and so forth.

Now notice here that I have this little green triangle in the corner.

This is denoting that there is some kind of warning or maybe an error on these cells.

And what this is basically telling me if I click this little warning icon is that I have unprotected

formulas.

So it's a long formula that I haven't protected, so anyone can go in here and make changes to this

formula.

Now I'm actually fine with that.

So what I'm going to do is control shift down to select all of the cells, and I'm going to say ignore

error just to get rid of those green triangles.

Always worth checking your warning.

Sometimes they are errors that you need to fix.

Sometimes they're just kind of notifications letting you know that maybe you haven't included enough

data.

Or maybe in this case, a long formula is unprotected.

So that is a nested if statement we have if statements inside, if statements.

Now it might be that I want to do this in a slightly different way.

So maybe instead of having the actual bonus amounts listed out just here, maybe I just wanted to say

if they're going to get a bonus or if they're not going to get a bonus because basically everybody with

a job rating of two to five is going to get a bonus.

It's only those of the job rating of one who aren't going to get a bonus.

So control shift down.

I'm going to delete this formula out, so I'm going to just reconstruct it in a slightly different way.

So I'm going to say if a logical test, if the job rating now this time I'm going to say is greater

than or equal to two.

Remember, we need to look that if that is true, then they're going to get a bonus if that is false.

So if the job rating is less than two, they're not going to get a bonus.

So that formula is a lot shorter to construct.

So it really all depends how you want to do these and set this up.

Now we'll say that with that nested if statement is a very long formula and there is a new a formula

that's been released in the later versions of Excel.

So from Excel 2016 onwards called if!

S not to be confused with nested ifs, we have a formula called if!

S.

And if I type it into a cell, you can see it sitting just there.

Now this is a way to make our nested if statement formulas a little bit shorter.

I will say not that much shorter, but a little bit quicker to construct.

And that's what we're going to take a look at in the next lesson.