Module 6b - End of Module Project 1 #part2
Description
Questions covered:
8. Naomi and Kaito have more string instruments than any other type. Naomi wants to summarize the string instrument data using subtotals to show the value of each type. Switch to the Strings worksheet and add subtotals as follows:
a. Sort the table by the Instrument field in ascending order.
b. Convert the table to a normal range.
c. Insert subtotals into the range B2:G12, with the subtotals appearing at each change in the Instrument column value. The subtotals should use the SUM function and include subtotals for the Price and Est. Value fields.
9. Switch to the Wind worksheet and remove the duplicate record based on the values in the Instrument, Manufacturer, and Condition columns. The duplicate record has an incorrect Purchase Date of 7/11/2020.
10. The data bars in the last two columns make some of the numbers hard to read and could coordinate better with the formatting of the Wind table.
Change the Data Bars conditional formatting for the range F3:G12 to use the Gradient Fill Orange Data Bar format.
11. Naomi wants to calculate the totals for the instrument collection data. Switch to the All Instruments worksheet, and then create the following formulas:
a. In cell B40, use the COUNTA function with a structured reference to count the values in the Instrument column of the Instruments table.
b. In cell F40, use the SUM function with a structured reference to total the values in the Price column of the Instruments table.
c. In cell G40, use the SUM function with a structured reference to total the values in the Est. Value column of the Instruments table.
12. Naomi and Kaito are preparing to sell some of the instruments, and Naomi wants to include the sales date in the Instruments table
Add a table column to the end of the table.
Support 💖 Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar
#sam #spreadsheet #excel ---
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:07,040 --> 00:00:12,179 switch to the strings worksheet okay so
2 00:00:10,559 --> 00:00:15,540 let's switch
3 00:00:12,179 --> 00:00:17,460 strings worksheet and add subtitles as
4 00:00:15,540 --> 00:00:20,760 follows are the table by the instrument
5 00:00:17,460 --> 00:00:22,980 field in ascending order so
6 00:00:20,760 --> 00:00:26,539 instrument fill click in this drop down
7 00:00:22,980 --> 00:00:29,760 and then select a to z
8 00:00:26,539 --> 00:00:32,399 so this is done convert the table into a
9 00:00:29,760 --> 00:00:34,380 normal range so for that just select the
10 00:00:32,399 --> 00:00:37,260 table
11 00:00:34,380 --> 00:00:41,160 and then table design and then here is
12 00:00:37,260 --> 00:00:42,960 convert to range and then click yes
13 00:00:41,160 --> 00:00:47,120 so after that it is saying insert
14 00:00:42,960 --> 00:00:51,719 subtotals into range B2 through z12 so
15 00:00:47,120 --> 00:00:54,059 B2 through z12 with the subtitles are
16 00:00:51,719 --> 00:00:56,879 bearing on each change in the instrument
17 00:00:54,059 --> 00:00:59,100 column value the subtitles will use the
18 00:00:56,879 --> 00:01:01,800 sum function and include subtitles for
19 00:00:59,100 --> 00:01:04,799 the price and estimated value field so
20 00:01:01,800 --> 00:01:08,760 to include the subtitles in this way
21 00:01:04,799 --> 00:01:10,930 just go to the formula tab and then here
22 00:01:08,760 --> 00:01:11,340 must be somewhere
23 00:01:10,930 --> 00:01:12,479 [Music]
24 00:01:11,340 --> 00:01:15,479 um
25 00:01:12,479 --> 00:01:18,240 but I think data tab okay in data Tab
26 00:01:15,479 --> 00:01:21,000 and here is subtotal option in the
27 00:01:18,240 --> 00:01:22,680 outline field so select estimated value
28 00:01:21,000 --> 00:01:26,700 and price
29 00:01:22,680 --> 00:01:28,619 and then yeah hit OK
30 00:01:26,700 --> 00:01:30,720 so this will add some total to every
31 00:01:28,619 --> 00:01:33,000 fields
32 00:01:30,720 --> 00:01:34,619 yeah it was price and estimated value
33 00:01:33,000 --> 00:01:36,720 okay
34 00:01:34,619 --> 00:01:38,759 now switch to the wind worksheet and
35 00:01:36,720 --> 00:01:41,220 remove the duplicate record based on the
36 00:01:38,759 --> 00:01:44,520 value in instrument manufacturer and
37 00:01:41,220 --> 00:01:47,100 column and condition column so let's go
38 00:01:44,520 --> 00:01:48,540 to the wing and then it is saying
39 00:01:47,100 --> 00:01:51,840 duplicate
40 00:01:48,540 --> 00:01:54,659 so for that also I'll just click initial
41 00:01:51,840 --> 00:01:57,240 inside this inside the table cell
42 00:01:54,659 --> 00:01:58,320 and then it is saying duplicate the
43 00:01:57,240 --> 00:01:59,939 value
44 00:01:58,320 --> 00:02:02,460 in the instrument manufacturing
45 00:01:59,939 --> 00:02:05,520 condition column okay
46 00:02:02,460 --> 00:02:06,780 so for that also go to the data Tab and
47 00:02:05,520 --> 00:02:08,099 then here must be somewhere remote
48 00:02:06,780 --> 00:02:10,560 duplicate
49 00:02:08,099 --> 00:02:12,540 yeah this is remember you can click
50 00:02:10,560 --> 00:02:14,340 click in the search bar and search the
51 00:02:12,540 --> 00:02:16,860 remote duplicate or duplicate and this
52 00:02:14,340 --> 00:02:18,300 will have you that will be easy way if
53 00:02:16,860 --> 00:02:20,099 you can find this
54 00:02:18,300 --> 00:02:24,739 so click here
55 00:02:20,099 --> 00:02:28,980 and then it is saying which field
56 00:02:24,739 --> 00:02:32,160 uh it was instrument manufacturer and
57 00:02:28,980 --> 00:02:37,520 condition columns okay
58 00:02:32,160 --> 00:02:37,520 so data Tab and then remove filter
59 00:02:41,400 --> 00:02:44,660 so this is done
60 00:02:45,060 --> 00:02:49,440 after that it is saying switch to the
61 00:02:47,220 --> 00:02:51,660 winged worksheet and so this was done
62 00:02:49,440 --> 00:02:54,480 and then the data by in the last two
63 00:02:51,660 --> 00:02:56,459 column uh make some of the numbers hard
64 00:02:54,480 --> 00:02:59,220 to read and could coordinate better with
65 00:02:56,459 --> 00:03:02,280 the formatting of interval change the
66 00:02:59,220 --> 00:03:05,700 data where conditional formatting for
67 00:03:02,280 --> 00:03:08,940 the range F3 to g12 so let's say like
68 00:03:05,700 --> 00:03:11,400 the app 3 through z12
69 00:03:08,940 --> 00:03:14,879 to use the gradient fill orange data
70 00:03:11,400 --> 00:03:18,060 power format so for that go to home
71 00:03:14,879 --> 00:03:21,780 and make sure you select G3 through
72 00:03:18,060 --> 00:03:23,580 F3 through through g12 and then go to
73 00:03:21,780 --> 00:03:27,300 Home tab in conditional formatting
74 00:03:23,580 --> 00:03:30,300 highlight cell rules oh no data bars and
75 00:03:27,300 --> 00:03:33,420 it was saying gradient to Orange
76 00:03:30,300 --> 00:03:34,980 okay after that Noemi wants to calculate
77 00:03:33,420 --> 00:03:36,420 the total for this domain collection
78 00:03:34,980 --> 00:03:38,580 data switch to the all instrument
79 00:03:36,420 --> 00:03:40,019 worksheet and then create the following
80 00:03:38,580 --> 00:03:45,360 formula so go to all instrument
81 00:03:40,019 --> 00:03:47,459 worksheet in cell b40 so select b40 and
82 00:03:45,360 --> 00:03:51,920 then it is saying enter account a
83 00:03:47,459 --> 00:03:54,680 function so count a
84 00:03:51,920 --> 00:03:57,060 function and function always have
85 00:03:54,680 --> 00:03:59,519 brackets open parenthesis close
86 00:03:57,060 --> 00:04:01,019 parenthesis and then with the structure
87 00:03:59,519 --> 00:04:03,540 reference to count the value in the
88 00:04:01,019 --> 00:04:06,080 instrument column of instruments table
89 00:04:03,540 --> 00:04:06,080 so
90 00:04:06,120 --> 00:04:10,980 so this is instrument still table and to
91 00:04:09,239 --> 00:04:13,500 tap into the instrument column we should
92 00:04:10,980 --> 00:04:16,139 now do square bracket and then
93 00:04:13,500 --> 00:04:18,840 instrument and then Square back is
94 00:04:16,139 --> 00:04:21,919 closed and then hit enter
95 00:04:18,840 --> 00:04:26,280 sorted that in f4d
96 00:04:21,919 --> 00:04:28,740 so you have for this F40 and you use
97 00:04:26,280 --> 00:04:31,740 some function
98 00:04:28,740 --> 00:04:33,660 open parenthesis close matrices and then
99 00:04:31,740 --> 00:04:35,460 in the price column of instrument table
100 00:04:33,660 --> 00:04:36,780 so
101 00:04:35,460 --> 00:04:41,699 same thing
102 00:04:36,780 --> 00:04:46,139 in the price column then hit enter after
103 00:04:41,699 --> 00:04:48,620 that in g4d G40 and again same song
104 00:04:46,139 --> 00:04:48,620 function
105 00:04:49,820 --> 00:04:53,840 estimated value
106 00:04:51,840 --> 00:04:56,520 so
107 00:04:53,840 --> 00:04:59,580 instruments in
108 00:04:56,520 --> 00:05:01,560 estimated value
109 00:04:59,580 --> 00:05:03,720 so just double click on that drop down
110 00:05:01,560 --> 00:05:06,000 and it will prefill you
111 00:05:03,720 --> 00:05:08,820 and then hit OK
112 00:05:06,000 --> 00:05:10,560 so finally stay with this thing are
113 00:05:08,820 --> 00:05:12,540 preparing to sell off some of the
114 00:05:10,560 --> 00:05:15,560 instrument and when we want to include
115 00:05:12,540 --> 00:05:18,300 sales date in the instrument tab table
116 00:05:15,560 --> 00:05:19,139 add a table column in the end of the
117 00:05:18,300 --> 00:05:21,960 table
118 00:05:19,139 --> 00:05:25,320 so just click here and then right click
119 00:05:21,960 --> 00:05:28,220 and then insert insert and table column
120 00:05:25,320 --> 00:05:28,220 to the right
