1
00:00:01,140 --> 00:00:07,770
Now let's take a look at one of Excel's most useful functions the "IF" function. And then we're going to

2
00:00:07,770 --> 00:00:14,510
take a look at other logical functions that can come in handy when you create Excel reports.

3
00:00:14,520 --> 00:00:20,280
Here we have a list of our employee names, their entry dates, yearly salary, and previous year's salary.

4
00:00:20,340 --> 00:00:22,590
And we want to calculate percentage change.

5
00:00:22,650 --> 00:00:25,610
Now this is something that we did in the previous lecture, right?

6
00:00:25,620 --> 00:00:26,960
It was quite simple.

7
00:00:27,000 --> 00:00:35,400
We did yearly salary divided by previous year's salary minus one and set this down. We got these errors

8
00:00:35,400 --> 00:00:35,810
here.

9
00:00:35,850 --> 00:00:43,200
In this case I just have zero values here so I'm just getting the #DIV/0 error. And we solved this by

10
00:00:43,260 --> 00:00:46,860
putting this formula inside that "IfError" function.

11
00:00:46,870 --> 00:00:53,250
Remember I said that you need to be careful if you use the IFERROR function because it covers up every

12
00:00:53,250 --> 00:00:54,270
type of error.

13
00:00:54,600 --> 00:00:57,090
Let's say in this case we didn't want that.

14
00:00:57,090 --> 00:01:04,950
So if someone came and typed in text here, we actually want to see an error here because we need to correct

15
00:01:05,310 --> 00:01:06,860
that dataset.

16
00:01:06,870 --> 00:01:13,980
So instead of accounting for every single type of error, we just want to account for this #DIV/0 errors.

17
00:01:13,980 --> 00:01:16,100
I can use the "If" function.

18
00:01:16,890 --> 00:01:20,310
Let's start off here with If and see how this works.

19
00:01:20,370 --> 00:01:28,290
First argument is the logical test that I want to apply. In this case my logical test is to check if

20
00:01:28,290 --> 00:01:31,170
this number here is zero or not.

21
00:01:31,200 --> 00:01:34,040
There are different ways of writing this and I'm gonna show them to you.

22
00:01:34,050 --> 00:01:39,310
But let's start off with checking if this cell value equals to a zero.

23
00:01:39,390 --> 00:01:41,460
That's our logical test.

24
00:01:41,460 --> 00:01:48,060
Next is, what do we want to happen if the results of this test is a true.

25
00:01:48,060 --> 00:01:56,000
So basically if this is actually a zero. Let's say I don't want to show anything in my final cells.

26
00:01:56,000 --> 00:01:58,290
I'm going to put quotation, quotation.

27
00:01:58,290 --> 00:02:03,160
The last argument of the If function is what do we want to show in the cell.

28
00:02:03,360 --> 00:02:05,140
If this condition here.

29
00:02:05,220 --> 00:02:11,490
If our logical test is not true. Which would be in this case that this is not a zero.

30
00:02:11,490 --> 00:02:12,510
What do we want to see?

31
00:02:13,020 --> 00:02:18,540
Well, we actually want to see the percentage change but as a first step I'm just going to show the value

32
00:02:18,540 --> 00:02:18,870
here.

33
00:02:18,870 --> 00:02:25,910
So I'm just going to click on this cell, close bracket, press enter, just to check what we get here.

34
00:02:25,920 --> 00:02:32,100
So that if I drag this down, I get empty and empty for these two cells.

35
00:02:32,100 --> 00:02:32,310
Right.

36
00:02:32,310 --> 00:02:34,950
So even though this doesn't say zero here.

37
00:02:34,950 --> 00:02:38,910
But the moment Excel is evaluating this in a formula.

38
00:02:38,910 --> 00:02:41,660
It translates this to a true.

39
00:02:41,900 --> 00:02:47,750
And I'm going to show you a quick debugging trick that you can use here to see what's behind the formula.

40
00:02:47,760 --> 00:02:56,100
So just highlight this portion of the formula and press the F9 key and you notice the result is

41
00:02:56,100 --> 00:02:56,830
true.

42
00:02:56,970 --> 00:03:02,700
The only thing you need to watch out for is don't press enter now, press control+z to go back because

43
00:03:02,700 --> 00:03:07,260
if you press enter you're going to keep that true in the cell.

44
00:03:07,260 --> 00:03:09,760
So now this was one way of writing this.

