Module 11: End of Module Project 1

1 00:00:02,340 --> 00:00:07,859 all right so this is module 11 in the

2 00:00:04,920 --> 00:00:10,740 module project 1 and in step one it is

3 00:00:07,859 --> 00:00:13,139 saying Nadia ivanok is a partner at

4 00:00:10,740 --> 00:00:14,700 quality Consulting a Consulting frame so

5 00:00:13,139 --> 00:00:16,740 you can escape this information let's

6 00:00:14,700 --> 00:00:18,960 see so it is saying here go to the sales

7 00:00:16,740 --> 00:00:21,300 report worksheet so on the bottom click

8 00:00:18,960 --> 00:00:23,160 on sales reboot which contains a table

9 00:00:21,300 --> 00:00:26,340 named sales listing details about

10 00:00:23,160 --> 00:00:30,359 Consulting project completed in 2024 in

11 00:00:26,340 --> 00:00:34,340 the range M3 through PA so let's see M3

12 00:00:30,359 --> 00:00:37,260 through p8 here it is saying

13 00:00:34,340 --> 00:00:39,660 wants to summarize project information

14 00:00:37,260 --> 00:00:42,480 start by calculating the Numbers number

15 00:00:39,660 --> 00:00:45,480 projects for clients in each business

16 00:00:42,480 --> 00:00:49,020 category in sale N4 inter formula using

17 00:00:45,480 --> 00:00:52,860 the county function okay so and for

18 00:00:49,020 --> 00:00:56,520 count e function so count if function is

19 00:00:52,860 --> 00:00:58,739 literally count if this range meets this

20 00:00:56,520 --> 00:01:01,260 criteria so it takes range and criteria

21 00:00:58,739 --> 00:01:03,359 now is the value in this range meets

22 00:01:01,260 --> 00:01:05,100 this criteria and then record that value

23 00:01:03,359 --> 00:01:06,780 and then again take another value and

24 00:01:05,100 --> 00:01:09,180 then if another value meets that

25 00:01:06,780 --> 00:01:12,000 criteria regard that value and at the

26 00:01:09,180 --> 00:01:13,680 end it will count those total value and

27 00:01:12,000 --> 00:01:17,100 then gives us the result

28 00:01:13,680 --> 00:01:19,200 okay so let's see what is the range

29 00:01:17,100 --> 00:01:21,299 that counts the number of projects for

30 00:01:19,200 --> 00:01:24,180 clients in the banking category

31 00:01:21,299 --> 00:01:27,180 okay checking that category column

32 00:01:24,180 --> 00:01:30,420 in the sales table so category column is

33 00:01:27,180 --> 00:01:31,140 the range so let's go to sales table and

34 00:01:30,420 --> 00:01:34,140 then

35 00:01:31,140 --> 00:01:37,799 category column

36 00:01:34,140 --> 00:01:40,380 okay and then criteria will be banking

37 00:01:37,799 --> 00:01:42,720 category is equals to the value in sale

38 00:01:40,380 --> 00:01:45,299 M4 so M4 is banking

39 00:01:42,720 --> 00:01:48,420 so this will be the criteria now close

40 00:01:45,299 --> 00:01:50,520 parenthesis and then in the so there are

41 00:01:48,420 --> 00:01:52,500 six projects that are request to banking

42 00:01:50,520 --> 00:01:56,460 category

43 00:01:52,500 --> 00:01:58,320 okay now step two in column o Nadia

44 00:01:56,460 --> 00:02:01,579 wants to calculate the total sales for

45 00:01:58,320 --> 00:02:04,920 each category in cell o4 okay

46 00:02:01,579 --> 00:02:07,500 formula using the sum IF function that

47 00:02:04,920 --> 00:02:10,319 totals the sale for banking purposes

48 00:02:07,500 --> 00:02:12,300 banking projects checking that category

49 00:02:10,319 --> 00:02:14,580 column in the sales table is equals to

50 00:02:12,300 --> 00:02:16,980 the value in cell M4 so same as

51 00:02:14,580 --> 00:02:20,220 previously step and then the formula

52 00:02:16,980 --> 00:02:22,860 totals the sale for all four quarters so

53 00:02:20,220 --> 00:02:25,500 in this case it will need some value to

54 00:02:22,860 --> 00:02:27,420 calculate the sum so if we don't provide

55 00:02:25,500 --> 00:02:31,379 the third arguments I will explain you

56 00:02:27,420 --> 00:02:33,480 so let's see some IF function so in case

57 00:02:31,379 --> 00:02:35,819 of sum IF function it will take three

58 00:02:33,480 --> 00:02:38,640 arguments so the third argument is

59 00:02:35,819 --> 00:02:41,420 optional so the range will be same thing

60 00:02:38,640 --> 00:02:44,879 category column

61 00:02:41,420 --> 00:02:48,180 so sales and then

