Module 7: SAM Project 1b

1 00:00:00,780 --> 00:00:05,880 all right so this is module 7 project 1B

2 00:00:04,020 --> 00:00:08,940 and in this step one it is saying

3 00:00:05,880 --> 00:00:11,340 Brittany Lambert is a volunteer for Bray

4 00:00:08,940 --> 00:00:14,400 Ward country swim clubs in Melbourne

5 00:00:11,340 --> 00:00:16,440 Florida so it is saying switch to the

6 00:00:14,400 --> 00:00:18,480 employees worksheet so here in the

7 00:00:16,440 --> 00:00:21,779 bottom corner there is option just click

8 00:00:18,480 --> 00:00:23,939 here and then in cell E3 enter formula

9 00:00:21,779 --> 00:00:27,060 using as lookup function

10 00:00:23,939 --> 00:00:29,400 so E3 as follows to determine an

11 00:00:27,060 --> 00:00:32,220 employee's potential period which is

12 00:00:29,400 --> 00:00:33,960 based on their years of experience so to

13 00:00:32,220 --> 00:00:36,180 enter any formula we should require we

14 00:00:33,960 --> 00:00:39,840 shall start with equals to sign

15 00:00:36,180 --> 00:00:42,059 so and look up the casing doesn't matter

16 00:00:39,840 --> 00:00:44,460 Capital case is small case it doesn't

17 00:00:42,059 --> 00:00:47,040 matter and it is saying use a structure

18 00:00:44,460 --> 00:00:49,559 reference to look up the value in the

19 00:00:47,040 --> 00:00:51,480 year of experience column

20 00:00:49,559 --> 00:00:54,420 so most people make mistake here they

21 00:00:51,480 --> 00:00:56,039 just select all this column

22 00:00:54,420 --> 00:00:57,600 oops

23 00:00:56,039 --> 00:00:59,940 all this column

24 00:00:57,600 --> 00:01:01,680 but it just required a value not the

25 00:00:59,940 --> 00:01:04,199 whole column it is saying lookup value

26 00:01:01,680 --> 00:01:06,900 so it's a secret of value from this

27 00:01:04,199 --> 00:01:10,320 column so for that what we should do is

28 00:01:06,900 --> 00:01:11,520 just just delete it

29 00:01:10,320 --> 00:01:13,700 okay

30 00:01:11,520 --> 00:01:13,700 foreign

31 00:01:15,740 --> 00:01:22,920 table array so table array means range

32 00:01:19,439 --> 00:01:27,920 so don't put two we should put p14

33 00:01:22,920 --> 00:01:33,240 through u15 so p14 through

34 00:01:27,920 --> 00:01:35,220 u15 and in Row 2 so in Row 2 using an

35 00:01:33,240 --> 00:01:37,560 absolute reference so for this range we

36 00:01:35,220 --> 00:01:40,619 should use absolute reference for this

37 00:01:37,560 --> 00:01:43,020 just select this range and then press F4

38 00:01:40,619 --> 00:01:45,240 key or surround row and column number

39 00:01:43,020 --> 00:01:47,880 with dollar sign

40 00:01:45,240 --> 00:01:50,880 and then lastly it is saying because uh

41 00:01:47,880 --> 00:01:53,280 hourly pay rate is based on experience

42 00:01:50,880 --> 00:01:56,820 finite approximate path so for that

43 00:01:53,280 --> 00:02:00,000 again enter comma and then

44 00:01:56,820 --> 00:02:01,680 select true for approximate match and

45 00:02:00,000 --> 00:02:05,520 then close parenthesis and then hit

46 00:02:01,680 --> 00:02:08,039 enter okay so Step One is down so step

47 00:02:05,520 --> 00:02:11,520 two is somewhere the swim club host a

48 00:02:08,039 --> 00:02:14,099 cam for youth for young swimmers to work

49 00:02:11,520 --> 00:02:16,440 as a counselor at one of the summer

50 00:02:14,099 --> 00:02:19,680 camps and employee must be at least 21

51 00:02:16,440 --> 00:02:22,319 year old with only once to determine how

52 00:02:19,680 --> 00:02:24,720 many employees are really eligible to be

53 00:02:22,319 --> 00:02:27,120 cam counselor so here's the main part in

54 00:02:24,720 --> 00:02:30,120 in cell j3

55 00:02:27,120 --> 00:02:30,120 j3

56 00:02:30,959 --> 00:02:36,300 enter a formula using the IF function

57 00:02:33,420 --> 00:02:38,520 okay so this is again this is Formula I

58 00:02:36,300 --> 00:02:41,580 will start with equals design and then

59 00:02:38,520 --> 00:02:43,319 if function so open parenthesis and

60 00:02:41,580 --> 00:02:47,099 restart the reference as follow to

61 00:02:43,319 --> 00:02:49,319 determine if Define Okay so

62 00:02:47,099 --> 00:02:52,620 the functions will use a reference to

63 00:02:49,319 --> 00:02:56,459 the eighth column to determine if the

64 00:02:52,620 --> 00:02:59,340 employee is greater than or equals to

65 00:02:56,459 --> 00:03:03,120 so for for The Logical test which will

66 00:02:59,340 --> 00:03:07,680 determine if the employees is is greater

67 00:03:03,120 --> 00:03:10,140 than or equals to 21 so employees is so

68 00:03:07,680 --> 00:03:14,400 here's the impulse yes just click here

69 00:03:10,140 --> 00:03:16,200 so if employees is is greater than or

70 00:03:14,400 --> 00:03:20,459 equals to

71 00:03:16,200 --> 00:03:22,319 21 and then press comma so this is this

72 00:03:20,459 --> 00:03:25,140 is the condition so this is the test

73 00:03:22,319 --> 00:03:27,480 logical test now we shall enter the

74 00:03:25,140 --> 00:03:30,000 value if this condition is true so if

75 00:03:27,480 --> 00:03:32,940 this condition is true it is saying it

76 00:03:30,000 --> 00:03:35,940 should return as so for this react in

77 00:03:32,940 --> 00:03:38,280 the quotation and then type S and then