45
00:03:09,780 --> 00:03:12,570
I checked if D5 equals to Zero.

46
00:03:12,930 --> 00:03:15,040
But I could do this differently.

47
00:03:15,210 --> 00:03:23,250
I could check if D5 is greater than a zero because I'm not going to have negative salaries, so I'm fine to

48
00:03:23,250 --> 00:03:29,100
check if it's greater than. If I was gonna have negative numbers here and I want to say,  doesn't equal

49
00:03:29,100 --> 00:03:30,110
to zero.

50
00:03:30,240 --> 00:03:31,800
That's the sign that you need.

51
00:03:32,310 --> 00:03:36,680
But here since we're dealing with salaries I'm going to go with greater than zero.

52
00:03:36,870 --> 00:03:40,220
If that's my logical test, what's my value

53
00:03:40,230 --> 00:03:41,090
if it's true?

54
00:03:41,550 --> 00:03:47,130
So basically if this number is greater than zero, what do I want to see here?

55
00:03:47,160 --> 00:03:54,930
I wanted to calculate the percentage change which is this number divided by this number minus one.

56
00:03:54,930 --> 00:03:59,880
Now you can also write this in a different way, you can say yearly salary minus previous year's salary

57
00:03:59,910 --> 00:04:02,130
divided by previous year's salary.

58
00:04:02,130 --> 00:04:06,090
We're going to get the same values. Now as the last argument.

59
00:04:06,120 --> 00:04:12,360
What do we want to see if our logical test is a false? Nothing.

60
00:04:12,390 --> 00:04:18,290
So quotation, quotation, press enter. Now let's send this down and this looks good.

61
00:04:18,329 --> 00:04:21,649
Let's format it as a percentage.

62
00:04:21,670 --> 00:04:27,630
Now if someone goes here and types in to check, I get an error here.

63
00:04:27,630 --> 00:04:32,730
So this can be my trigger that there's something wrong with the data set and I should actually correct

64
00:04:32,730 --> 00:04:35,840
this before I finalize my report.

65
00:04:35,950 --> 00:04:38,760
I'm just gonna press control+z to go back.

66
00:04:38,760 --> 00:04:44,790
Now let's do another example with the IF function. We want to know if the employees started to work after

67
00:04:44,790 --> 00:04:45,600
that date.

68
00:04:45,600 --> 00:04:49,670
So we want to get a Yes and a No in these cells.

69
00:04:49,980 --> 00:04:52,440
Let's start off with the IF function.

70
00:04:52,500 --> 00:04:54,060
What's the logical test?

71
00:04:54,750 --> 00:04:58,530
I have to compare this date with the employees date, right?

72
00:04:58,770 --> 00:05:07,220
I'm going to check if this date is greater than this date. And I'm going to fix this because I'm planning

73
00:05:07,220 --> 00:05:09,640
to pull this formula down.

74
00:05:09,780 --> 00:05:16,170
Now if it's greater than, what do I want returned? I want to get a Yes.

75
00:05:16,180 --> 00:05:23,810
So I'm going to put it in quotation marks because it's text and if it's not, I want to get a No.

76
00:05:23,810 --> 00:05:26,970
Close bracket, press enter, send this down.

77
00:05:27,050 --> 00:05:28,650
Now let's double check.

78
00:05:28,730 --> 00:05:32,420
So did this employees start to work after this date.

79
00:05:32,420 --> 00:05:34,190
No.

80
00:05:34,340 --> 00:05:35,440
This one, Yes.

81
00:05:35,470 --> 00:05:41,480
By putting the greater than sign I'm not including the date. If I wanted to include the date as well

82
00:05:41,570 --> 00:05:45,850
I would put greater than or equal to this date.

83
00:05:46,740 --> 00:05:47,070
Okay.

84
00:05:47,090 --> 00:05:53,560
So now let's take a look at other logical operators. We have the number 10 in the cell.

85
00:05:53,720 --> 00:05:58,360
We have text that says 10 in the cell and here we have an empty cell.

86
00:05:58,370 --> 00:06:02,770
We want to check if the number here is a number or not.

87
00:06:02,930 --> 00:06:07,250
There is an Excel function for that and it's called "IsNumber".

88
00:06:07,280 --> 00:06:15,350
All we need to give it is the value, close bracket, press enter. And this tells us if I have a number or

89
00:06:15,350 --> 00:06:20,390
not. And notice that an empty cell is false, it's not a number.