62 00:02:44,879 --> 00:02:50,340 category column and in criteria will be

63 00:02:48,180 --> 00:02:52,500 also same thing that the values will be

64 00:02:50,340 --> 00:02:54,840 equals to banking

65 00:02:52,500 --> 00:02:57,959 so we have four

66 00:02:54,840 --> 00:03:01,319 now this is optional so if we just enter

67 00:02:57,959 --> 00:03:04,739 Then it must give zero let's see see if

68 00:03:01,319 --> 00:03:07,920 you look it gives 0 because

69 00:03:04,739 --> 00:03:10,440 sales category so here is the column see

70 00:03:07,920 --> 00:03:13,500 if any value is equals to banking then

71 00:03:10,440 --> 00:03:16,200 it has no reference what to uh what to

72 00:03:13,500 --> 00:03:18,120 count as the song so here is no any

73 00:03:16,200 --> 00:03:20,400 number value these are thus the text

74 00:03:18,120 --> 00:03:23,459 value so it has no any reference so it

75 00:03:20,400 --> 00:03:27,060 will just totals to zero but if we give

76 00:03:23,459 --> 00:03:28,739 this third third argument some range so

77 00:03:27,060 --> 00:03:30,120 let's see what is the range range is

78 00:03:28,739 --> 00:03:33,420 total sales

79 00:03:30,120 --> 00:03:37,980 so total sales column

80 00:03:33,420 --> 00:03:40,860 total sales in this now it will check

81 00:03:37,980 --> 00:03:42,959 banking banking value in this column so

82 00:03:40,860 --> 00:03:45,120 for instance let's see so this is the

83 00:03:42,959 --> 00:03:47,819 banking banking value now it will go to

84 00:03:45,120 --> 00:03:49,739 this column and then take this value so

85 00:03:47,819 --> 00:03:52,560 take this value and then again it will

86 00:03:49,739 --> 00:03:54,420 Source other value so it will search

87 00:03:52,560 --> 00:03:56,519 this value and then again it will go

88 00:03:54,420 --> 00:03:58,680 here and then take this value so like

89 00:03:56,519 --> 00:04:01,620 this it will take this value take this

90 00:03:58,680 --> 00:04:04,860 value and then calculates the sum

91 00:04:01,620 --> 00:04:08,640 now let's hit enter so this is the total

92 00:04:04,860 --> 00:04:10,860 sum okay so if you are still confused

93 00:04:08,640 --> 00:04:12,480 you can post a comment and then I'll

94 00:04:10,860 --> 00:04:15,959 make sure to

95 00:04:12,480 --> 00:04:17,639 reply it okay step three in column P

96 00:04:15,959 --> 00:04:22,440 Nadia wants to calculate the average

97 00:04:17,639 --> 00:04:25,259 sales for each category in cell P4 okay

98 00:04:22,440 --> 00:04:28,639 using the average if so again this is

99 00:04:25,259 --> 00:04:31,560 the same average

100 00:04:28,639 --> 00:04:33,240 so range will be let's see

101 00:04:31,560 --> 00:04:36,720 checking the category so same thing

102 00:04:33,240 --> 00:04:38,520 range will be category column

103 00:04:36,720 --> 00:04:42,120 so category

104 00:04:38,520 --> 00:04:44,520 and then criteria will be M4

105 00:04:42,120 --> 00:04:46,919 and then to for average also it needs

106 00:04:44,520 --> 00:04:49,919 some number value to calculate the

107 00:04:46,919 --> 00:04:51,240 average so for those number value it's

108 00:04:49,919 --> 00:04:54,120 same

109 00:04:51,240 --> 00:04:57,660 and total sales call

110 00:04:54,120 --> 00:05:00,900 of sales table so total sales

111 00:04:57,660 --> 00:05:03,720 double click and then close this and

112 00:05:00,900 --> 00:05:06,479 then close parenthesis okay

113 00:05:03,720 --> 00:05:08,220 so step four it is saying Nadia wants to

114 00:05:06,479 --> 00:05:11,400 identify the number of projects that

115 00:05:08,220 --> 00:05:15,120 have total sales more than 10 000 and

116 00:05:11,400 --> 00:05:16,800 those for client Kira okay in sale and

117 00:05:15,120 --> 00:05:17,820 12

118 00:05:16,800 --> 00:05:20,699 .

119 00:05:17,820 --> 00:05:23,160 let's see did you miss something

120 00:05:20,699 --> 00:05:24,960 so it is saying okay equals to the value

121 00:05:23,160 --> 00:05:27,720 and then it is saying fill the range and

122 00:05:24,960 --> 00:05:29,340 5 through N8 with the formula in cell

123 00:05:27,720 --> 00:05:31,919 and four

124 00:05:29,340 --> 00:05:34,380 okay and five