78 00:03:35,940 --> 00:03:40,379 comma Now we shall enter the value if

79 00:03:38,280 --> 00:03:42,959 this condition is false

80 00:03:40,379 --> 00:03:46,860 so if this condition is false which will

81 00:03:42,959 --> 00:03:50,819 return no so I can reap it in quotation

82 00:03:46,860 --> 00:03:54,239 and then this is done so close it with

83 00:03:50,819 --> 00:03:57,180 close parenthesis so if function takes

84 00:03:54,239 --> 00:03:59,580 condition and then value if it is true

85 00:03:57,180 --> 00:04:03,299 value if it is false so the condition is

86 00:03:59,580 --> 00:04:05,819 if a is value is greater than 21 then if

87 00:04:03,299 --> 00:04:07,980 it is true yes if it is false no

88 00:04:05,819 --> 00:04:11,220 and then hit enter

89 00:04:07,980 --> 00:04:13,200 okay so step two is down now step three

90 00:04:11,220 --> 00:04:15,299 to be eligible to work with the swim

91 00:04:13,200 --> 00:04:17,639 team and employee must have at least two

92 00:04:15,299 --> 00:04:22,260 years of experience or a completed CPR

93 00:04:17,639 --> 00:04:24,900 training in cell K3 okay in cell K3

94 00:04:22,260 --> 00:04:26,699 again if function this time if function

95 00:04:24,900 --> 00:04:29,520 and all function

96 00:04:26,699 --> 00:04:31,259 so this will be interesting so for E any

97 00:04:29,520 --> 00:04:33,000 function we should start with equals to

98 00:04:31,259 --> 00:04:36,180 sign if function

99 00:04:33,000 --> 00:04:40,139 and then open parenthesis

100 00:04:36,180 --> 00:04:42,300 now the functions will determine if the

101 00:04:40,139 --> 00:04:45,840 employees years of experience is greater

102 00:04:42,300 --> 00:04:49,080 than or equals to 2 or if employee CPR

103 00:04:45,840 --> 00:04:51,680 training status is as

104 00:04:49,080 --> 00:04:54,840 so here are the two condition

105 00:04:51,680 --> 00:04:56,820 returning the text as if an employee

106 00:04:54,840 --> 00:04:59,940 meet one or both of the both of the

107 00:04:56,820 --> 00:05:04,740 criteria and no it they do not make

108 00:04:59,940 --> 00:05:07,080 so logical test will be if the Year oops

109 00:05:04,740 --> 00:05:09,419 what is the yeah year of experience

110 00:05:07,080 --> 00:05:12,600 so let's go to Europe experience column

111 00:05:09,419 --> 00:05:16,259 and then click here so if this is

112 00:05:12,600 --> 00:05:18,720 greater than or equals to 2 and what we

113 00:05:16,259 --> 00:05:21,180 would think is if this condition and

114 00:05:18,720 --> 00:05:23,580 then R and then where is the another

115 00:05:21,180 --> 00:05:26,400 condition CPR training status

116 00:05:23,580 --> 00:05:28,680 so for that

117 00:05:26,400 --> 00:05:32,340 it is

118 00:05:28,680 --> 00:05:35,400 CPR training status if the employee CPR

119 00:05:32,340 --> 00:05:38,520 Trend status so here is just click here

120 00:05:35,400 --> 00:05:40,560 and then if this

121 00:05:38,520 --> 00:05:41,820 equals

122 00:05:40,560 --> 00:05:45,180 as

123 00:05:41,820 --> 00:05:46,979 now so if this condition is true we need

124 00:05:45,180 --> 00:05:49,979 to return

125 00:05:46,979 --> 00:05:52,320 true and then if this condition is false

126 00:05:49,979 --> 00:05:54,000 I'm going to sorry when to return s and

127 00:05:52,320 --> 00:05:56,100 if this condition is false we need to

128 00:05:54,000 --> 00:05:59,340 return no

129 00:05:56,100 --> 00:06:01,800 so at first you might think this would

130 00:05:59,340 --> 00:06:04,380 be the case but the syntax is different

131 00:06:01,800 --> 00:06:08,759 so this is the condition what the syntax

132 00:06:04,380 --> 00:06:11,280 is different so for that we should do if

133 00:06:08,759 --> 00:06:14,220 and then inside this

134 00:06:11,280 --> 00:06:17,639 we should do

135 00:06:14,220 --> 00:06:19,380 R and then open parenthesis and then

136 00:06:17,639 --> 00:06:22,020 this test

137 00:06:19,380 --> 00:06:24,500 okay after this test we should do

138 00:06:22,020 --> 00:06:24,500 here

139 00:06:25,319 --> 00:06:31,039 oops comma and then close parenthesis

140 00:06:31,080 --> 00:06:36,300 so all we'll take two parameters and if

141 00:06:33,900 --> 00:06:39,000 we'll compute this condition if this is

142 00:06:36,300 --> 00:06:41,580 true or if this is true and then print

143 00:06:39,000 --> 00:06:43,919 this if this is true and then print this

144 00:06:41,580 --> 00:06:45,620 if there is false so before I think that

145 00:06:43,919 --> 00:06:48,600 made more sense to you

146 00:06:45,620 --> 00:06:52,199 that's the same case but the syntax is

147 00:06:48,600 --> 00:06:54,780 different so I reformat it in this way

148 00:06:52,199 --> 00:06:57,960 so let me check one more time so this is

149 00:06:54,780 --> 00:07:00,539 the bracket synthesis open close so this

150 00:06:57,960 --> 00:07:04,199 must be valid formula so let's test it

151 00:07:00,539 --> 00:07:07,380 okay so step three is down

152 00:07:04,199 --> 00:07:09,600 after that step four employees may serve

153 00:07:07,380 --> 00:07:12,600 as swim team courses if they are at

154 00:07:09,600 --> 00:07:16,259 least is 23 and have at least three

155 00:07:12,600 --> 00:07:19,500 years of experience in cell L3 enter a

156 00:07:16,259 --> 00:07:21,360 formula using IF and and function so

157 00:07:19,500 --> 00:07:23,400 here also we need two we need to enter

