Module 3 - End of Module Project 1 #part2

1 00:00:00,179 --> 00:00:04,259 all right continuing from the step 6 it

2 00:00:02,639 --> 00:00:07,080 is saying create vlookup function to

3 00:00:04,259 --> 00:00:09,240 complete the summary section in cell I6

4 00:00:07,080 --> 00:00:10,920 so I6 create a vlooker function to

5 00:00:09,240 --> 00:00:13,080 display the number of how worked in the

6 00:00:10,920 --> 00:00:15,660 selected week look up to the week number

7 00:00:13,080 --> 00:00:18,060 initial I5 arrange this and return the

8 00:00:15,660 --> 00:00:20,939 value in second column

9 00:00:18,060 --> 00:00:22,740 and so select in this cell and to use

10 00:00:20,939 --> 00:00:26,699 any function we should start with equals

11 00:00:22,740 --> 00:00:31,080 to sine so B look up and then it accepts

12 00:00:26,699 --> 00:00:33,480 lookup value so lookup value is i5 and

13 00:00:31,080 --> 00:00:38,100 then it accepts a table array means

14 00:00:33,480 --> 00:00:40,680 range a 7 through G20 and then column

15 00:00:38,100 --> 00:00:42,660 index means second column

16 00:00:40,680 --> 00:00:44,280 and then here is a tricky body it is

17 00:00:42,660 --> 00:00:48,000 saying use absolute reference for the

18 00:00:44,280 --> 00:00:50,100 cell i5 and the range uh so

19 00:00:48,000 --> 00:00:53,760 to convert this into the absolute

20 00:00:50,100 --> 00:00:55,559 reference we should hit F4 key F4 the

21 00:00:53,760 --> 00:00:58,079 state once and it will add dollar dollar

22 00:00:55,559 --> 00:01:00,239 sign it means uh row and column will be

23 00:00:58,079 --> 00:01:02,399 constant if we drag this formula to

24 00:01:00,239 --> 00:01:04,739 another cell if we copy this and paste

25 00:01:02,399 --> 00:01:06,420 this formula to other cell then this

26 00:01:04,739 --> 00:01:09,240 will not be changed on the best of the

27 00:01:06,420 --> 00:01:12,060 cell position this will be constant so

28 00:01:09,240 --> 00:01:13,680 that is absolute difference and we

29 00:01:12,060 --> 00:01:18,299 should also convert this so just click

30 00:01:13,680 --> 00:01:21,000 here and then hit F4 and then click here

31 00:01:18,299 --> 00:01:23,280 or you just select select this

32 00:01:21,000 --> 00:01:25,979 select this range

33 00:01:23,280 --> 00:01:28,680 and then hit the F4

34 00:01:25,979 --> 00:01:31,860 and hit enter this is done

35 00:01:28,680 --> 00:01:35,700 so copy the formula from cell I6 to the

36 00:01:31,860 --> 00:01:37,500 range i7 through i11 so here is I6 and

37 00:01:35,700 --> 00:01:39,780 if you move the cursor in the corner

38 00:01:37,500 --> 00:01:42,900 then you can see the plus sign and just

39 00:01:39,780 --> 00:01:45,320 hold it and it is saying I 6 through I

40 00:01:42,900 --> 00:01:45,320 love it

41 00:01:45,780 --> 00:01:50,700 all right so

42 00:01:48,060 --> 00:01:53,280 um and then it is saying I6 through I

43 00:01:50,700 --> 00:01:55,860 Library I6 through and edit the formula

44 00:01:53,280 --> 00:01:59,939 in cell I 7 to return the value in third

45 00:01:55,860 --> 00:02:03,960 column so in Isla i7 we should edit it

46 00:01:59,939 --> 00:02:06,780 to third column hit enter and then for

47 00:02:03,960 --> 00:02:09,179 I8 it is saying

48 00:02:06,780 --> 00:02:11,640 it is saying fourth column and then I9

49 00:02:09,179 --> 00:02:15,420 fifth column and then I 10th sixth

50 00:02:11,640 --> 00:02:18,060 column and then I 11 7. okay so these

51 00:02:15,420 --> 00:02:19,560 are three this is

52 00:02:18,060 --> 00:02:22,200 four

53 00:02:19,560 --> 00:02:23,760 and then this is

54 00:02:22,200 --> 00:02:27,379 five

55 00:02:23,760 --> 00:02:27,379 and then this is

56 00:02:29,940 --> 00:02:34,800 so in i11 it is saying sixth column now

57 00:02:32,819 --> 00:02:37,800 seventh column

58 00:02:34,800 --> 00:02:40,020 all right so this is done now moving on

59 00:02:37,800 --> 00:02:42,660 to step 8 add formula to complete the

60 00:02:40,020 --> 00:02:46,800 balance section in cell create a formula

61 00:02:42,660 --> 00:02:48,840 using song function so okay it

62 00:02:46,800 --> 00:02:51,060 this kid

63 00:02:48,840 --> 00:02:55,319 using some function that calculates the

64 00:02:51,060 --> 00:02:57,660 total range and subtract it okay so the

65 00:02:55,319 --> 00:03:01,280 sum function

66 00:02:57,660 --> 00:03:01,280 is 17 through