125 00:05:31,919 --> 00:05:36,720 through n a

126 00:05:34,380 --> 00:05:37,740 and then for all it was saying feel I

127 00:05:36,720 --> 00:05:39,120 missed that

128 00:05:37,740 --> 00:05:42,539 okay

129 00:05:39,120 --> 00:05:46,199 you can go to corner and then drag it

130 00:05:42,539 --> 00:05:48,060 all right so step four it is saying in

131 00:05:46,199 --> 00:05:49,800 n12

132 00:05:48,060 --> 00:05:53,580 okay here is n12

133 00:05:49,800 --> 00:05:57,060 it is saying D count function okay

134 00:05:53,580 --> 00:05:59,039 so D count so this counts the cell

135 00:05:57,060 --> 00:06:01,199 containing number in the field of

136 00:05:59,039 --> 00:06:04,680 Records in the database that match the

137 00:06:01,199 --> 00:06:06,900 condition you specify okay

138 00:06:04,680 --> 00:06:10,860 so we need to give database and then

139 00:06:06,900 --> 00:06:13,560 fill the and then criteria let's see

140 00:06:10,860 --> 00:06:15,600 so to count the number of project with

141 00:06:13,560 --> 00:06:19,080 the total sales of more than 10 000

142 00:06:15,600 --> 00:06:22,500 using the data in entire sales table so

143 00:06:19,080 --> 00:06:27,199 this entire sale will be our database

144 00:06:22,500 --> 00:06:30,539 oops so sales and then hashtag

145 00:06:27,199 --> 00:06:31,560 also entire sale of this database of

146 00:06:30,539 --> 00:06:33,780 this table

147 00:06:31,560 --> 00:06:36,900 okay and then field will be counting the

148 00:06:33,780 --> 00:06:40,020 value in the total sales column so for

149 00:06:36,900 --> 00:06:43,860 field its total sales column so the

150 00:06:40,020 --> 00:06:47,520 Syntax for it is quotation and then

151 00:06:43,860 --> 00:06:50,220 total sales so the Syntax for field in

152 00:06:47,520 --> 00:06:52,080 this case is we need to wrap the name of

153 00:06:50,220 --> 00:06:54,180 the column in quotation mark So if you

154 00:06:52,080 --> 00:06:57,180 Google D count

155 00:06:54,180 --> 00:07:00,419 so which I did earlier so if you Google

156 00:06:57,180 --> 00:07:01,919 the count this case it is Bing okay and

157 00:07:00,419 --> 00:07:04,259 then it will give the information so

158 00:07:01,919 --> 00:07:06,539 decount takes database field criteria

159 00:07:04,259 --> 00:07:08,699 which are shown in Excel so the example

160 00:07:06,539 --> 00:07:11,280 will be so this will be the range so

161 00:07:08,699 --> 00:07:13,620 this is our database and then field so

162 00:07:11,280 --> 00:07:15,180 as is the name of the column and then it

163 00:07:13,620 --> 00:07:18,360 react the name of the column in

164 00:07:15,180 --> 00:07:20,880 quotation and then this is the criteria

165 00:07:18,360 --> 00:07:23,479 so same thing it is explained here

166 00:07:20,880 --> 00:07:23,479 okay

167 00:07:23,940 --> 00:07:29,580 so total sales and then let's see the

168 00:07:26,940 --> 00:07:31,560 criteria will be so encounting the value

169 00:07:29,580 --> 00:07:34,080 in the sales column and that are equals

170 00:07:31,560 --> 00:07:38,580 to the value in the range M10 through

171 00:07:34,080 --> 00:07:40,800 M11 so this is our criteria so I am 10

172 00:07:38,580 --> 00:07:43,259 through and 11

173 00:07:40,800 --> 00:07:45,539 and then close parenthesis and then hit

174 00:07:43,259 --> 00:07:48,060 enter okay now it's day five it is

175 00:07:45,539 --> 00:07:51,000 saying in cell and six create a formula

176 00:07:48,060 --> 00:07:54,240 using D count a function to count the

177 00:07:51,000 --> 00:07:57,180 number of project okay so here and 16

178 00:07:54,240 --> 00:07:59,160 now it is saying the count oops which

179 00:07:57,180 --> 00:08:01,860 will start with the cost to sound sign

180 00:07:59,160 --> 00:08:04,740 the count and then a so the difference

181 00:08:01,860 --> 00:08:08,160 is it it counts in only the non blank

182 00:08:04,740 --> 00:08:10,380 zip so it will escape the blank sales so

183 00:08:08,160 --> 00:08:13,979 that's it the different so different so

184 00:08:10,380 --> 00:08:15,900 database field criteria okay so let's

185 00:08:13,979 --> 00:08:19,440 see what is the database it's same thing

186 00:08:15,900 --> 00:08:21,120 so entire sales entire sales of sales