158 00:07:21,360 --> 00:07:25,319 two function IF function and end

159 00:07:23,400 --> 00:07:27,440 function

160 00:07:25,319 --> 00:07:30,479 so for that we start with equals to sign

161 00:07:27,440 --> 00:07:35,160 and then if and then just as previous

162 00:07:30,479 --> 00:07:40,319 end now this will take two parameter

163 00:07:35,160 --> 00:07:43,500 so uh if functions will if greater than

164 00:07:40,319 --> 00:07:46,139 or equals to 23 so employee is so let's

165 00:07:43,500 --> 00:07:49,620 go to the as column and then click here

166 00:07:46,139 --> 00:07:53,280 so if this is greater than or equals to

167 00:07:49,620 --> 00:07:55,500 23 and then another condition is

168 00:07:53,280 --> 00:07:59,039 years of experience are greater than or

169 00:07:55,500 --> 00:08:02,280 equals to 3 so click here and then

170 00:07:59,039 --> 00:08:05,099 greater than or equals to 3.

171 00:08:02,280 --> 00:08:07,440 now close this parenthesis and then it

172 00:08:05,099 --> 00:08:08,819 is saying return yes if this condition

173 00:08:07,440 --> 00:08:12,960 is true

174 00:08:08,819 --> 00:08:14,539 just as previous case and then oops

175 00:08:12,960 --> 00:08:18,000 and then

176 00:08:14,539 --> 00:08:20,220 know if this condition is false now

177 00:08:18,000 --> 00:08:22,379 close this parenthesis

178 00:08:20,220 --> 00:08:25,620 and then

179 00:08:22,379 --> 00:08:27,660 eat in there okay so step four is also

180 00:08:25,620 --> 00:08:31,020 down now step five

181 00:08:27,660 --> 00:08:33,360 so Brittany wants to identify employees

182 00:08:31,020 --> 00:08:36,659 who are eligible to take a CPR course at

183 00:08:33,360 --> 00:08:39,000 the club's expense employee who can work

184 00:08:36,659 --> 00:08:40,919 as camp counselor eligible for this

185 00:08:39,000 --> 00:08:42,779 course in cell entry

186 00:08:40,919 --> 00:08:45,060 so N3

187 00:08:42,779 --> 00:08:47,820 enter a formula using a nested e

188 00:08:45,060 --> 00:08:50,279 function and structure reference as

189 00:08:47,820 --> 00:08:53,339 followed to determine so here we need to

190 00:08:50,279 --> 00:08:55,200 use nested IF function

191 00:08:53,339 --> 00:08:58,320 all right

192 00:08:55,200 --> 00:09:01,260 so again let's start with equals to so

193 00:08:58,320 --> 00:09:03,480 if if the value in cprint column is

194 00:09:01,260 --> 00:09:05,660 equals to text X

195 00:09:03,480 --> 00:09:08,880 and the formula sold

196 00:09:05,660 --> 00:09:10,080 displayed Trend as text

197 00:09:08,880 --> 00:09:13,380 okay

198 00:09:10,080 --> 00:09:15,180 so if open parenthesis now CPR Trend

199 00:09:13,380 --> 00:09:16,200 column so let's first that source that

200 00:09:15,180 --> 00:09:19,580 column

201 00:09:16,200 --> 00:09:19,580 CPR trying to

202 00:09:19,620 --> 00:09:25,019 oh here it is so if CPR column is equals

203 00:09:23,279 --> 00:09:26,519 to

204 00:09:25,019 --> 00:09:28,800 yes

205 00:09:26,519 --> 00:09:33,240 then we should display

206 00:09:28,800 --> 00:09:35,399 trained so value if true is

207 00:09:33,240 --> 00:09:39,180 [Music]

208 00:09:35,399 --> 00:09:42,000 Trend and then for value is false so if

209 00:09:39,180 --> 00:09:43,500 this case is false then otherwise going

210 00:09:42,000 --> 00:09:46,620 to display

211 00:09:43,500 --> 00:09:48,779 if the value in the cam Consular column

212 00:09:46,620 --> 00:09:50,760 is equals to the text X

213 00:09:48,779 --> 00:09:52,680 so again we're here when you enter

214 00:09:50,760 --> 00:09:55,680 another if condition

215 00:09:52,680 --> 00:09:57,660 so for this it is saying cam counselor

216 00:09:55,680 --> 00:10:00,720 so select here

217 00:09:57,660 --> 00:10:03,899 and then if Cam counselor

218 00:10:00,720 --> 00:10:06,240 is equals to s

219 00:10:03,899 --> 00:10:09,600 and then if this condition is true now

220 00:10:06,240 --> 00:10:12,000 we need to display yes

221 00:10:09,600 --> 00:10:13,440 now this can if that condition is false

222 00:10:12,000 --> 00:10:14,640 we have to display

223 00:10:13,440 --> 00:10:16,680 no

224 00:10:14,640 --> 00:10:18,540 now close parenthesis for this if

225 00:10:16,680 --> 00:10:21,660 condition and then again another close

226 00:10:18,540 --> 00:10:24,959 parenthesis for this first if condition

227 00:10:21,660 --> 00:10:28,740 so for here here is the condition so if

228 00:10:24,959 --> 00:10:31,740 CPR trend is s we will display trend

229 00:10:28,740 --> 00:10:33,600 and if CPR trend is not other value then

230 00:10:31,740 --> 00:10:36,000 s then we will

231 00:10:33,600 --> 00:10:38,160 get into this part and this part is

232 00:10:36,000 --> 00:10:40,200 again another if condition so if the cam

233 00:10:38,160 --> 00:10:43,380 counselor is as then it will display s

234 00:10:40,200 --> 00:10:45,839 and if the cam counselor is other value

235 00:10:43,380 --> 00:10:48,660 then X then it will display no

236 00:10:45,839 --> 00:10:50,880 so if you look carefully it will not be

237 00:10:48,660 --> 00:10:52,320 that difficult

238 00:10:50,880 --> 00:10:54,860 now hit enter

239 00:10:52,320 --> 00:10:54,860 okay

