1
00:00:04,820 --> 00:00:08,189
Let's take a look at the challenge for the function section.

2
00:00:08,270 --> 00:00:11,430
We have some serious tasks ahead of us.

3
00:00:11,450 --> 00:00:16,700
Number one, get the employee division from the Master Data tab.

4
00:00:16,700 --> 00:00:20,940
So here we have the name of the employee, the salary of the employee

5
00:00:21,080 --> 00:00:23,800
and we need to figure out what their division is.

6
00:00:23,810 --> 00:00:26,710
So let's take a look at the master data.

7
00:00:26,750 --> 00:00:29,370
We have names here.

8
00:00:29,450 --> 00:00:32,900
We have division right here and department right here.

9
00:00:33,200 --> 00:00:35,960
All we're interested in is division.

10
00:00:36,820 --> 00:00:37,260
OK.

11
00:00:37,290 --> 00:00:42,600
So which formula can we use here? the "VLOOKUP" function.

12
00:00:42,600 --> 00:00:49,380
So let's start off with VLOOKUP. What are we looking up? The name here.

13
00:00:49,380 --> 00:00:51,510
Where are we looking this up?

14
00:00:51,540 --> 00:00:54,370
Our table is in the Master Data tab.

15
00:00:54,420 --> 00:01:00,390
We are only interested to get division back but we need the name because that's going to be our lookup

16
00:01:00,430 --> 00:01:01,350
value.

17
00:01:01,350 --> 00:01:04,790
Just gonna use the shortcut key control+shift+down.

18
00:01:04,890 --> 00:01:11,820
Now let's fix this area using the F4 key because we're planning to pull our other formula down and

19
00:01:11,820 --> 00:01:14,320
we don't want this range to shift.

20
00:01:14,640 --> 00:01:18,510
The last argument for VLOOKUP is the column index.

21
00:01:18,510 --> 00:01:20,030
What do we need here?

22
00:01:20,070 --> 00:01:21,050
Number two, right?

23
00:01:21,060 --> 00:01:29,130
The division is sitting in the second column right here and then for the last argument we want an exact

24
00:01:29,130 --> 00:01:29,710
match.

25
00:01:29,730 --> 00:01:34,680
So we're going to go with false, close bracket, press enter. Productivity.

26
00:01:35,190 --> 00:01:43,410
Let's send this down and let's just make sure that Paul Garza does work in the productivity division.

27
00:01:43,410 --> 00:01:45,280
Paul Garza, productivity.

28
00:01:45,330 --> 00:01:46,740
That looks good.

29
00:01:46,740 --> 00:01:48,120
Let's go to the bottom here.

30
00:01:48,120 --> 00:01:57,480
Just make sure our formula still looks right and that Robert Richardson works in utility. Robert Richardson

31
00:01:58,140 --> 00:02:00,620
works in the utility division.

32
00:02:00,630 --> 00:02:01,380
Perfect.

33
00:02:01,380 --> 00:02:02,790
That was number one.

34
00:02:02,790 --> 00:02:11,780
Number one is done. Number two, calculate the overall average salary and round to the nearest integer.

35
00:02:11,780 --> 00:02:15,280
So we want to calculate the overall salary right here.

36
00:02:15,300 --> 00:02:18,000
That's our salary information.

37
00:02:18,100 --> 00:02:19,890
What formula am I going to use here?

38
00:02:20,050 --> 00:02:24,220
The "Average" function. Now we're planning to round as well.

39
00:02:24,220 --> 00:02:29,890
So it's up to you if you want to start with round and then put in average function or start from the

40
00:02:29,890 --> 00:02:33,200
inside first and work your way out.

41
00:02:33,300 --> 00:02:35,200
Let's start from inside.

42
00:02:35,200 --> 00:02:37,330
Let's go with average.

43
00:02:37,540 --> 00:02:39,270
The numbers are right here.

44
00:02:39,280 --> 00:02:40,700
Control+shift+down.

45
00:02:40,810 --> 00:02:46,690
Now I don't necessarily need to fix this because I'm not planning to pull the formula down. So it's enough

46
00:02:46,720 --> 00:02:53,440
if I close the bracket, press enter. Just to make sure I get my number I don't get any errors.

47
00:02:53,440 --> 00:03:03,130
Now I can put this inside the ROUND function and we want how many digits? I think I said round to the nearest

48
00:03:03,220 --> 00:03:04,300
integer.

49
00:03:04,300 --> 00:03:11,450
So I just need to go with zero, close bracket, press enter. And that's my average salary.

50
00:03:11,490 --> 00:03:13,150
Now that was number two.

51
00:03:13,150 --> 00:03:14,360
Number two is done.

52
00:03:14,530 --> 00:03:19,300
Number three, calculate the average salary by division.

53
00:03:19,330 --> 00:03:27,430
So now we need to use "AverageIfs" because we have to account for criteria. And then we want to round to