187 00:08:19,440 --> 00:08:26,160 table

188 00:08:21,120 --> 00:08:28,139 so on okay and then field is

189 00:08:26,160 --> 00:08:30,419 using the data in industrial and

190 00:08:28,139 --> 00:08:32,719 Counting the value in client ID column

191 00:08:30,419 --> 00:08:37,860 so this is the field

192 00:08:32,719 --> 00:08:39,659 so graph it quotation client ID that are

193 00:08:37,860 --> 00:08:42,360 equals to

194 00:08:39,659 --> 00:08:45,180 young 14

195 00:08:42,360 --> 00:08:48,720 through and 15 and then close

196 00:08:45,180 --> 00:08:50,820 parenthesis enter okay so step six it is

197 00:08:48,720 --> 00:08:53,160 saying Nadia also needs to calculate the

198 00:08:50,820 --> 00:08:55,800 total sale for marketing plans and the

199 00:08:53,160 --> 00:08:58,440 average sales for clients in New Jersey

200 00:08:55,800 --> 00:09:01,140 in sales and money create a formula

201 00:08:58,440 --> 00:09:02,519 using the D song function to calculate

202 00:09:01,140 --> 00:09:07,860 the okay

203 00:09:02,519 --> 00:09:10,800 so and 20 and 20 D sum

204 00:09:07,860 --> 00:09:13,019 so T sum it adds so you can read the

205 00:09:10,800 --> 00:09:15,480 description here as the number in the

206 00:09:13,019 --> 00:09:17,160 field okay column of Records in the

207 00:09:15,480 --> 00:09:18,300 database that match the condition you

208 00:09:17,160 --> 00:09:21,600 specify

209 00:09:18,300 --> 00:09:23,839 okay so data database will be the same

210 00:09:21,600 --> 00:09:26,880 sales all

211 00:09:23,839 --> 00:09:30,300 and then the field will be

212 00:09:26,880 --> 00:09:33,120 okay so field will be total sales

213 00:09:30,300 --> 00:09:36,860 total sales column

214 00:09:33,120 --> 00:09:41,519 total sales and then criteria will be

215 00:09:36,860 --> 00:09:43,440 M22 through M23 and then close

216 00:09:41,519 --> 00:09:46,080 parenthesis enter

217 00:09:43,440 --> 00:09:48,240 okay so step 8 it is saying go to the

218 00:09:46,080 --> 00:09:52,560 sales by category worksheet

219 00:09:48,240 --> 00:09:56,040 let's see did with the to this N24

220 00:09:52,560 --> 00:09:56,760 so N24 we missed this

221 00:09:56,040 --> 00:09:58,680 [Music]

222 00:09:56,760 --> 00:10:01,320 um

223 00:09:58,680 --> 00:10:03,480 so here is this sum

224 00:10:01,320 --> 00:10:07,920 any step six it was the sum and then

225 00:10:03,480 --> 00:10:12,120 brutal sales and then range was M18

226 00:10:07,920 --> 00:10:16,500 I am 18 through m19

227 00:10:12,120 --> 00:10:18,600 okay and then here in N24 it is average

228 00:10:16,500 --> 00:10:21,839 so d

229 00:10:18,600 --> 00:10:24,540 the average and the database will be

230 00:10:21,839 --> 00:10:27,959 same all sales

231 00:10:24,540 --> 00:10:31,620 okay and then field will be

232 00:10:27,959 --> 00:10:35,640 let's see our totals is total sales

233 00:10:31,620 --> 00:10:38,640 and then criteria will be M22 through

234 00:10:35,640 --> 00:10:38,640 M23

235 00:10:43,220 --> 00:10:48,019 worksheet so on bottom here oops

236 00:10:48,480 --> 00:10:54,200 it is sales

237 00:10:51,200 --> 00:10:55,820 report yes

238 00:10:54,200 --> 00:10:59,579 created

239 00:10:55,820 --> 00:11:00,899 table named category pivot okay so this

240 00:10:59,579 --> 00:11:03,120 is the pivot table

241 00:11:00,899 --> 00:11:04,980 yeah this is the pivot table here the

242 00:11:03,120 --> 00:11:06,240 way to identify is this if you click on

243 00:11:04,980 --> 00:11:09,899 this cell then

244 00:11:06,240 --> 00:11:11,940 here okay let me expand this so here

245 00:11:09,899 --> 00:11:12,740 nothing is appearing but if you click

246 00:11:11,940 --> 00:11:15,200 here

247 00:11:12,740 --> 00:11:18,420 oops

248 00:11:15,200 --> 00:11:20,339 but if you click here in the pivot table

249 00:11:18,420 --> 00:11:21,920 then pivot table analyze and the design

250 00:11:20,339 --> 00:11:23,519 tab will appear

251 00:11:21,920 --> 00:11:26,100 okay