90
00:06:20,390 --> 00:06:26,990
So that's another test you can do to make sure that people have filled up your templates correctly.

91
00:06:27,020 --> 00:06:28,010
Let's do another one.

92
00:06:28,010 --> 00:06:30,540
We want to check if the value is text.

93
00:06:30,560 --> 00:06:32,320
These are all really easy.

94
00:06:32,330 --> 00:06:38,160
The function we need is "IsText" put in the value, close bracket, and push this down.

95
00:06:38,180 --> 00:06:44,320
That's not text, that's text, and an empty cell is not text.

96
00:06:44,420 --> 00:06:47,880
Next, blank guess what it is? "IsBlank"

97
00:06:48,020 --> 00:06:54,760
What do you think the answers of these are going to be? False, false, and true.

98
00:06:54,940 --> 00:07:00,140
Another way you can check if something is not a number is to use

99
00:07:00,140 --> 00:07:02,430
the "IsNumber" function.

100
00:07:02,530 --> 00:07:06,870
But before it, you put the "Not" logical argument.

101
00:07:06,910 --> 00:07:09,030
So if it's not a number.

102
00:07:09,040 --> 00:07:14,650
So now I'm going to put "Not(IsNumber..." and let's see the difference to the other formulas here.

103
00:07:14,650 --> 00:07:21,840
Close, close, and pres enter. Now my results for this one is similar to "IsText".

104
00:07:21,850 --> 00:07:25,930
Because if it's text, it's also not a number.

105
00:07:26,170 --> 00:07:33,970
So for both cases I get false. Now when I drag this down, for this one I also get true.

106
00:07:33,970 --> 00:07:41,830
Just like with "IsText" but for the blank cell I get a different result. I actually get a true back.

107
00:07:41,830 --> 00:07:49,750
So basically what the "Not" logical test does is it turns these the other way round. The true becomes

108
00:07:49,750 --> 00:07:58,920
a false and the two false values here both become true values. In the next example, let's do a logical

109
00:07:58,920 --> 00:08:03,470
test that checks for two conditions at the same time.

110
00:08:03,750 --> 00:08:11,190
So we want to check here if this value is greater than 2 and less than 10.

111
00:08:11,190 --> 00:08:18,510
So not just greater than 2, not just less than 10 but both conditions have to apply for us to get a true

112
00:08:18,510 --> 00:08:19,710
here.

113
00:08:19,710 --> 00:08:23,660
Here we can use the "And" logical test.

114
00:08:23,670 --> 00:08:29,550
All we have to do is put the different logical tests inside the "And" Function.

115
00:08:29,550 --> 00:08:37,830
My first logical test is if the number here is greater than a 2 and then followed by a comma,

116
00:08:37,840 --> 00:08:39,510
that's the Excel separator.

117
00:08:39,510 --> 00:08:44,950
The second logical test is, is this number less than a 10.

118
00:08:44,980 --> 00:08:48,980
And since I don't have it in cell, I'm just gonna type it in. Close bracket, press enter.

119
00:08:49,040 --> 00:08:53,650
What do you think I'm gonna get? True.

120
00:08:53,730 --> 00:09:00,080
Now I'm going to drag these down and just think in advance what we're gonna get back for these? False, false

121
00:09:00,090 --> 00:09:00,380
right.

122
00:09:00,390 --> 00:09:04,130
Because these don't fall between 2 and 10.

123
00:09:04,620 --> 00:09:07,460
The "or" condition on the other hand checks

124
00:09:07,560 --> 00:09:10,920
if either of these conditions apply.

125
00:09:10,920 --> 00:09:13,340
So is this number greater than a two

126
00:09:13,350 --> 00:09:16,390
or is it less than 10.

127
00:09:16,440 --> 00:09:20,190
The writing of the function follows the same logic as with the "and" function.

128
00:09:20,190 --> 00:09:29,190
So we're going to check if this number is greater than 2 or if this number is less than 10.

129
00:09:29,340 --> 00:09:31,160
Close bracket, press enter.

130
00:09:31,440 --> 00:09:32,710
True, right?

131
00:09:32,790 --> 00:09:35,220
Because in this case it is less than 10.

132
00:09:35,220 --> 00:09:37,480
It's also greater than 2.

133
00:09:37,540 --> 00:09:40,140
Now what do you think is going to happen when I drag these down?

134
00:09:40,260 --> 00:09:43,240
What's gonna happen to the next one?

135
00:09:43,260 --> 00:09:49,550
It's true because it's greater than two. And what's going to happen to the last one?

