Module 3 - End of Module Project 1 #part1
Description
There are the steps that we'll cover in the video"
- Chanelle Larson is a professional assistant with Barrel Barnard Consulting. Chanelle is responsible for collecting consultant hours for some of Barrel Barnard's projects and processing the payments. She wants to automate some tasks and calculate summary information automatically on the worksheet that consultants use to track their hours. Switch to the Timesheet worksheet. In cell C5, insert the NOW function to record the current date.
- Each worksheet covers a period of 20 work days. In cell C4, create a formula using the WORKDAY function to calculate the date 19 days from the start date (cell C3).
- Use AutoFill to complete the labels in the table of hours. Use the values in the range A9:A10 to extend the week numbering to the range A11:A12. Use the value in cell B8 to autofill the remaining weekday abbreviations in the range C8:F8.
- Add formulas to complete the table of hours used. In cell B17, create a nested formula with the IF and SUM functions that check if the total number of hours worked in week 1 (cells B9:F9) is equal to 0. If it is, the cell should display nothing (indicated with two quote marks: ""). Otherwise, the cell should display the total number of hours worked in week 1. Copy the formula from cell B17 to fill the range B18:B20.
- In cell H2, create a formula using the WORKDAY function to calculate the date 5 days after the period end date (cell C4).
#sam #excel #spreadsheet Buy us a coffee: https://www.buymeacoffee.com/taandar ---
Disclaimer: This video is intended for educational purposes only. The purpose of this video is to help students understand how to solve problems in the Cengage business spreadsheet course. However, please note that any attempt to copy or replicate the solutions shown in this video may be considered a violation of academic integrity policies at your college or university. We strongly encourage you to use this video as a guide and to complete the assignments on your own, without resorting to cheating or plagiarism. We are not responsible for any consequences that may result from the use of this video in violation of academic integrity policies.
Transcript
1 00:00:00,000 --> 00:00:05,160 so this is the module 3 project one so
2 00:00:03,240 --> 00:00:07,440 we are covering step one through five in
3 00:00:05,160 --> 00:00:10,880 this video in this step one it is saying
4 00:00:07,440 --> 00:00:13,500 is a professional assistant with you
5 00:00:10,880 --> 00:00:14,460 she want to automate some task and
6 00:00:13,500 --> 00:00:16,440 calculate summary information
7 00:00:14,460 --> 00:00:19,080 automatically on the worksheet that
8 00:00:16,440 --> 00:00:21,420 country that consultant used to track
9 00:00:19,080 --> 00:00:23,400 their hours with so that time sheet
10 00:00:21,420 --> 00:00:24,840 worksheet so we are on the timesheet
11 00:00:23,400 --> 00:00:27,539 worksheet
12 00:00:24,840 --> 00:00:30,439 um in the cell C5 insert Now function to
13 00:00:27,539 --> 00:00:33,480 record the current date so in the state
14 00:00:30,439 --> 00:00:35,040 clc5 it is saying to insert null
15 00:00:33,480 --> 00:00:37,020 function so every time we insert
16 00:00:35,040 --> 00:00:39,540 function or do we or we do any
17 00:00:37,020 --> 00:00:43,379 calculation we should start it with
18 00:00:39,540 --> 00:00:46,440 equal sign and then just now it is case
19 00:00:43,379 --> 00:00:48,539 and then send it so you can the now
20 00:00:46,440 --> 00:00:51,860 small case or Capital case it doesn't
21 00:00:48,539 --> 00:00:55,379 matter so that's it now and then
22 00:00:51,860 --> 00:00:57,360 uh open and close parenthesis and hit
23 00:00:55,379 --> 00:01:00,899 enter and it will fill with the current
24 00:00:57,360 --> 00:01:03,300 date each worksheet cover a period of 20
25 00:01:00,899 --> 00:01:05,339 work days in cell C for create a formula
26 00:01:03,300 --> 00:01:09,479 using a workday function to calculate
27 00:01:05,339 --> 00:01:12,600 the date 19 days from the started say
28 00:01:09,479 --> 00:01:15,240 cell C3 so in the cell C4
29 00:01:12,600 --> 00:01:16,680 so we should enter workday function so
30 00:01:15,240 --> 00:01:19,140 again when we enter the function we
31 00:01:16,680 --> 00:01:23,340 should start with equals to sign and
32 00:01:19,140 --> 00:01:26,400 then work day and then open parenthesis
33 00:01:23,340 --> 00:01:28,860 so it is saying started it is giving us
34 00:01:26,400 --> 00:01:31,080 clue it is saying started so start date
35 00:01:28,860 --> 00:01:33,900 is cell C3
36 00:01:31,080 --> 00:01:36,360 C3 and then it is saying days so we need
37 00:01:33,900 --> 00:01:38,640 19 days
38 00:01:36,360 --> 00:01:40,979 and then hit enter
39 00:01:38,640 --> 00:01:43,680 so step three use autofill to complete
40 00:01:40,979 --> 00:01:46,140 the label in table of outs use the value
41 00:01:43,680 --> 00:01:48,119 in the range a 9 through 18 to extend
42 00:01:46,140 --> 00:01:51,600 the weak numbering to the range a 11
43 00:01:48,119 --> 00:01:54,000 through a12 so here is the a now A9 to
44 00:01:51,600 --> 00:01:56,640 18 so I need this
45 00:01:54,000 --> 00:01:59,159 to extend the value of a live with
46 00:01:56,640 --> 00:02:01,259 rulers so just select this and then go
47 00:01:59,159 --> 00:02:03,899 to the corner and it will give you this
48 00:02:01,259 --> 00:02:07,380 plus sign and then click it and drag it
49 00:02:03,899 --> 00:02:10,200 drag it now it's a lot of fill then it
50 00:02:07,380 --> 00:02:12,000 is saying use the value of cell B8 to
51 00:02:10,200 --> 00:02:15,000 autofill the remaining weekdays
52 00:02:12,000 --> 00:02:17,879 remaining weekdays abbreviation in the
53 00:02:15,000 --> 00:02:20,040 rest here through f8 so we shall use
54 00:02:17,879 --> 00:02:21,599 this and then same procedure just go to
55 00:02:20,040 --> 00:02:24,239 the corner it will give you this plus
56 00:02:21,599 --> 00:02:25,980 sign and then hold it and drag it until
57 00:02:24,239 --> 00:02:29,480 uh
58 00:02:25,980 --> 00:02:29,480 uh yeah Fiat
59 00:02:29,879 --> 00:02:34,920 so it's a lot of it
60 00:02:31,739 --> 00:02:38,340 so step three is down now add formulas
61 00:02:34,920 --> 00:02:39,959 to complete the table of hours used in
62 00:02:38,340 --> 00:02:43,319 cell B17
63 00:02:39,959 --> 00:02:45,300 so this is B17 uh create a listed
64 00:02:43,319 --> 00:02:48,000 formula with if and some function so
65 00:02:45,300 --> 00:02:50,819 here is the interesting part that checks
66 00:02:48,000 --> 00:02:54,120 if the total number of hours worked in
67 00:02:50,819 --> 00:02:55,860 week one sales B7 through B9 is equals
68 00:02:54,120 --> 00:02:58,140 to zero
69 00:02:55,860 --> 00:03:01,019 so we should check if the sum is equals
70 00:02:58,140 --> 00:03:02,819 to zero if the sale and now if it is 0
71 00:03:01,019 --> 00:03:04,200 then we shall display nothing it is
72 00:03:02,819 --> 00:03:06,599 indicated by the quotation mark
73 00:03:04,200 --> 00:03:09,480 otherwise sales will display the total
74 00:03:06,599 --> 00:03:11,340 number of hour worked in week one
75 00:03:09,480 --> 00:03:14,040 um so total number of hours worked in
76 00:03:11,340 --> 00:03:16,080 week one is B9 through B7 sum of B9
77 00:03:14,040 --> 00:03:17,940 through B7 now we should copy this
78 00:03:16,080 --> 00:03:20,400 formula and then after it so let's open
79 00:03:17,940 --> 00:03:22,620 it so when we have to enter this Long
80 00:03:20,400 --> 00:03:26,040 Function I prefer to work in this bar
81 00:03:22,620 --> 00:03:28,440 instead of this because if we type here
82 00:03:26,040 --> 00:03:31,319 uh if or something something like that
83 00:03:28,440 --> 00:03:32,879 and if we hit the left right left Arrow
84 00:03:31,319 --> 00:03:35,400 the right arrow it will automatically
85 00:03:32,879 --> 00:03:38,099 enter the value sometimes so let's test
86 00:03:35,400 --> 00:03:40,680 it it is not doing that now but
87 00:03:38,099 --> 00:03:43,980 sometimes it does that
88 00:03:40,680 --> 00:03:46,680 anyway let's let's do it in this bar it
89 00:03:43,980 --> 00:03:49,379 will be easier so if
90 00:03:46,680 --> 00:03:51,840 so anytime we enter the function we
91 00:03:49,379 --> 00:03:54,000 shall start with equals to sign now if
92 00:03:51,840 --> 00:03:56,280 you know this function has every
93 00:03:54,000 --> 00:03:58,680 function has open parenthesis and close
94 00:03:56,280 --> 00:04:02,040 parenthesis now it takes some parameters
95 00:03:58,680 --> 00:04:03,959 it is logical test so let's do some test
96 00:04:02,040 --> 00:04:08,239 so if some
97 00:04:03,959 --> 00:04:12,120 sum of B9 through EF
98 00:04:08,239 --> 00:04:15,299 F9 equals to 0
99 00:04:12,120 --> 00:04:16,919 and then to Value so value if true so if
100 00:04:15,299 --> 00:04:18,660 if this is true then we should display
101 00:04:16,919 --> 00:04:21,359 nothing which is indicated by quotation
102 00:04:18,660 --> 00:04:23,820 mark so let's just do quotation mark and
103 00:04:21,359 --> 00:04:25,979 then do comma so value if false so if it
104 00:04:23,820 --> 00:04:28,080 is false then visual display total
105 00:04:25,979 --> 00:04:30,680 number of hours worked in week one which
106 00:04:28,080 --> 00:04:30,680 is some
107 00:04:30,780 --> 00:04:37,560 sum of
108 00:04:32,660 --> 00:04:41,520 P9 through F9
109 00:04:37,560 --> 00:04:44,820 and then close its parenthesis
110 00:04:41,520 --> 00:04:47,580 now hit enter
111 00:04:44,820 --> 00:04:50,699 so this autofill this so what do I I was
112 00:04:47,580 --> 00:04:52,860 and look this also convert it into the
113 00:04:50,699 --> 00:04:57,660 capital case so we don't need to worry
114 00:04:52,860 --> 00:05:01,520 about the casing uh let's just cut it
115 00:04:57,660 --> 00:05:01,520 and then paste it here
116 00:05:04,680 --> 00:05:09,960 or this time this time it is working
117 00:05:06,500 --> 00:05:12,060 previously when I used to do left Arrow
118 00:05:09,960 --> 00:05:13,979 writer it is used to automatically enter
119 00:05:12,060 --> 00:05:15,720 the code and if the code was not
120 00:05:13,979 --> 00:05:18,540 complete and it used to give here but
121 00:05:15,720 --> 00:05:21,120 now it seems to be working fine
122 00:05:18,540 --> 00:05:25,500 so that's okay
123 00:05:21,120 --> 00:05:28,100 all right so this is a quite formula
124 00:05:25,500 --> 00:05:28,100 oops
125 00:05:28,979 --> 00:05:32,720 now
126 00:05:31,139 --> 00:05:35,039 um
127 00:05:32,720 --> 00:05:37,620 otherwise if you don't copy the formula
128 00:05:35,039 --> 00:05:42,300 from cell B17 to fill the range B18
129 00:05:37,620 --> 00:05:44,160 through v20 so B17 again it is giving
130 00:05:42,300 --> 00:05:45,539 some error the following refers to the
131 00:05:44,160 --> 00:05:49,740 range that the additional number to
132 00:05:45,539 --> 00:05:51,539 address it just ignore it so go to the
133 00:05:49,740 --> 00:05:55,440 corner it will give this plus sign the
134 00:05:51,539 --> 00:05:59,280 solid and drag it to B20
135 00:05:55,440 --> 00:06:02,160 so it will automatically fill the good
136 00:05:59,280 --> 00:06:07,460 now incl has to create a formula using
137 00:06:02,160 --> 00:06:07,460 workday function so let's go to as to
138 00:06:07,680 --> 00:06:11,580 now every time we enter the function we
139 00:06:09,720 --> 00:06:14,280 actually start with equals to 5 equals
140 00:06:11,580 --> 00:06:16,139 to sign now over today
141 00:06:14,280 --> 00:06:19,199 and then open parenthesis close
142 00:06:16,139 --> 00:06:21,360 frequency parenthesis oh look that way
143 00:06:19,199 --> 00:06:23,759 this is what I was talking about so if
144 00:06:21,360 --> 00:06:24,960 you're doing this this time it's not the
145 00:06:23,759 --> 00:06:27,180 error
146 00:06:24,960 --> 00:06:28,860 so if you do left Arrow right arrow it
147 00:06:27,180 --> 00:06:31,020 will sometimes automatically enter the
148 00:06:28,860 --> 00:06:33,300 code and it will say and give error
149 00:06:31,020 --> 00:06:35,400 because our function is in complete so I
150 00:06:33,300 --> 00:06:39,419 prefer working in this bar
151 00:06:35,400 --> 00:06:40,380 so workday now First Agreement is start
152 00:06:39,419 --> 00:06:45,919 days
153 00:06:40,380 --> 00:06:45,919 so it is saying five days and then
154 00:06:45,960 --> 00:06:49,800 create a formula using a workday
155 00:06:47,580 --> 00:06:53,460 function to calculate the date five days
156 00:06:49,800 --> 00:06:55,979 after the period uh in date
157 00:06:53,460 --> 00:07:01,100 so days is 5 days
158 00:06:55,979 --> 00:07:01,100 five days and then date is C4
159 00:07:01,259 --> 00:07:06,419 so let's hit enter so these are required
160 00:07:04,139 --> 00:07:09,080 sample so we'll continue from the step 6
161 00:07:06,419 --> 00:07:09,080 in next video