252 00:11:23,519 --> 00:11:27,360 so step a reducing the data would be

253 00:11:26,100 --> 00:11:30,660 easier

254 00:11:27,360 --> 00:11:32,880 to interpret if the dates appear as what

255 00:11:30,660 --> 00:11:34,860 a normal group The Standard field value

256 00:11:32,880 --> 00:11:37,019 by quarters only

257 00:11:34,860 --> 00:11:39,120 okay so change the report layout so

258 00:11:37,019 --> 00:11:41,820 after that we'll take care of that so

259 00:11:39,120 --> 00:11:44,940 firstly we need to do group the start

260 00:11:41,820 --> 00:11:47,519 date fields by quarters only so started

261 00:11:44,940 --> 00:11:49,740 field click on here and then right click

262 00:11:47,519 --> 00:11:52,680 and then there must be group a year

263 00:11:49,740 --> 00:11:55,200 group and then it is saying Group by

264 00:11:52,680 --> 00:11:57,480 quarters only So currently it's month

265 00:11:55,200 --> 00:12:00,060 create click quarters the select month

266 00:11:57,480 --> 00:12:02,040 and then okay

267 00:12:00,060 --> 00:12:04,260 so after that this thing change the

268 00:12:02,040 --> 00:12:07,980 report layout to compact form to make

269 00:12:04,260 --> 00:12:10,560 the pivot table look less cluttered okay

270 00:12:07,980 --> 00:12:12,120 so for that good pivot table analyze

271 00:12:10,560 --> 00:12:15,839 let's see

272 00:12:12,120 --> 00:12:17,459 okay maybe it's in design yeah here is

273 00:12:15,839 --> 00:12:20,399 the layout and it was saying compact

274 00:12:17,459 --> 00:12:22,920 form okay

275 00:12:20,399 --> 00:12:25,260 so step 8 is done now step nine it is

276 00:12:22,920 --> 00:12:28,680 saying go to the sales flight sales by

277 00:12:25,260 --> 00:12:30,480 client worksheet here it is Nadia

278 00:12:28,680 --> 00:12:33,060 created a paper table named client

279 00:12:30,480 --> 00:12:35,459 people that list sales by client and

280 00:12:33,060 --> 00:12:37,440 state okay once you want to simplify the

281 00:12:35,459 --> 00:12:40,440 revertible by displaying the sales by

282 00:12:37,440 --> 00:12:44,339 client and reason manually group CT and

283 00:12:40,440 --> 00:12:49,160 z and NY column cell B3 E3 and GAP three

284 00:12:44,339 --> 00:12:53,459 okay so B3 oops B3

285 00:12:49,160 --> 00:12:56,339 E3 and then F3 select all these

286 00:12:53,459 --> 00:12:58,740 and then it is saying manually group so

287 00:12:56,339 --> 00:13:01,800 right click and then again group

288 00:12:58,740 --> 00:13:04,320 and then it is saying use North as the

289 00:13:01,800 --> 00:13:05,820 name of the group okay so it's group one

290 00:13:04,320 --> 00:13:08,600 we want

291 00:13:05,820 --> 00:13:08,600 North

292 00:13:09,420 --> 00:13:16,260 and then it is saying manually group GA

293 00:13:12,480 --> 00:13:19,680 and F L column level in Rover okay so G

294 00:13:16,260 --> 00:13:22,800 A and F L here is GA and then here is f

295 00:13:19,680 --> 00:13:25,800 l and then right click group

296 00:13:22,800 --> 00:13:28,980 okay and then it is saying South as the

297 00:13:25,800 --> 00:13:33,120 name So currently it's group two

298 00:13:28,980 --> 00:13:35,760 so this will be South okay

299 00:13:33,120 --> 00:13:38,040 yes now it is saying remove the State

300 00:13:35,760 --> 00:13:40,500 field from the column area

301 00:13:38,040 --> 00:13:43,560 okay and then rename the state to field

302 00:13:40,500 --> 00:13:46,079 to use the reason okay so let's take

303 00:13:43,560 --> 00:13:48,899 care of this is there first remove the

304 00:13:46,079 --> 00:13:50,579 State field from The Columns area Okay

305 00:13:48,899 --> 00:13:54,120 expand this oops

306 00:13:50,579 --> 00:13:56,339 and then click here double click

307 00:13:54,120 --> 00:13:59,220 Okay click here and then we would

308 00:13:56,339 --> 00:14:01,800 analyze and then fill list and then this

309 00:13:59,220 --> 00:14:04,320 this section will appear and then it was

310 00:14:01,800 --> 00:14:05,339 saying delete State field from column

311 00:14:04,320 --> 00:14:07,700 stable

312 00:14:05,339 --> 00:14:10,860 it was a state or state two

313 00:14:07,700 --> 00:14:13,380 yeah it was saying the State field okay