240 00:10:55,140 --> 00:11:00,300 so

241 00:10:57,060 --> 00:11:03,240 5 is down now let's go to 6

242 00:11:00,300 --> 00:11:05,720 so employee who worked at Swim Club are

243 00:11:03,240 --> 00:11:08,339 also considered for employment at the

244 00:11:05,720 --> 00:11:10,980 Braveheart quantifiers and Recreation

245 00:11:08,339 --> 00:11:13,680 Department employee with more than four

246 00:11:10,980 --> 00:11:17,880 years of experience are qualified for

247 00:11:13,680 --> 00:11:20,399 more complex level 2 jobs so in sale and

248 00:11:17,880 --> 00:11:23,459 to enter the text level

249 00:11:20,399 --> 00:11:24,959 okay so just double click or click on

250 00:11:23,459 --> 00:11:28,079 this bar

251 00:11:24,959 --> 00:11:31,920 and then it is saying to enter level

252 00:11:28,079 --> 00:11:35,880 so level there now in cell and three

253 00:11:31,920 --> 00:11:39,779 enter a formula using the IF function

254 00:11:35,880 --> 00:11:41,899 so again equals to sine IF function

255 00:11:39,779 --> 00:11:45,420 any standard reference

256 00:11:41,899 --> 00:11:47,880 which employee which employment level

257 00:11:45,420 --> 00:11:49,800 Divinity is qualified for so the

258 00:11:47,880 --> 00:11:51,779 functions will determine if employees

259 00:11:49,800 --> 00:11:55,079 years of experience is greater than or

260 00:11:51,779 --> 00:11:56,640 equals to 4. so let's go to years of

261 00:11:55,079 --> 00:11:59,339 experience column

262 00:11:56,640 --> 00:12:01,440 and then click here if employees years

263 00:11:59,339 --> 00:12:04,140 of experience so this one is greater

264 00:12:01,440 --> 00:12:07,680 than or equals to 4 so just as previous

265 00:12:04,140 --> 00:12:12,060 greater than or equals to 4

266 00:12:07,680 --> 00:12:14,640 then return the value 2 a 2 is true so

267 00:12:12,060 --> 00:12:18,420 if this condition is true going to

268 00:12:14,640 --> 00:12:20,519 return to any this condition is false we

269 00:12:18,420 --> 00:12:23,100 have to return one

270 00:12:20,519 --> 00:12:26,760 and now close parenthesis and then enter

271 00:12:23,100 --> 00:12:29,220 okay so step 7 is also down now step 8

272 00:12:26,760 --> 00:12:31,440 it is saying returning created a formula

273 00:12:29,220 --> 00:12:33,899 with the vlooker function to look up and

274 00:12:31,440 --> 00:12:36,360 employee names by their employee ID she

275 00:12:33,899 --> 00:12:40,380 also want to alert user when they enter

276 00:12:36,360 --> 00:12:44,820 and correct ID number in cell Q4

277 00:12:40,380 --> 00:12:48,120 okay so Q4

278 00:12:44,820 --> 00:12:51,000 miss the existing vlooker function in an

279 00:12:48,120 --> 00:12:53,519 if error function if B lookup function

280 00:12:51,000 --> 00:12:55,800 returns an error function the text

281 00:12:53,519 --> 00:12:58,980 invalids will be displayed

282 00:12:55,800 --> 00:13:01,800 so if this function Returns the error

283 00:12:58,980 --> 00:13:08,160 then we need to display invalid employee

284 00:13:01,800 --> 00:13:11,220 ID so for that let's cut that now if

285 00:13:08,160 --> 00:13:13,440 error and then open parenthesis say it

286 00:13:11,220 --> 00:13:17,100 takes value so value will be this

287 00:13:13,440 --> 00:13:18,839 formula and then if if error occur then

288 00:13:17,100 --> 00:13:21,120 we will display

289 00:13:18,839 --> 00:13:23,720 this one let me copy it

290 00:13:21,120 --> 00:13:23,720 oops

291 00:13:24,000 --> 00:13:29,459 copy now okay

292 00:13:27,060 --> 00:13:32,660 let me type

293 00:13:29,459 --> 00:13:32,660 this will be easy

294 00:13:33,560 --> 00:13:37,620 invalid employee ID okay

295 00:13:36,480 --> 00:13:39,839 oh

296 00:13:37,620 --> 00:13:41,820 it exceeds so

297 00:13:39,839 --> 00:13:42,660 just click here and it will show all of

298 00:13:41,820 --> 00:13:45,600 them

299 00:13:42,660 --> 00:13:47,459 so invalid employee ID and then close

300 00:13:45,600 --> 00:13:49,620 parenthesis

301 00:13:47,459 --> 00:13:51,360 okay so this seems to be right now hit

302 00:13:49,620 --> 00:13:54,839 enter

303 00:13:51,360 --> 00:13:57,060 and now so step 8 is also down now step

304 00:13:54,839 --> 00:13:59,040 9 return you want to determine wants to

305 00:13:57,060 --> 00:14:02,459 determine several protocols and average

306 00:13:59,040 --> 00:14:06,060 for active employees in cell q9

307 00:14:02,459 --> 00:14:08,040 so q9 enter the formula using the

308 00:14:06,060 --> 00:14:09,000 average IF function and structure

309 00:14:08,040 --> 00:14:13,019 reference

310 00:14:09,000 --> 00:14:16,820 Okay so so equals to

311 00:14:13,019 --> 00:14:16,820 if we are oops

312 00:14:17,100 --> 00:14:22,800 average IF function okay

313 00:14:20,839 --> 00:14:24,920 reference to determine the average

314 00:14:22,800 --> 00:14:27,839 number of years of experience for life

315 00:14:24,920 --> 00:14:30,320 so average IF function so it will takes

316 00:14:27,839 --> 00:14:32,639 range criteria

317 00:14:30,320 --> 00:14:36,660 all right

318 00:14:32,639 --> 00:14:42,680 Oh I thought I think we skip number nine

319 00:14:36,660 --> 00:14:42,680 number ID is saying in cell q9 so q9 is