136
00:09:49,590 --> 00:09:52,580
It's true because it's less than 10.

137
00:09:52,620 --> 00:09:52,830
Right.

138
00:09:52,830 --> 00:10:00,570
So keep that in mind if you're going to be using the "and" and the "or" conditions. In this example, we want

139
00:10:00,570 --> 00:10:07,050
to do the same check that we did but we want to do it in a more efficient way. So we want to again check

140
00:10:07,140 --> 00:10:11,200
if the entry date is after the specified date here.

141
00:10:11,210 --> 00:10:18,960
Now we had our original version before. So let's just practice that. We said if this date is greater than

142
00:10:19,290 --> 00:10:21,980
this date, use F4 to fix this.

143
00:10:22,200 --> 00:10:23,760
Then I want to see

144
00:10:24,150 --> 00:10:24,860
Yes.

145
00:10:24,960 --> 00:10:31,560
Otherwise I want to see a No. Close bracket, press enter, pull this down.

146
00:10:31,560 --> 00:10:32,360
This looks good.

147
00:10:32,370 --> 00:10:33,820
This looks good.

148
00:10:33,930 --> 00:10:37,230
This doesn't look so good. If I have text here.

149
00:10:37,260 --> 00:10:39,650
It's going to say yes.

150
00:10:39,660 --> 00:10:40,150
Why?

151
00:10:40,150 --> 00:10:47,460
Well because if you sort data where you have numbers and text, text comes after the numbers. So it thinks

152
00:10:47,460 --> 00:10:51,060
that any text is greater than that number.

153
00:10:51,060 --> 00:10:53,190
That's something I want to avoid.

154
00:10:53,190 --> 00:11:01,920
One way of doing this is to account for that specific text so I could say: If my logical test is, if this

155
00:11:01,920 --> 00:11:05,720
value equals missing

156
00:11:06,690 --> 00:11:09,390
then it should show nothing.

157
00:11:09,660 --> 00:11:17,710
Otherwise, I can bring my original IF function inside here so that means I have a nested IF function.

158
00:11:17,970 --> 00:11:28,280
I'm going to start off with my second IF and say check if this value is greater than this date, F4

159
00:11:28,290 --> 00:11:34,290
to fix it, then yes otherwise no.

160
00:11:36,050 --> 00:11:42,280
Okay so we're getting advanced here but it's important to expose you to this because if you're going

161
00:11:42,280 --> 00:11:47,820
to work a lot with Excel, you are going to come across nested IF functions.

162
00:11:47,820 --> 00:11:54,570
And hopefully after this lecture it's going to be easier for you to read those functions. For this case,

163
00:11:54,570 --> 00:11:55,780
I get a no.

164
00:11:55,800 --> 00:12:00,660
So basically everything should be identical to my version one, except for missing

165
00:12:00,660 --> 00:12:03,960
I should have nothing. That looks good.

166
00:12:03,960 --> 00:12:09,860
But now what if someone comes and they don't put missing, they put empty.

167
00:12:09,960 --> 00:12:16,560
I get a yes here. I can't account for every single thing someone might type here so I just want to make

168
00:12:16,560 --> 00:12:24,450
sure that this isn't text, that is actually a number. I can use one of the logical test functions we

169
00:12:24,570 --> 00:12:25,880
saw above.

170
00:12:25,950 --> 00:12:36,140
If, ISTEXT, this value, so then that accounts for any type of text then, nothing.

171
00:12:36,260 --> 00:12:46,530
Otherwise, check if this value is greater than this one. Then put a Yes in the cell.

172
00:12:46,530 --> 00:12:49,920
Otherwise, put a No, close bracket.

173
00:12:49,920 --> 00:12:56,040
I have two IF functions. I need to close two brackets, press enter, and send this down.

174
00:12:57,040 --> 00:12:59,870
It doesn't care what type of text they put here.

175
00:13:00,010 --> 00:13:02,170
It's going to show empty.

176
00:13:02,170 --> 00:13:04,040
Everything else is going to work.

177
00:13:04,520 --> 00:13:08,970
Okay so this is the power of the "IF" function.

178
00:13:09,040 --> 00:13:14,020
If you're completely new to this, it's going to take some practice to get a hang of it.

179
00:13:14,110 --> 00:13:20,290
So please take the time to practice because it's going to make your life so much easier once you

180
00:13:20,290 --> 00:13:21,550
learn to master this.