314 00:14:10,860 --> 00:14:16,320 so here's the state and then remove

315 00:14:13,380 --> 00:14:18,899 field and that is saying rename state to

316 00:14:16,320 --> 00:14:21,660 to use reason Okay click on this drop

317 00:14:18,899 --> 00:14:27,060 down and then field setting

318 00:14:21,660 --> 00:14:29,279 and then it will get reason now hit OK

319 00:14:27,060 --> 00:14:33,060 so after that is saying sort the South

320 00:14:29,279 --> 00:14:35,880 value in ascending order to least cross

321 00:14:33,060 --> 00:14:38,040 the north sales amount in column B

322 00:14:35,880 --> 00:14:39,300 followed by South Sale amount in column

323 00:14:38,040 --> 00:14:42,600 C

324 00:14:39,300 --> 00:14:46,079 so cancel this section now it is saying

325 00:14:42,600 --> 00:14:48,480 sort this value of South

326 00:14:46,079 --> 00:14:52,320 in ascending order okay

327 00:14:48,480 --> 00:14:54,360 so for that expand this go to home and

328 00:14:52,320 --> 00:14:59,060 then here is sort and filter

329 00:14:54,360 --> 00:15:02,100 and then sort smallest to largest okay

330 00:14:59,060 --> 00:15:04,680 let's see in the interesting area B

331 00:15:02,100 --> 00:15:08,940 followed by South in column C okay

332 00:15:04,680 --> 00:15:11,100 yes now hide the field header to further

333 00:15:08,940 --> 00:15:14,279 streamline the layout okay so I'm going

334 00:15:11,100 --> 00:15:16,680 to add this field header so for that go

335 00:15:14,279 --> 00:15:19,320 to pivot table analyze and then here is

336 00:15:16,680 --> 00:15:22,019 field field header selected now click

337 00:15:19,320 --> 00:15:24,320 here to deselect okay

338 00:15:22,019 --> 00:15:24,320 foreign

339 00:15:24,860 --> 00:15:33,199 go to the sales by service worksheet

340 00:15:27,959 --> 00:15:33,199 oops so sales by service

341 00:15:33,420 --> 00:15:36,779 so if you if you don't understand

342 00:15:34,980 --> 00:15:39,060 anything in this

343 00:15:36,779 --> 00:15:40,500 video then may feel free to comment down

344 00:15:39,060 --> 00:15:43,440 I will

345 00:15:40,500 --> 00:15:45,600 I'll try my best to make you understand

346 00:15:43,440 --> 00:15:47,639 okay step 10 it is saying go to the

347 00:15:45,600 --> 00:15:49,980 sales by service or six so we are on the

348 00:15:47,639 --> 00:15:52,139 same worksheet which contains okay that

349 00:15:49,980 --> 00:15:54,779 compare and annual sale for providing

350 00:15:52,139 --> 00:15:56,760 Nadia wants to know the difference and

351 00:15:54,779 --> 00:15:59,639 the percentage of difference between the

352 00:15:56,760 --> 00:16:01,620 quarter four and quarter one sales and

353 00:15:59,639 --> 00:16:04,019 display the average total sale for each

354 00:16:01,620 --> 00:16:07,279 service okay so it is saying insert a

355 00:16:04,019 --> 00:16:10,680 calculated field name difference

356 00:16:07,279 --> 00:16:13,740 okay that subtracts quarter one field

357 00:16:10,680 --> 00:16:16,339 amount from quarter four field amount

358 00:16:13,740 --> 00:16:19,440 okay so it is saying insult of

359 00:16:16,339 --> 00:16:21,420 calculated field name different

360 00:16:19,440 --> 00:16:23,579 now we need to insert one more column

361 00:16:21,420 --> 00:16:26,220 here so normally what we would do is

362 00:16:23,579 --> 00:16:29,459 right click and then insert but this is

363 00:16:26,220 --> 00:16:31,980 pivot table so we can do this way

364 00:16:29,459 --> 00:16:34,320 we can do it there by that way so for

365 00:16:31,980 --> 00:16:37,920 that click on debut table and then go to

366 00:16:34,320 --> 00:16:39,839 pivot table analyze Tab and then fails

367 00:16:37,920 --> 00:16:42,180 item and set stuff down and then

368 00:16:39,839 --> 00:16:44,399 calculator field

369 00:16:42,180 --> 00:16:46,980 okay let's see other instruction

370 00:16:44,399 --> 00:16:50,160 so it is saying

371 00:16:46,980 --> 00:16:52,199 display okay insert this name different

372 00:16:50,160 --> 00:16:55,220 so name will be different

373 00:16:52,199 --> 00:16:57,839 again repeat that

374 00:16:55,220 --> 00:17:01,740 calculation and then here in calculated