320 00:14:42,779 --> 00:14:47,339 q9 is for countif oops you might have

321 00:14:45,540 --> 00:14:49,860 noticed that

322 00:14:47,339 --> 00:14:52,019 uh

323 00:14:49,860 --> 00:14:54,839 count if

324 00:14:52,019 --> 00:14:57,360 function to the list of the reference to

325 00:14:54,839 --> 00:15:00,240 count the number of employees who can

326 00:14:57,360 --> 00:15:03,720 work as work as lifeguard

327 00:15:00,240 --> 00:15:06,420 so range Reigns will be who can work

328 00:15:03,720 --> 00:15:07,680 work as lifeguard

329 00:15:06,420 --> 00:15:10,199 okay

330 00:15:07,680 --> 00:15:13,699 so for that it requires range so range

331 00:15:10,199 --> 00:15:13,699 means this whole value

332 00:15:14,339 --> 00:15:23,040 okay lifeguard no comma and then

333 00:15:17,339 --> 00:15:26,459 criteria so criteria means f s sorry

334 00:15:23,040 --> 00:15:28,800 so if if any value of this column return

335 00:15:26,459 --> 00:15:32,040 as then it will count

336 00:15:28,800 --> 00:15:35,040 so close parenthesis and then enter all

337 00:15:32,040 --> 00:15:37,639 right so step nine is also down now step

338 00:15:35,040 --> 00:15:41,639 10 in R9

339 00:15:37,639 --> 00:15:44,820 so R9 I am from fuser because I did this

340 00:15:41,639 --> 00:15:47,100 wrong I put these stupid values so let

341 00:15:44,820 --> 00:15:49,139 me correct it so for this

342 00:15:47,100 --> 00:15:51,180 number 10 it is saying enter formula

343 00:15:49,139 --> 00:15:52,800 using the average IF function and

344 00:15:51,180 --> 00:15:55,260 structure reference to determine the

345 00:15:52,800 --> 00:15:57,839 average number of years of experience

346 00:15:55,260 --> 00:15:59,399 for lifeguard so years of experience and

347 00:15:57,839 --> 00:16:01,560 lifeguard is the keyword of these

348 00:15:59,399 --> 00:16:05,180 questions I'm going to focus on this so

349 00:16:01,560 --> 00:16:05,180 it is saying average if range

350 00:16:10,500 --> 00:16:16,680 so just select all of these

351 00:16:14,699 --> 00:16:19,500 all these

352 00:16:16,680 --> 00:16:20,820 and then comma and then criteria will be

353 00:16:19,500 --> 00:16:24,240 as

354 00:16:20,820 --> 00:16:26,040 so if these are the lifeguards

355 00:16:24,240 --> 00:16:28,620 and then it is saying numbers of

356 00:16:26,040 --> 00:16:31,220 experiences now if

357 00:16:28,620 --> 00:16:34,560 if in this row if the value is s

358 00:16:31,220 --> 00:16:37,199 calculate and the average of the years

359 00:16:34,560 --> 00:16:40,199 of experience of those like that so for

360 00:16:37,199 --> 00:16:42,600 that just select all this column

361 00:16:40,199 --> 00:16:44,639 on this column so this is the curved

362 00:16:42,600 --> 00:16:47,579 formula so let me repeat it again so it

363 00:16:44,639 --> 00:16:49,019 is saying average if any stocks are

364 00:16:47,579 --> 00:16:51,180 reference to determine the average

365 00:16:49,019 --> 00:16:53,759 number of years of experience for

366 00:16:51,180 --> 00:16:56,100 lifeguard so it is saying for lifeguard

367 00:16:53,759 --> 00:16:58,199 so for that we should determine if these

368 00:16:56,100 --> 00:17:00,120 are the Live card if these people are

369 00:16:58,199 --> 00:17:03,720 knife guard or not

370 00:17:00,120 --> 00:17:06,419 so for that we we did this filter so if

371 00:17:03,720 --> 00:17:08,459 this range has the value as that means

372 00:17:06,419 --> 00:17:11,400 these are the Lifeguard and then for

373 00:17:08,459 --> 00:17:14,220 this uh calculate the average for the

374 00:17:11,400 --> 00:17:17,579 years of experience number 11 so it is

375 00:17:14,220 --> 00:17:21,620 saying in sale at 10 so 18 internal

376 00:17:17,579 --> 00:17:21,620 formula using average function so

377 00:17:21,720 --> 00:17:25,919 plain old address function and it stops

378 00:17:24,780 --> 00:17:27,720 the reference to determine the average

379 00:17:25,919 --> 00:17:29,520 for years of accident so this will be

380 00:17:27,720 --> 00:17:33,799 easy just select

381 00:17:29,520 --> 00:17:33,799 number of experience for lifeguard

382 00:17:34,860 --> 00:17:38,880 and then that's it

383 00:17:37,200 --> 00:17:41,059 and then hit enter it will find the

384 00:17:38,880 --> 00:17:41,059 address

385 00:17:41,280 --> 00:17:47,520 okay so number 11 is done now number 12

386 00:17:44,580 --> 00:17:49,980 switch to Melbourne swim team what seeds

387 00:17:47,520 --> 00:17:53,760 so this seems to be here this is the one

388 00:17:49,980 --> 00:17:58,260 and it is in cell b14

389 00:17:53,760 --> 00:18:01,740 let me collapse it so in cell b for pin

390 00:17:58,260 --> 00:18:03,480 so this is the one v14 use the index

391 00:18:01,740 --> 00:18:05,880 function and structure reference to

392 00:18:03,480 --> 00:18:09,500 display the value in the first row and

393 00:18:05,880 --> 00:18:09,500 First Column of the okay

394 00:18:09,539 --> 00:18:13,580 so use the index function

395 00:18:16,860 --> 00:18:21,480 so is this the same way there this is

396 00:18:18,900 --> 00:18:24,720 the scientific table so first let's

397 00:18:21,480 --> 00:18:26,760 select this table okay

398 00:18:24,720 --> 00:18:29,340 so no need to

399 00:18:26,760 --> 00:18:31,440 no need to add this all