54
00:03:27,430 --> 00:03:29,680
the nearest integer as well.

55
00:03:29,680 --> 00:03:36,340
So now let's do this the other way round. Let's actually start with ROUND. And inside ROUND, our number that

56
00:03:36,340 --> 00:03:43,260
we want to around is the result of the AverageIfs function, which is right here.

57
00:03:43,540 --> 00:03:46,100
What's the range we want to get the average of?

58
00:03:46,230 --> 00:03:47,720
That's this one.

59
00:03:48,310 --> 00:03:52,410
Now let us think about the fixing because I'm planning to pull this down.

60
00:03:53,050 --> 00:03:56,640
I have to fix this using the F4 key.

61
00:03:56,880 --> 00:03:59,880
Next is the criteria_range1.

62
00:03:59,890 --> 00:04:06,770
Basically the range where productivity is sitting on which is actually right here.

63
00:04:06,850 --> 00:04:15,910
So let's go and highlight that and press F4. Then the last one is the criteria itself which is this one.

64
00:04:16,269 --> 00:04:18,579
Do I need to fix this?

65
00:04:18,760 --> 00:04:25,540
Not necessarily because I'm only pulling down, I'm not pulling across. So I'm going to close the bracket.

66
00:04:25,990 --> 00:04:30,840
I'm still in the ROUND function so this is the number argument for ROUND.

67
00:04:31,060 --> 00:04:36,400
Now I need to decide to how many digits I want to round this. We said to the nearest integer, so zero.

68
00:04:36,400 --> 00:04:38,140
Close bracket, press enter.

69
00:04:38,320 --> 00:04:42,090
That's my average salary for productivity.

70
00:04:42,220 --> 00:04:50,600
Now let's just push this down and just make sure everything works well. That looks good.

71
00:04:50,650 --> 00:04:52,780
That's the average salary for Game.

72
00:04:52,780 --> 00:04:54,760
So that was number three.

73
00:04:54,760 --> 00:05:01,150
Number three is done. Number four, is the salary above or below average?

74
00:05:01,630 --> 00:05:06,700
The average salary by division, is that above or below the overall average.

75
00:05:06,700 --> 00:05:13,110
Use a function to put above, below or nothing depending on the salary comparisons.

76
00:05:13,130 --> 00:05:15,400
I'm comparing this number to this number

77
00:05:15,550 --> 00:05:23,680
and then I need to get an outcome back. Which function can I use here? The "IF" function. Start off with IF.

78
00:05:23,710 --> 00:05:30,730
Our logical test is, is this number greater than this number.

79
00:05:30,730 --> 00:05:32,170
Now what about my fixing.

80
00:05:32,170 --> 00:05:34,810
Do I need to fix G13?

81
00:05:34,960 --> 00:05:39,950
I don't, right? I want that to come down when I pull down the formula.

82
00:05:40,080 --> 00:05:41,500
Do I need to fix G9?

83
00:05:42,040 --> 00:05:48,100
Yes, I do. I have to press F4 because I don't want that to move. If this number is greater than this number

84
00:05:49,000 --> 00:05:50,770
then what do I want back?

85
00:05:50,770 --> 00:05:54,470
I want to see "Above".

86
00:05:54,680 --> 00:06:01,130
I need to put my text in quotation marks. Because remember anytime you use text in a formula you need

87
00:06:01,130 --> 00:06:02,560
the quotation marks.

88
00:06:02,630 --> 00:06:06,400
Then what do I want to see if it's false?

89
00:06:06,410 --> 00:06:10,170
So if G13 is not greater than G9.

90
00:06:10,970 --> 00:06:16,730
Well I need to do another check because if it's below this, I want to see below.

91
00:06:16,730 --> 00:06:20,570
But if it's exactly identical I want to see nothing.

92
00:06:21,140 --> 00:06:22,950
So let's do another IF.

93
00:06:23,240 --> 00:06:26,040
So now I have a nested IF argument.

94
00:06:26,420 --> 00:06:30,270
If this number is less than this number.

95
00:06:30,300 --> 00:06:31,690
So again let's fix it.

96
00:06:32,650 --> 00:06:36,250
Then I want to see "below"

97
00:06:36,250 --> 00:06:42,240
Otherwise, if neither of these occurs it means the values are identical.

98
00:06:42,250 --> 00:06:49,510
I want to see nothing. And nothing is quotation quotation, close bracket, and close bracket again.

99
00:06:49,510 --> 00:06:51,910
For the second IF, press enter.

100
00:06:51,940 --> 00:06:53,070
This is above.

101
00:06:53,110 --> 00:06:54,090
That's correct.

102
00:06:54,100 --> 00:06:54,970
I send this down.

103
00:06:55,600 --> 00:06:59,050
This is below and that's correct.

104
00:06:59,110 --> 00:07:01,900
That was number four. Number four is done.

105
00:07:01,930 --> 00:07:03,280
Challenge completed.