375 00:16:57,839 --> 00:17:04,319 field so name will be difference

376 00:17:01,740 --> 00:17:06,419 okay that subtracts the quarter one

377 00:17:04,319 --> 00:17:07,559 field amount from quarter four field

378 00:17:06,419 --> 00:17:11,400 amount

379 00:17:07,559 --> 00:17:13,380 okay in Formula so quarter quarter one

380 00:17:11,400 --> 00:17:15,500 field amount so for that we can do like

381 00:17:13,380 --> 00:17:19,020 this quarter one

382 00:17:15,500 --> 00:17:20,939 so where to manually type so here is the

383 00:17:19,020 --> 00:17:25,559 quarter one so we need to enter exactly

384 00:17:20,939 --> 00:17:26,819 this this text so Q TR and then one it's

385 00:17:25,559 --> 00:17:29,400 mentioned here

386 00:17:26,819 --> 00:17:32,280 and then difference

387 00:17:29,400 --> 00:17:34,679 so let's read it again it is saying that

388 00:17:32,280 --> 00:17:36,840 subtracts quarter one field M1 from

389 00:17:34,679 --> 00:17:39,360 quarter four even so for that we're

390 00:17:36,840 --> 00:17:40,380 going to enter quarter four first and

391 00:17:39,360 --> 00:17:43,500 then

392 00:17:40,380 --> 00:17:46,620 Q T are movement

393 00:17:43,500 --> 00:17:49,320 and then okay all right so this is added

394 00:17:46,620 --> 00:17:52,140 and it is saying in cell e to use this

395 00:17:49,320 --> 00:17:54,120 as the column label so we need to rename

396 00:17:52,140 --> 00:17:56,880 that okay

397 00:17:54,120 --> 00:17:58,740 so oops copy it

398 00:17:56,880 --> 00:18:01,140 and then go here

399 00:17:58,740 --> 00:18:04,740 and then paste it

400 00:18:01,140 --> 00:18:07,260 so delete this and then enter okay

401 00:18:04,740 --> 00:18:09,360 now Step 11 it is saying insert another

402 00:18:07,260 --> 00:18:11,940 calculator Field named parts and

403 00:18:09,360 --> 00:18:14,880 difference okay so the name will be

404 00:18:11,940 --> 00:18:16,980 percent difference that subtracts the

405 00:18:14,880 --> 00:18:19,440 quarter one field amount from the

406 00:18:16,980 --> 00:18:22,440 quarter four and then divides the result

407 00:18:19,440 --> 00:18:24,539 for by quarter one okay

408 00:18:22,440 --> 00:18:27,419 so same thing go to this tab you got

409 00:18:24,539 --> 00:18:30,120 table analyze and the calculation Fields

410 00:18:27,419 --> 00:18:32,340 item at sets and then calculated field

411 00:18:30,120 --> 00:18:35,220 and then in this case the name will be

412 00:18:32,340 --> 00:18:36,720 percent difference and then formula will

413 00:18:35,220 --> 00:18:39,480 be

414 00:18:36,720 --> 00:18:42,179 okay subtract this from quarter Force so

415 00:18:39,480 --> 00:18:44,100 quarter four will be fast so we need to

416 00:18:42,179 --> 00:18:47,280 interpreter 4 as

417 00:18:44,100 --> 00:18:48,539 let's see a q t r it's mentioned here

418 00:18:47,280 --> 00:18:51,980 qtr

419 00:18:48,539 --> 00:18:55,799 4 and then

420 00:18:51,980 --> 00:18:59,900 Oops I did this okay

421 00:18:55,799 --> 00:19:04,740 so this so qtr4

422 00:18:59,900 --> 00:19:07,440 subtract qtr one and then I'll drag it

423 00:19:04,740 --> 00:19:10,860 in parenthesis

424 00:19:07,440 --> 00:19:14,160 and then it is saying divide by

425 00:19:10,860 --> 00:19:18,780 okay let's see divide by

426 00:19:14,160 --> 00:19:21,260 quarter one so qtr and then one and then

427 00:19:18,780 --> 00:19:24,059 hit okay all right

428 00:19:21,260 --> 00:19:27,120 now it is saying change the number

429 00:19:24,059 --> 00:19:28,440 format of these two percentage with two

430 00:19:27,120 --> 00:19:31,500 decimal places

431 00:19:28,440 --> 00:19:34,260 so select all this value

432 00:19:31,500 --> 00:19:37,140 and then go to home

433 00:19:34,260 --> 00:19:38,400 and then here is number and then click

434 00:19:37,140 --> 00:19:42,299 on this

435 00:19:38,400 --> 00:19:45,200 and then percent and they do decimal

436 00:19:42,299 --> 00:19:48,720 places and then hit OK

437 00:19:45,200 --> 00:19:51,380 alrighty so this will be our name of the