400 00:18:29,340 --> 00:18:34,200 okay so this will select stream teams

401 00:18:31,440 --> 00:18:36,419 table and then second parameter is

402 00:18:34,200 --> 00:18:38,880 second argument is row number

403 00:18:36,419 --> 00:18:42,720 so row number is first row First Column

404 00:18:38,880 --> 00:18:44,760 so just enter one comma one and then

405 00:18:42,720 --> 00:18:47,700 close fantasies enter

406 00:18:44,760 --> 00:18:49,679 in cell B16 use the same function and

407 00:18:47,700 --> 00:18:53,160 structure reference to display the total

408 00:18:49,679 --> 00:18:54,720 wins for team in the youth league so

409 00:18:53,160 --> 00:18:57,660 here is the league and it is saying

410 00:18:54,720 --> 00:18:59,640 display the total wins so total wincy is

411 00:18:57,660 --> 00:19:02,760 shown here so we need to utilize these

412 00:18:59,640 --> 00:19:06,419 two column to find the answer so for

413 00:19:02,760 --> 00:19:08,700 this firstly design use summary function

414 00:19:06,419 --> 00:19:13,679 so sum if

415 00:19:08,700 --> 00:19:18,179 now range so range is this one

416 00:19:13,679 --> 00:19:20,640 okay leak range and then criteria is it

417 00:19:18,179 --> 00:19:23,360 is saying a youth league so it should

418 00:19:20,640 --> 00:19:23,360 select only

419 00:19:23,480 --> 00:19:27,960 use league and then sum range will be

420 00:19:26,940 --> 00:19:30,480 this one

421 00:19:27,960 --> 00:19:33,059 total

422 00:19:30,480 --> 00:19:36,020 oops

423 00:19:33,059 --> 00:19:39,600 uh let me

424 00:19:36,020 --> 00:19:44,780 use I lost it okay so let's start again

425 00:19:39,600 --> 00:19:44,780 it is saying um if and then

426 00:19:45,419 --> 00:19:50,120 this range

427 00:19:48,120 --> 00:19:52,559 and then

428 00:19:50,120 --> 00:19:55,500 Youth League

429 00:19:52,559 --> 00:19:56,700 and then some brains will be this

430 00:19:55,500 --> 00:19:59,760 okay

431 00:19:56,700 --> 00:20:02,760 now close parenthesis and then hit enter

432 00:19:59,760 --> 00:20:04,440 so this is done now we stay 14

433 00:20:02,760 --> 00:20:06,660 and every time you want to display them

434 00:20:04,440 --> 00:20:08,640 L1 same theme data via the league and

435 00:20:06,660 --> 00:20:10,799 here she asks you to create a pivot

436 00:20:08,640 --> 00:20:13,020 table okay to better manipulate and

437 00:20:10,799 --> 00:20:15,179 filter the data switch to the mail one

438 00:20:13,020 --> 00:20:17,220 pivot table worksheet so this is the one

439 00:20:15,179 --> 00:20:20,100 just click here and it is saying and

440 00:20:17,220 --> 00:20:25,140 then create a viewer table in cell A1

441 00:20:20,100 --> 00:20:26,340 Okay so cell A1 based on the swim teams

442 00:20:25,140 --> 00:20:28,679 table

443 00:20:26,340 --> 00:20:32,700 as follows so

444 00:20:28,679 --> 00:20:34,880 pivot table based on swims swim teams

445 00:20:32,700 --> 00:20:34,880 table

446 00:20:35,419 --> 00:20:41,160 okay so this is the same things right

447 00:20:39,000 --> 00:20:44,039 let me check yeah this is the correct

448 00:20:41,160 --> 00:20:46,200 one so it is saying A1

449 00:20:44,039 --> 00:20:48,480 now insert

450 00:20:46,200 --> 00:20:50,520 now keyword table

451 00:20:48,480 --> 00:20:56,480 from table range

452 00:20:50,520 --> 00:20:56,480 and then click these worksheet and then

453 00:20:56,640 --> 00:21:01,940 select this one okay okay send them and

454 00:20:59,580 --> 00:21:01,940 then okay

455 00:21:01,980 --> 00:21:07,320 so this is done everything will be

456 00:21:04,020 --> 00:21:09,799 vertical name to this okay

457 00:21:07,320 --> 00:21:09,799 foreign

458 00:21:10,340 --> 00:21:15,539 table as follows so that it matches

459 00:21:13,140 --> 00:21:18,120 final figure so we'll check at the last

460 00:21:15,539 --> 00:21:20,880 so to eat the name of the pivot table

461 00:21:18,120 --> 00:21:22,620 click any cell inside pivot table and

462 00:21:20,880 --> 00:21:24,780 then this tab will appear click on view

463 00:21:22,620 --> 00:21:26,880 whatever table analyze and then pivot

464 00:21:24,780 --> 00:21:30,720 table name will be this one

465 00:21:26,880 --> 00:21:33,539 which I copied from the instruction

466 00:21:30,720 --> 00:21:36,960 so add the League field and the name

467 00:21:33,539 --> 00:21:40,159 field in in that order to the row area

468 00:21:36,960 --> 00:21:40,159 so League field

469 00:21:40,860 --> 00:21:46,440 so League field in row area and then

470 00:21:44,940 --> 00:21:50,940 team name field

471 00:21:46,440 --> 00:21:54,659 so team name field in the row area okay

472 00:21:50,940 --> 00:21:57,720 and 2020 2021 and 2022 fill in there are

473 00:21:54,659 --> 00:22:01,020 to the value area so

474 00:21:57,720 --> 00:22:03,179 oops 2020

475 00:22:01,020 --> 00:22:05,820 and then 21

476 00:22:03,179 --> 00:22:07,740 and then 22.

477 00:22:05,820 --> 00:22:10,200 after that it is saying change the

478 00:22:07,740 --> 00:22:14,760 display of subtotals to show all

479 00:22:10,200 --> 00:22:18,539 subtotals at bottom of group so for that

480 00:22:14,760 --> 00:22:19,799 let's say so subtotals so just click on

481 00:22:18,539 --> 00:22:22,559 any cell