67 00:03:01,680 --> 00:03:08,940 that creates some function

68 00:03:03,599 --> 00:03:12,120 well it's a d17 I bet d17 D20

69 00:03:08,940 --> 00:03:15,000 and subtract it from the cell in from

70 00:03:12,120 --> 00:03:16,920 the value in cell j8 so it says from

71 00:03:15,000 --> 00:03:18,599 that means we cannot do like this we

72 00:03:16,920 --> 00:03:21,300 cannot do like this

73 00:03:18,599 --> 00:03:23,459 the problem means it's subtract the

74 00:03:21,300 --> 00:03:27,540 value from Z

75 00:03:23,459 --> 00:03:31,080 oh from J yeah from j8 which will do

76 00:03:27,540 --> 00:03:33,420 like this so so now that makes sense so

77 00:03:31,080 --> 00:03:36,120 initial gate create a formula using a

78 00:03:33,420 --> 00:03:37,980 sum function and subtract it from the

79 00:03:36,120 --> 00:03:40,200 value of Z from the value we should

80 00:03:37,980 --> 00:03:43,080 subtract it not to the value it's from

81 00:03:40,200 --> 00:03:44,180 the values that's why we should do j8

82 00:03:43,080 --> 00:03:47,900 first

83 00:03:44,180 --> 00:03:47,900 uh so hit enter

84 00:03:48,480 --> 00:03:54,599 now in CL again I create a song that

85 00:03:51,060 --> 00:03:57,239 calculates is the same so

86 00:03:54,599 --> 00:04:00,140 um equals

87 00:03:57,239 --> 00:04:00,140 sum

88 00:04:00,980 --> 00:04:06,720 E17 through E20

89 00:04:04,440 --> 00:04:07,739 and then subtract it from so this is

90 00:04:06,720 --> 00:04:11,040 also from

91 00:04:07,739 --> 00:04:13,319 from Main cell j9 and then subtract it

92 00:04:11,040 --> 00:04:16,019 in the

93 00:04:13,319 --> 00:04:17,820 inculcating create a formula using some

94 00:04:16,019 --> 00:04:20,459 function that calculates the total of

95 00:04:17,820 --> 00:04:22,639 range U and subtract it from the cell so

96 00:04:20,459 --> 00:04:26,280 this is also same

97 00:04:22,639 --> 00:04:28,340 equals J 10 subtract

98 00:04:26,280 --> 00:04:33,000 some

99 00:04:28,340 --> 00:04:37,139 uh you have 17 through F20

100 00:04:33,000 --> 00:04:40,440 and then from uh from these adds the

101 00:04:37,139 --> 00:04:41,699 total of the range z17 now after this we

102 00:04:40,440 --> 00:04:44,240 shall add

103 00:04:41,699 --> 00:04:47,460 the song

104 00:04:44,240 --> 00:04:50,220 g17 through G20

105 00:04:47,460 --> 00:04:53,220 and then subtracts 160 so it is saying

106 00:04:50,220 --> 00:04:55,440 the subtracts if if it was from then we

107 00:04:53,220 --> 00:04:58,199 should do from the ahead if it if it's

108 00:04:55,440 --> 00:05:02,340 just a subtract then we can do like this

109 00:04:58,199 --> 00:05:04,440 so hit enter so it's 8.50

110 00:05:02,340 --> 00:05:07,020 and then copy the formula in the cell

111 00:05:04,440 --> 00:05:09,600 j11

112 00:05:07,020 --> 00:05:12,080 2K11

113 00:05:09,600 --> 00:05:12,080 so

114 00:05:15,660 --> 00:05:21,180 notice is just you just remove this

115 00:05:17,940 --> 00:05:23,039 borderline so just copy this formula

116 00:05:21,180 --> 00:05:24,840 and then hit

117 00:05:23,039 --> 00:05:27,120 Ctrl Z

118 00:05:24,840 --> 00:05:31,020 and just go here and then paste this

119 00:05:27,120 --> 00:05:33,660 formula restart

120 00:05:31,020 --> 00:05:34,919 uh so copy this this is done in the cell

121 00:05:33,660 --> 00:05:36,720 j16

122 00:05:34,919 --> 00:05:40,639 uh create a formula using average

123 00:05:36,720 --> 00:05:42,419 function that calculates average is 16

124 00:05:40,639 --> 00:05:45,380 j16

125 00:05:42,419 --> 00:05:45,380 so average

126 00:05:46,380 --> 00:05:57,419 and then it accepts range B9 through F12

127 00:05:51,960 --> 00:06:02,820 and then hit enter in Cell j17 Max

128 00:05:57,419 --> 00:06:06,320 Max from B9 through F12 hit enter

129 00:06:02,820 --> 00:06:06,320 and then mean

130 00:06:06,720 --> 00:06:13,620 from B9 through a F12

131 00:06:10,680 --> 00:06:14,759 and then hit enter so let's just take

132 00:06:13,620 --> 00:06:20,340 our answer

133 00:06:14,759 --> 00:06:22,259 it's 7.2 9.5 and then 56 14 878

134 00:06:20,340 --> 00:06:24,300 so yeah this is done thank you so much

135 00:06:22,259 --> 00:06:26,539 you can ask any question in the comment

136 00:06:24,300 --> 00:06:26,539 section