438 00:19:48,720 --> 00:19:51,380 column oops

439 00:19:51,600 --> 00:19:55,320 copy this

440 00:19:52,919 --> 00:19:58,200 and then click here

441 00:19:55,320 --> 00:20:00,179 and then paste it

442 00:19:58,200 --> 00:20:03,660 okay

443 00:20:00,179 --> 00:20:05,760 so step 12 it is saying add the total

444 00:20:03,660 --> 00:20:06,960 sales field to the value area of the

445 00:20:05,760 --> 00:20:09,720 pivot table

446 00:20:06,960 --> 00:20:13,020 making it the last field in the value

447 00:20:09,720 --> 00:20:15,600 area Okay use the average calculation to

448 00:20:13,020 --> 00:20:18,419 summarize okay so it is saying add total

449 00:20:15,600 --> 00:20:20,880 sales okay so for that we again

450 00:20:18,419 --> 00:20:24,600 need to maximize and then pivot table

451 00:20:20,880 --> 00:20:26,640 analyze and then fill list and then it

452 00:20:24,600 --> 00:20:31,140 was saying total sales

453 00:20:26,640 --> 00:20:33,080 so add these total sales to Value field

454 00:20:31,140 --> 00:20:36,419 okay

455 00:20:33,080 --> 00:20:38,280 now it is saying value area use the

456 00:20:36,419 --> 00:20:39,419 average calculation to summarize the

457 00:20:38,280 --> 00:20:42,840 total sales

458 00:20:39,419 --> 00:20:45,299 okay so it wants average so click here

459 00:20:42,840 --> 00:20:49,200 fuel value setting and then instead of

460 00:20:45,299 --> 00:20:51,120 song click on address and then okay

461 00:20:49,200 --> 00:20:54,000 now it is saying move the annual field

462 00:20:51,120 --> 00:20:56,039 so that it is listed as the last field

463 00:20:54,000 --> 00:20:59,520 in the value area

464 00:20:56,039 --> 00:21:01,620 okay so it also want and you'll feel

465 00:20:59,520 --> 00:21:04,940 let's see

466 00:21:01,620 --> 00:21:04,940 where is annual field

467 00:21:05,039 --> 00:21:10,380 it must be yeah annual field and then

468 00:21:08,340 --> 00:21:12,840 drag it to the last

469 00:21:10,380 --> 00:21:15,660 okay so finally step it is saying the

470 00:21:12,840 --> 00:21:18,179 idea also want to display details about

471 00:21:15,660 --> 00:21:20,880 the two services with the highest total

472 00:21:18,179 --> 00:21:23,760 sale apply a value filter to the service

473 00:21:20,880 --> 00:21:27,360 field that display the top two item by

474 00:21:23,760 --> 00:21:30,059 annual sales amount okay so value filter

475 00:21:27,360 --> 00:21:33,179 to the service field so here is our

476 00:21:30,059 --> 00:21:36,240 service field let's see so this is not

477 00:21:33,179 --> 00:21:38,640 here so let's see service field and then

478 00:21:36,240 --> 00:21:42,120 click on this drop down and then label

479 00:21:38,640 --> 00:21:43,980 filter value filter its value filter and

480 00:21:42,120 --> 00:21:45,380 then it is saying

481 00:21:43,980 --> 00:21:50,039 okay

482 00:21:45,380 --> 00:21:51,539 so top two items so select top 10 and

483 00:21:50,039 --> 00:21:54,000 then edit it

484 00:21:51,539 --> 00:21:57,600 top two

485 00:21:54,000 --> 00:22:00,419 and then items and then buy annual sales

486 00:21:57,600 --> 00:22:01,980 so here's the annual sale and then click

487 00:22:00,419 --> 00:22:05,220 on total

488 00:22:01,980 --> 00:22:10,159 so this is it let's check our result

489 00:22:05,220 --> 00:22:10,159 okay so sales report worksheet

490 00:22:10,400 --> 00:22:16,260 okay this is good

491 00:22:12,840 --> 00:22:17,760 and then sales by category worksheet

492 00:22:16,260 --> 00:22:20,480 oops

493 00:22:17,760 --> 00:22:20,480 cancel this

494 00:22:20,520 --> 00:22:26,460 okay this is also good and then sales by

495 00:22:24,900 --> 00:22:29,100 client

496 00:22:26,460 --> 00:22:32,039 let's see this is also good and then

497 00:22:29,100 --> 00:22:34,620 sales by service our final one

498 00:22:32,039 --> 00:22:36,480 okay this is also good all right thank

499 00:22:34,620 --> 00:22:38,220 you make sure to subscribe and then if

500 00:22:36,480 --> 00:22:39,960 you don't understand anything please

501 00:22:38,220 --> 00:22:42,380 feel free to comment I will make sure to

502 00:22:39,960 --> 00:22:42,380 reply