482 00:22:19,799 --> 00:22:24,780 and then this this to new tab will have

483 00:22:22,559 --> 00:22:26,280 here and then there must be soft total

484 00:22:24,780 --> 00:22:28,500 somewhere in design so here is the

485 00:22:26,280 --> 00:22:30,600 subtitles and it was saying so all

486 00:22:28,500 --> 00:22:33,059 subtitles at bottom of group so just

487 00:22:30,600 --> 00:22:35,520 click here okay

488 00:22:33,059 --> 00:22:38,940 now another Edition change the report

489 00:22:35,520 --> 00:22:43,080 layout to show in outline form

490 00:22:38,940 --> 00:22:48,059 so report layout to sew in outline from

491 00:22:43,080 --> 00:22:52,320 sorry for layout so in out light form

492 00:22:48,059 --> 00:22:55,559 Now update the sum of 2020 field in the

493 00:22:52,320 --> 00:23:00,000 value area so get this one

494 00:22:55,559 --> 00:23:02,280 to display the name 20 wins who is the

495 00:23:00,000 --> 00:23:03,900 number format so number number format

496 00:23:02,280 --> 00:23:07,559 with zero decimal

497 00:23:03,900 --> 00:23:09,419 places so value field setting and then

498 00:23:07,559 --> 00:23:12,960 number format

499 00:23:09,419 --> 00:23:16,320 and then again number format with

500 00:23:12,960 --> 00:23:19,080 zero decimal places okay

501 00:23:16,320 --> 00:23:22,080 and then okay

502 00:23:19,080 --> 00:23:24,000 now again it is saying I think it is the

503 00:23:22,080 --> 00:23:25,980 same thing fill in there to display the

504 00:23:24,000 --> 00:23:29,520 with the number number okay

505 00:23:25,980 --> 00:23:33,659 so same thing for this here belly fill

506 00:23:29,520 --> 00:23:35,039 setting and then number formate again

507 00:23:33,659 --> 00:23:38,400 number

508 00:23:35,039 --> 00:23:39,900 and then zero decimal places

509 00:23:38,400 --> 00:23:42,120 and then okay

510 00:23:39,900 --> 00:23:45,659 now same step

511 00:23:42,120 --> 00:23:48,000 number format again number

512 00:23:45,659 --> 00:23:49,559 zero decimal places hit okay and then

513 00:23:48,000 --> 00:23:51,059 again okay

514 00:23:49,559 --> 00:23:53,640 Okay so

515 00:23:51,059 --> 00:23:56,520 step 14 is down now step 15 Britain

516 00:23:53,640 --> 00:23:58,320 wants to summarize data for all teams in

517 00:23:56,520 --> 00:24:00,659 a paper table to do so she must first

518 00:23:58,320 --> 00:24:02,960 update the all team table switch to the

519 00:24:00,659 --> 00:24:07,559 all team worksheet okay

520 00:24:02,960 --> 00:24:10,080 so all teams work seed here

521 00:24:07,559 --> 00:24:12,659 and then it is saying refresh the viewer

522 00:24:10,080 --> 00:24:15,600 the viewer table data and then verify

523 00:24:12,659 --> 00:24:20,880 that 2020 when stable for the

524 00:24:15,600 --> 00:24:23,940 um Brave add Blue Wave reflects the okay

525 00:24:20,880 --> 00:24:26,039 refresh the pivot table data so before

526 00:24:23,940 --> 00:24:28,440 that we need to do something so it is

527 00:24:26,039 --> 00:24:31,740 saying uh didn't want to summarize data

528 00:24:28,440 --> 00:24:34,620 versus switch to here all teams

529 00:24:31,740 --> 00:24:40,520 worksheet and then edit the record for

530 00:24:34,620 --> 00:24:40,520 the Private Blue Wave row 26.

531 00:24:41,760 --> 00:24:47,780 so this one

532 00:24:43,799 --> 00:24:53,820 to use 10 as the 2020 field value

533 00:24:47,780 --> 00:24:57,120 so for this 2020 and row 26 use 10 so

534 00:24:53,820 --> 00:24:59,820 10. okay

535 00:24:57,120 --> 00:25:02,700 now switch to the all teamview board

536 00:24:59,820 --> 00:25:04,799 here and the interesting to refresh not

537 00:25:02,700 --> 00:25:07,440 this one this one

538 00:25:04,799 --> 00:25:08,820 this one so click here and then whatever

539 00:25:07,440 --> 00:25:14,900 analyze

540 00:25:08,820 --> 00:25:14,900 and then refresh okay so distance to 10

541 00:25:15,059 --> 00:25:20,580 now

542 00:25:16,679 --> 00:25:24,900 so 15 this we did switch to the all row

543 00:25:20,580 --> 00:25:27,659 and then verify so we verified now 17 F

544 00:25:24,900 --> 00:25:31,980 lighter lavender pivot install median 13

545 00:25:27,659 --> 00:25:34,159 favor table to the pivot table Style

546 00:25:31,980 --> 00:25:37,700 so all team pivot table works here so

547 00:25:34,159 --> 00:25:37,700 this worksheet

548 00:25:37,980 --> 00:25:45,779 okay now it is saying uh lavender pivot

549 00:25:42,779 --> 00:25:49,620 style medium 13.

550 00:25:45,779 --> 00:25:51,960 so here and then drop down

551 00:25:49,620 --> 00:25:54,840 so it was saying medium

552 00:25:51,960 --> 00:25:57,120 now where is that

553 00:25:54,840 --> 00:25:58,799 just click here over run it and it will

554 00:25:57,120 --> 00:26:00,659 show different text

555 00:25:58,799 --> 00:26:03,179 and we'll

556 00:26:00,659 --> 00:26:05,539 so we were installing medium 13 so this

557 00:26:03,179 --> 00:26:05,539 is the one

558 00:26:06,179 --> 00:26:11,460 okay

559 00:26:08,400 --> 00:26:14,159 add the League field to a filter date to

560 00:26:11,460 --> 00:26:16,440 filter areas of the pivot table filter

561 00:26:14,159 --> 00:26:18,779 the table so that only theme in the

