Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,500 --> 00:00:13,450
In the previous couple of lessons, we started to explore the usage of if to make logical decisions,
2
00:00:14,230 --> 00:00:20,320
and in this lesson, I want to move your knowledge on a little bit and start talking about nested if
3
00:00:20,320 --> 00:00:21,010
statements.
4
00:00:21,760 --> 00:00:25,480
Now you might be thinking to yourself, what on earth are nested if statements?
5
00:00:26,260 --> 00:00:32,170
Well, basically what they are are if statements inside other if statements.
6
00:00:32,530 --> 00:00:35,650
So let's take a look at an example of what I mean.
7
00:00:35,860 --> 00:00:41,410
Now, in this table of data, again, we just have some employee data, so we have employee names,
8
00:00:42,280 --> 00:00:47,890
location information department, the date they were hired, the number of years they've been at the
9
00:00:47,890 --> 00:00:51,820
company, their salary and then the job rating.
10
00:00:52,360 --> 00:00:57,790
And it's this column here that is going to be important in this particular lesson because what we're
11
00:00:57,790 --> 00:01:04,420
trying to do here is we're trying to dish out end of year bonuses based on that employee's job rating
12
00:01:05,110 --> 00:01:05,680
and notice.
13
00:01:05,680 --> 00:01:11,260
I have another little table on the right hand side, which basically sets out the bonus amount assigned
14
00:01:11,260 --> 00:01:12,490
to each job rating.
15
00:01:12,700 --> 00:01:18,850
So if they have a job rating of five, which is the best in this case, they're going to get a bonus
16
00:01:18,850 --> 00:01:23,290
of $3000 if their job rating is four.
17
00:01:23,320 --> 00:01:32,710
It's going to be 1500 three, 900 to 100, and if it's one or zero, they're going to get nothing.
18
00:01:33,070 --> 00:01:40,750
So what I'm having to do here is basically I need to perform multiple logical tests because I want the
19
00:01:40,750 --> 00:01:47,980
formula to look at this cell and say if it's five, the bonus is this amount, if it's for the bonus,
20
00:01:47,980 --> 00:01:50,560
is this amount, if it's three, the bonuses, this amount.
21
00:01:50,590 --> 00:01:52,060
So on and so forth.
22
00:01:52,750 --> 00:01:55,240
And this is where nested if statements come in.
23
00:01:55,900 --> 00:02:02,170
And I will say these can get quite complicated, but when you break them down, they're actually pretty
24
00:02:02,260 --> 00:02:02,950
logical.
25
00:02:03,250 --> 00:02:05,620
So let's take a look at how we would construct this.
26
00:02:06,490 --> 00:02:11,320
Now, the result that I'm actually looking for in column age is I'm basically just wanted to output
27
00:02:11,320 --> 00:02:12,400
the amount of the bonus.
28
00:02:12,400 --> 00:02:15,910
So 3000, 1400, 900, so on and so forth.
29
00:02:16,900 --> 00:02:20,180
I don't want it to say bonus or no bonus at this stage.
30
00:02:20,440 --> 00:02:23,380
So we're going to taipan equals f and open our bracket.
31
00:02:23,590 --> 00:02:25,990
So we want a first logical test.
32
00:02:26,260 --> 00:02:36,160
So we're going to say if the job rating in Sochi four is equal to five, which we have listed in Seoul
33
00:02:36,160 --> 00:02:36,820
J4.
34
00:02:36,820 --> 00:02:40,760
Remember, I could have hardcoded this in and typed five.
35
00:02:41,320 --> 00:02:46,390
But we want to try and get away from hard coding in values and use the cell reference instead.
36
00:02:47,290 --> 00:02:50,740
Now I'm going to lock this cell because I don't want it to move.
37
00:02:51,700 --> 00:02:56,230
Now, if that is true, they're going to get a bonus of 3000.
38
00:02:56,710 --> 00:03:01,690
So I simply want to select the cell and again, make sure that I lock it comma.
39
00:03:02,470 --> 00:03:06,820
Now, normally at this stage, I would then go and type in the value if false.
40
00:03:07,480 --> 00:03:13,670
But I need to add in more logical statements, so I'm going to go straight into my second if.
41
00:03:14,830 --> 00:03:20,680
And this is where we get the terminology nested ifs because you have if statements inside other if statements.
42
00:03:21,310 --> 00:03:23,440
And I basically go through this process again.
43
00:03:23,440 --> 00:03:31,030
So if the job rating is equal to four this times, the J5 lock the cell.
44
00:03:31,690 --> 00:03:35,500
If that's true, they're going to get a bonus of 1500.
45
00:03:36,010 --> 00:03:39,130
Lock the cell comma next.
46
00:03:39,140 --> 00:03:40,030
If statement.
47
00:03:40,360 --> 00:03:48,580
If the job rating is equal to three, then they're going to get a bonus of 900
48
00:03:51,490 --> 00:03:54,070
if the job rating.
49
00:03:55,060 --> 00:03:57,690
Is equal to two.
50
00:04:00,070 --> 00:04:02,680
Then they're going to get a bonus of 100.
51
00:04:02,770 --> 00:04:06,970
And then the final if and then we have a final if.
52
00:04:08,400 --> 00:04:12,390
The job rating now I could put here equal to one.
53
00:04:12,600 --> 00:04:15,360
But if they've got a zero, I also want it to include that.
54
00:04:15,570 --> 00:04:19,020
So what I'm going to say is if it's less than or equal.
55
00:04:20,190 --> 00:04:21,000
To one.
56
00:04:23,660 --> 00:04:25,910
Then they're going to get nothing.
57
00:04:26,510 --> 00:04:29,930
And then the value of false is going to be zero.
58
00:04:30,110 --> 00:04:36,140
Now that is a really long formula and it's a little bit easier to see in that formula bar.
59
00:04:36,890 --> 00:04:43,220
Now when you're constructing formulas like this, remember you need to close off as many brackets or
60
00:04:43,220 --> 00:04:45,050
parentheses as you've opened.
61
00:04:45,560 --> 00:04:46,970
So I've got quite a few there.
62
00:04:46,970 --> 00:04:49,070
If I count them, I think I've got about four or five.
63
00:04:49,100 --> 00:04:52,040
Let's just add four onto the end here.
64
00:04:52,190 --> 00:04:58,250
When I hit enter, if I haven't got enough, which I haven't in this case, Excel is going to automatically
65
00:04:58,250 --> 00:04:58,910
correct me.
66
00:04:58,910 --> 00:05:01,670
So I'm going to say yes to select the correction.
67
00:05:02,570 --> 00:05:09,260
Let's double click to copy this formula down, and I should find that everything in here works.
68
00:05:09,260 --> 00:05:15,650
So the job rating of one is going to give a result of nothing, which is correct a job rating of four.
69
00:05:15,740 --> 00:05:22,050
They're going to get 1400 job rating of five 3000 job rating of two 100.
70
00:05:22,070 --> 00:05:23,420
So on and so forth.
71
00:05:23,780 --> 00:05:26,930
Now notice here that I have this little green triangle in the corner.
72
00:05:26,930 --> 00:05:32,240
This is denoting that there is some kind of warning or maybe an error on these cells.
73
00:05:32,670 --> 00:05:37,910
And what this is basically telling me if I click this little warning icon is that I have unprotected
74
00:05:37,910 --> 00:05:38,480
formulas.
75
00:05:38,480 --> 00:05:45,110
So it's a long formula that I haven't protected, so anyone can go in here and make changes to this
76
00:05:45,110 --> 00:05:45,590
formula.
77
00:05:46,070 --> 00:05:47,810
Now I'm actually fine with that.
78
00:05:47,810 --> 00:05:54,620
So what I'm going to do is control shift down to select all of the cells, and I'm going to say ignore
79
00:05:54,620 --> 00:05:58,040
error just to get rid of those green triangles.
80
00:05:58,310 --> 00:05:59,870
Always worth checking your warning.
81
00:05:59,910 --> 00:06:02,000
Sometimes they are errors that you need to fix.
82
00:06:02,030 --> 00:06:07,610
Sometimes they're just kind of notifications letting you know that maybe you haven't included enough
83
00:06:07,610 --> 00:06:08,240
data.
84
00:06:08,540 --> 00:06:12,230
Or maybe in this case, a long formula is unprotected.
85
00:06:12,620 --> 00:06:18,680
So that is a nested if statement we have if statements inside, if statements.
86
00:06:19,040 --> 00:06:21,650
Now it might be that I want to do this in a slightly different way.
87
00:06:21,660 --> 00:06:29,240
So maybe instead of having the actual bonus amounts listed out just here, maybe I just wanted to say
88
00:06:29,240 --> 00:06:35,270
if they're going to get a bonus or if they're not going to get a bonus because basically everybody with
89
00:06:35,270 --> 00:06:38,120
a job rating of two to five is going to get a bonus.
90
00:06:38,360 --> 00:06:41,990
It's only those of the job rating of one who aren't going to get a bonus.
91
00:06:42,740 --> 00:06:44,600
So control shift down.
92
00:06:44,660 --> 00:06:49,460
I'm going to delete this formula out, so I'm going to just reconstruct it in a slightly different way.
93
00:06:50,120 --> 00:06:58,460
So I'm going to say if a logical test, if the job rating now this time I'm going to say is greater
94
00:06:58,460 --> 00:07:02,180
than or equal to two.
95
00:07:02,480 --> 00:07:10,570
Remember, we need to look that if that is true, then they're going to get a bonus if that is false.
96
00:07:10,580 --> 00:07:16,200
So if the job rating is less than two, they're not going to get a bonus.
97
00:07:17,750 --> 00:07:20,720
So that formula is a lot shorter to construct.
98
00:07:20,990 --> 00:07:25,280
So it really all depends how you want to do these and set this up.
99
00:07:25,520 --> 00:07:32,060
Now we'll say that with that nested if statement is a very long formula and there is a new a formula
100
00:07:32,060 --> 00:07:34,430
that's been released in the later versions of Excel.
101
00:07:34,430 --> 00:07:38,750
So from Excel 2016 onwards called if!
102
00:07:38,750 --> 00:07:43,700
S not to be confused with nested ifs, we have a formula called if!
103
00:07:43,700 --> 00:07:44,210
S.
104
00:07:44,210 --> 00:07:47,600
And if I type it into a cell, you can see it sitting just there.
105
00:07:48,350 --> 00:07:53,260
Now this is a way to make our nested if statement formulas a little bit shorter.
106
00:07:53,270 --> 00:07:57,740
I will say not that much shorter, but a little bit quicker to construct.
107
00:07:58,250 --> 00:08:01,250
And that's what we're going to take a look at in the next lesson.
10678
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.