562 00:26:16,440 --> 00:26:21,860 Years League are displayed

563 00:26:18,779 --> 00:26:25,679 so Fields leads list

564 00:26:21,860 --> 00:26:28,760 and then it was saying in League field

565 00:26:25,679 --> 00:26:28,760 if I didn't forget

566 00:26:30,360 --> 00:26:35,299 so add the leak video add the leak field

567 00:26:35,419 --> 00:26:39,900 through the filters area

568 00:26:38,580 --> 00:26:42,900 oops

569 00:26:39,900 --> 00:26:45,900 not here to the filters area

570 00:26:42,900 --> 00:26:48,600 okay filter the table so that only theme

571 00:26:45,900 --> 00:26:50,940 in the youth league and split

572 00:26:48,600 --> 00:26:54,179 so cross this

573 00:26:50,940 --> 00:26:56,580 now not all but only the youth

574 00:26:54,179 --> 00:26:58,860 now okay

575 00:26:56,580 --> 00:27:00,840 so step 19 it is saying continue to

576 00:26:58,860 --> 00:27:03,260 filter the pivot table as follow create

577 00:27:00,840 --> 00:27:06,740 a slicer based on the level field value

578 00:27:03,260 --> 00:27:11,700 so for that go to insert

579 00:27:06,740 --> 00:27:13,980 and then filters slicer so if we select

580 00:27:11,700 --> 00:27:16,380 cell outside the Vivo table then we

581 00:27:13,980 --> 00:27:17,880 should it will select it will show like

582 00:27:16,380 --> 00:27:20,039 this

583 00:27:17,880 --> 00:27:22,440 so it is saying continue to filter the

584 00:27:20,039 --> 00:27:24,840 pivot table so for that you should stay

585 00:27:22,440 --> 00:27:28,200 inside the pivot table and then

586 00:27:24,840 --> 00:27:29,520 click filter slicer right so so like

587 00:27:28,200 --> 00:27:32,760 this

588 00:27:29,520 --> 00:27:36,059 so it is saying level field value hit

589 00:27:32,760 --> 00:27:38,179 enter and it is saying hi to 2.3 and

590 00:27:36,059 --> 00:27:41,039 then width of 1.5

591 00:27:38,179 --> 00:27:45,240 so 2.3

592 00:27:41,039 --> 00:27:49,320 and then width of 1.5 and then it is

593 00:27:45,240 --> 00:27:52,320 saying F3 through G14 so move

594 00:27:49,320 --> 00:27:52,320 er3

595 00:27:53,100 --> 00:27:59,580 so right here up to G 14.

596 00:27:58,080 --> 00:28:03,120 okay

597 00:27:59,580 --> 00:28:06,179 so is this LG 14 okay now it is saying

598 00:28:03,120 --> 00:28:07,860 so this is done and then use the slicer

599 00:28:06,179 --> 00:28:13,799 to filter the viewer table so that only

600 00:28:07,860 --> 00:28:13,799 level 4 team screw bar uh visible

601 00:28:14,159 --> 00:28:19,380 so click here

602 00:28:16,500 --> 00:28:21,419 and then it is saying final step return

603 00:28:19,380 --> 00:28:23,880 you also want to summarize and compute

604 00:28:21,419 --> 00:28:27,419 the performance of the same same themes

605 00:28:23,880 --> 00:28:31,260 by label switch to the same themes level

606 00:28:27,419 --> 00:28:34,200 same level pivot table so here inside of

607 00:28:31,260 --> 00:28:36,960 your chart using using the stack

608 00:28:34,200 --> 00:28:38,580 column chart type and format is that

609 00:28:36,960 --> 00:28:41,640 follows so

610 00:28:38,580 --> 00:28:43,919 click here and then go to pivot table

611 00:28:41,640 --> 00:28:46,080 analyze and Design

612 00:28:43,919 --> 00:28:48,779 oops maybe analyze

613 00:28:46,080 --> 00:28:51,779 so pivot pivot chart

614 00:28:48,779 --> 00:28:55,080 so go to insert let's see oh here is the

615 00:28:51,779 --> 00:28:56,940 website so we will chat and then it was

616 00:28:55,080 --> 00:29:01,159 saying stacked

617 00:28:56,940 --> 00:29:06,260 extract plus third column

618 00:29:01,159 --> 00:29:06,260 all sort so stacked column

619 00:29:07,279 --> 00:29:12,360 so this is the one

620 00:29:09,779 --> 00:29:14,760 now hit enter so let's confirm it was

621 00:29:12,360 --> 00:29:19,020 saying a stacked column and then resize

622 00:29:14,760 --> 00:29:22,020 and reposition within E2 through l22

623 00:29:19,020 --> 00:29:26,100 uh so just

624 00:29:22,020 --> 00:29:29,960 drag it up to E2

625 00:29:26,100 --> 00:29:29,960 and then L 22

626 00:29:32,000 --> 00:29:39,380 so up to here okay add this and title

627 00:29:36,360 --> 00:29:39,380 wins by level

628 00:29:39,620 --> 00:29:43,200 so here it does not seem to have that

629 00:29:42,179 --> 00:29:46,860 title

630 00:29:43,200 --> 00:29:49,340 so let's add it oops and

631 00:29:46,860 --> 00:29:52,580 and then

632 00:29:49,340 --> 00:29:58,980 wins wins by

633 00:29:52,580 --> 00:30:01,260 level oops level okay 200 using uh the

634 00:29:58,980 --> 00:30:04,500 above chart option so filter the pivot

635 00:30:01,260 --> 00:30:06,020 chart so that only wins in level 4 5 and

636 00:30:04,500 --> 00:30:07,740 6 are displayed

637 00:30:06,020 --> 00:30:09,720 so

638 00:30:07,740 --> 00:30:13,380 level

639 00:30:09,720 --> 00:30:17,279 so not select all only

640 00:30:13,380 --> 00:30:18,840 four five and six are displayed now hit

641 00:30:17,279 --> 00:30:22,700 OK

642 00:30:18,840 --> 00:30:22,700 so this will adjust the chart