Expert answer:Ms excel project
Expert answer:
instructions_sc_ex16_6a.docx
sc_ex16_6a_igormbiso_1.xlsx
Unformatted Attachment Preview
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
Fish Chips Electronics
CREATING, SORTING, AND QUERYING A TABLE
GETTING STARTED
•
Open the file SC_EX16_6a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as SC_EX16_6a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
o
•
If you do not see the .xlsx file extension in the Save As dialog box, do
not type it. The program will add the file extension for you automatically.
With the file SC_EX16_6a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
PROJECT STEP
1.
Cyril Netreba works at Fish Chips Electronics, which sells inexpensive
computers (called “Fish Chip systems”) to schools to help teach computer
science and electrical engineering. Cyril wants to summarize some information
on Fish Chips Electronics’ product data into tables to help him train a new sales
associate.
Switch to the Systems worksheet. Select the range A1:H9 and format the range
as a table (with headers), using the Table Style Medium 14. (Hint:
Depending on your version of Office, the Table style may appear as Green,
Table Style Medium 14.)
Use SystemsTable as the name of the table.
2.
Fish Chips Electronics approved a new third-party sensor for use with the Fish
Chip Systems.
Switch to the Sensors worksheet. Enter a new record into the end of the
SensorsTable table (in row 15) as shown in Table 1 below:
Table 1: New SensorsTable Record
Sensor ID
Sensor
Name
Fish Chip
Classic
Compatible
Fish Chip 2
Compatible
Fish Chip 3
Compatible
Cost
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
S487
Adi
Humidity
Sensor
Yes
Yes
No
$5.50
3.
Sort the SensorsTable table from smallest to largest by the Cost field values.
4.
On the Sensors worksheet, Cyril wants to display only sensors that are
compatible with the Fish Chip 3 systems below the SensorsTable data.
Use an advanced filter to copy all the records for sensors compatible with the
Fish Chip 3 systems from the SensorTable into a new range as described below:
a.
Enter Yes into cell E19 to set up the value to filter on in the criteria
range.
b.
In the Advanced Filter, use the range A1:F15 as the List range, the range
A18:F19 as the Criteria range, and copy the records to the range
A21:F21. (Hint: 10 records will be copied from the SensorsTable data.)
5.
Switch to the Displays worksheet. Apply Wrap Text formatting to the range
E1:G1.
6.
Remove the duplicate record in the DisplaysTable table.
7.
LED displays are the most popular displays sold by Fish Chips Electronics and
Cyril wants to highlight these displays in the table.
Filter the table to display only those records with a Type column value of LED.
8.
At the beginning of the year, Fish Chips Electronics makes a bulk order of cases
for each system they sell. Cyril wishes to show his sales associate a snap-shot
of the number of cases that were ordered, sold, and are available at the
warehouse for each Fish Chip system. Cyril already sorted the table containing
this data, he just needs to insert subtotals into the table.
Switch to the Cases Subtotal worksheet. Convert the CasesSubtotal table (in
the range A1:I24) into a range.
9.
10.
After converting the table to a range, insert subtotals into the range A1:I24
using the following options:
a.
Subtotals should be inserted at each change in the System
Compatibility value.
b.
The subtotals should use the Sum function.
c.
Subtotals should be added to the 2018 Inventory, Cases Sold, and
Cases Available values only.
d.
The subtotals should replace current subtotals and include a summary
below the data.
Cyril wishes to highlight some additional data on the cases sold by Fish Chips
Electronics. He first wants to identify how many options are available for each
type of case.
Switch to the Cases worksheet and complete the following steps to the identify
how many case options are available for each case type.
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
11.
12.
a.
In cell C2, enter a formula using the COUNTIF function that counts the
number of cases with the Unibody case type. Use Unibody as the criteria
argument, and CasesTable[Body Type] as the range argument in your
formula.
b.
In cell C3, enter a formula using the COUNTIF function that counts the
number of cases with the 2-Piece case type. Use 2-Piece as the criteria
argument, and CasesTable[Body Type] as the range argument in your
formula.
c.
In cell C4, enter a formula using the COUNTIF function that counts the
number of cases with the Compact case type. Use Compact as the
criteria argument, and CasesTable[Body Type] as the range argument
in your formula.
Complete the following steps to determine the total number of cases were sold
for each body type.
a.
In cell E2, enter a formula using the SUMIF function that totals the value
in the Cases Sold column for each case with a Unibody case type. Use
Unibody as the criteria argument, CasesTable[Body Type] as the
range argument, and CasesTable[Cases Sold] as the sum_range
argument in your formula.
b.
In cell E3, enter a formula using the SUMIF function that totals the value
in the Cases Sold column for each case with a 2-Piece case type. Use 2Piece as the criteria argument, CasesTable[Body Type] as the range
argument, and CasesTable[Cases Sold] as the sum_range argument in
your formula.
c.
In cell E4, enter a formula using the SUMIF function that totals the value
in the Cases Sold column for each case with a Compact case type. Use
Compact as the criteria argument, CasesTable[Body Type] as the
range argument, and CasesTable[Cases Sold] as the sum_range
argument in your formula.
In the range H8:H30 (the Cases Sold column), create a new Icon Set
conditional formatting rule as described below:
a.
Use the 3 Signs icon set in your conditional formatting rule.
b.
Display the green, circular icon in all cells with a Number type value
greater than or equal to 60.
c.
Display the yellow, triangular icon in all cells with a Number type value
less than 60 and greater than or equal to 25.
d.
Display the red, diamond icon in all cells with a Number type value less
than 25.
13.
Apply a custom sort on multiple fields to the CasesTable, so that the table is
first sorted by the System Compatibility field in descending (Z to A) order,
then by the Body Material field in ascending (A to Z) order, and finally by the
Body Type field in ascending order.
14.
Add a Total Row to the CasesTable. (Hint: The total row should appear in row
31 with a total for Cases Sold automatically appearing in cell H31.)
Using the total row, display the SUM of the 2018 Inventory in cell G31.
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
15.
Upon review of the CasesTable, Cyril notices the Cases Available column needs
to be added to the table.
Add a calculated column to the CasesTable (in the range A7:H30) as described
below:
a.
In cell I7, enter the text Cases Available as the calculated column’s
heading.
b.
In cell I8, enter a formula without a function using structured references
that subtracts the value in the Cases Sold column from the value in the
2018 Inventory column. Use [2018 Inventory] and [Cases Sold] as
the structured references in your formula.
The formula should autofill into the range I8:I30. If it does not, copy the
formula into the range.
c.
16.
Using the table’s total row (in row 31), SUM the values in the Cases
Available column.
Fish Chips Electronics also sells packaged sets that combine popular Fish Chip
systems with an LED display, a case, and a simple project guide. The
PackageTable on the Packages worksheet identifies the system and display
included in each package by ID number rather than by name. Cyril wants to set
up a simple lookup table to make it easier for his new sales associate to identify
what is included in each package.
Switch to the Packages worksheet. In cell H3, create a formula using the
VLOOKUP function to determine the type of system included with the package
using the following parameters:
17.
18.
a.
The formula should use cell J3 (the System ID) as the lookup_value, the
range G7:H12 as the table_array, and 2 as the col_index_num.
b.
Leave the optional range_lookup parameter blank.
In cell H5, create a formula using the VLOOKUP function to display the name of
the package using the following parameters:
a.
The formula should use cell H2 (the Package ID) as the lookup_value, the
table named PackagesTable as the table_array, and 4 as the
col_index_num.
b.
Leave the optional range_lookup parameter blank.
Cyril also wants to highlight the number of packages associated with the latest
iteration of the Fish Chip System – the Fish Chip 3 Mod C+.
In cell H16, create a formula using the DCOUNT function to count the number
of packages associated with the Fish Chip 3 Mod C+ systems, using the range
A1:D22 as the database, “System ID” as the field, and the range H14:H15
as the criteria.
19.
In cell H17, create a formula using the DAVERAGE function to determine the
average cost of a Fish Chip 3 Mod C+ package, using using the range A1:D22
as the database, “Cost” as the field, and the range H14:H16 as the criteria.
Your workbook should look like the Final Figures on the following pages. Save your
changes, close the document, and then exit Excel. Follow the directions on the SAM
website to submit your completed project.
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
Final Figure 1: Systems Worksheet
Final Figure 2: Sensors Worksheet
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
Final Figure 3: Displays Worksheet
Final Figure 4: Cases Subtotal Worksheet
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
Final Figure 5: Cases Worksheet
Final Figure 6: Packages Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
Shelly Cashman Excel 2016 | Module 6: SAM Project 1a
Fish Chips Electronics
CREATING, SORTING, AND QUERYING A TABLE
Igor Mbiso
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
System ID
1000
2013
3013
3014
4034
4035
4036
4037
System Name
Fish Chip Classic
Fish Chip 2
Fish Chip 2+
Fish Chip 2 Vinegar
Fish Chip 3 Mod A
Fish Chip 3 Mod B
Fish Chip 3 Mod C
Fish Chip 3 Mod C+
Processor Speed
300 MHz
600 MHz
600 MHz
900 MHz
900 MHz
1.2 MHz
1.2 MHz
1.5 MHz
RAM
500 Mb
1 GB
2 GB
2 GB
1 GB
2 GB
2 GB
2 GB
Power Source
1.0 Amp
1.5 Amp
1.5 Amp
1.5 Amp
2.0 Amp
2.0 Amp
2.5 Amp
2.5 Amp
USB Ports
2
3
3
3
3
4
4
4
Bluetooth
No
No
Yes
Yes
No
Yes
Yes
Yes
Wi-Fi Ready
No
No
No
Yes
Yes
Yes
Yes
Yes
Sensor ID
S103
S203
S304
S345
S378
S397
S399
S400
S401
S402
S414
S321
S357
Sensor Name
Roger Temperature Sensor
Humidity Sensor (wired)
Temperature-Humidity Sensor
Triple-Axis Accelerometer
Light Sensor (Photo Transistor)
Microphone (Omnidirectional)
UV Index/IR/Visible Light Sensor (Digital)
Venti Muscle Sensor
Barcode Scanner
Marcos Ultrasonic Rangefinder
Boraks Ultrasonic Rangefinder
Luminosity Sensor
Double-Axis Accelerometer
Sensor ID
Sensor Name
Sensor ID
Sensor Name
Fish Chip Classic
Compatible
Yes
Yes
No
No
No
No
No
No
No
Yes
No
No
Yes
Fish Chip 3 Compatible Sensors
Fish Chip Classic
Compatible
Fish Chip Classic
Compatible
Fish Chip 2
Compatible
Yes
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Fish Chip 2
Compatible
Fish Chip 2
Compatible
Fish Chip 3
Compatible
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
Yes
No
Yes
Cost
$5.50
$8.00
$14.95
$10.50
$1.25
$2.00
$9.95
$40.50
$65.95
$20.95
$50.95
$6.00
$8.50
Fish Chip 3
Compatible
Cost
Fish Chip 3
Compatible
Cost
Display ID
D4431
D1116
D7500
D4132
D9124
D9651
D9770
D2305
D7554
D1809
D1519
D9651
Display Name
1.3″ OLED – Monochrome
1.3″ LED – Green on Black
1.3″ Touchscreen
1.4″ LED – Square – Backlit
1.4″ LED – Blue on White
1.8″ Touchscreen
1.4″ LED – Backlit
1.4″ LED – Square – Green on Black
10.1″ HDMI (No Touchscreen)
7″ HDMI (No Touchscreen)
7″ HDMI + Touchscreen
1.8″ Touchscreen
Size
1.3″
0.96″
3.5″
1.4″
1.4″
1.8″
1.4″
1.4″
10.1″
7″
7″
1.8″
Type
OLED
LED
Touchscreen
LED
LED
Touchscreen
LED
LED
HDMI
HDMI
HDMI – Touchscreen
Touchscreen
Fish Chip Classic Compatible
Fish Chip 2 Compatible
Fish Chip 3 Compatible Cost
Yes
Yes
Yes
$ 24.50
Yes
Yes
Yes
$
5.50
No
Yes
Yes
$ 40.95
Yes
Yes
Yes
$ 10.95
Yes
Yes
Yes
$ 12.95
No
Yes
Yes
$ 20.95
Yes
Yes
Yes
$ 10.95
Yes
Yes
Yes
$
8.95
No
No
Yes
$ 150.00
No
No
Yes
$ 45.50
No
No
Yes
$ 175.00
No
Yes
Yes
$ 20.95
Case ID
C9257
C2496
C6235
C7656
C3411
C6617
C9894
C4329
C9517
C6557
C6247
C4587
C9956
C4241
C6882
C2914
C9494
C7697
C3765
C8726
C1289
C3933
C3418
System Compatibility
Fish Chip Classic
Fish Chip Classic
Fish Chip Classic
Fish Chip Classic
Fish Chip 2
Fish Chip 2
Fish Chip 2
Fish Chip 2
Fish Chip 2
Fish Chip 2
Fish Chip 2
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Body Material
Aluminum
Plastic
Plastic
Plastic
Aluminum
Plastic
Plastic
Plastic
Plastic
Plastic
Plastic
Aluminum
Aluminum
Aluminum
Aluminum
Plastic
Plastic
Plastic
Plastic
Plastic
Plastic
Plastic
Plastic
Body Type
Unibody
Unibody
2-Piece
2-Piece
Unibody
2-Piece
Unibody
Unibody
2-Piece
Unibody
2-Piece
Unibody
2-Piece
Unibody
2-Piece
2-Piece
2-Piece
Compact
Unibody
2-Piece
2-Piece
Unibody
Compact
Color
Standard
Clear
Blue
Translucent Green
Standard
Smoke Grey
Blue
Green
Translucent Smoke Grey
Red
Translucent Orange
Standard
Standard
Standard
Standard
Rainbow
Translucent Green
Clear
Pink
White
Translucent Black
Translucent Black
Black
Mounting Plate
No
No
No
No
No
Yes
Yes
Yes
Yes
Yes
No
Yes
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
2018 Inventory
50
25
25
25
75
100
50
100
50
100
50
100
50
150
150
50
50
150
50
100
75
50
200
Cases Sold
28
17
13
10
37
36
28
33
28
53
15
37
31
58
94
37
35
106
32
37
56
36
81
Cases Available
22
8
12
15
38
64
22
67
22
47
35
63
19
92
56
13
15
44
18
63
19
14
119
Case Types
Unibody
2-Piece
Compact
Case ID
C2496
C9257
C6617
C3411
C6235
C7656
C9894
C4329
C9517
C2914
C4587
C9494
C7697
C3765
C9956
C4241
C6882
C8726
C1289
C3933
C3418
C6557
C6247
System Compatibility
Fish Chip Classic
Fish Chip Classic
Fish Chip 2
Fish Chip 2
Fish Chip Classic
Fish Chip Classic
Fish Chip 2
Fish Chip 2
Fish Chip 2
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 3
Fish Chip 2
Fish Chip 2
Body Material
Plastic
Aluminum
Plastic
Aluminum
Plastic
Plastic
Plastic
Plastic
Plastic
Plastic
Aluminum
Plastic
Plastic
Plastic
Aluminum
Aluminum
Aluminum
Plastic
Plastic
Plastic
Plastic
Plastic
Plastic
2018 Inventory
750
725
350
Body Type
Unibody
Unibody
2-Piece
Unibody
2-Piece
2-Piece
Unibody
Unibody
2-Piece
2-Piece
Unibody
2-Piece
Compact
Unibody
2-Piece
Unibody
2-Piece
2-Piece
2-Piece
Unibody
Compact
Unibody
2-Piece
Cases Sold
Color
Clear
Standard
Smoke Grey
Standard
Blue
Translucent Green
Blue
Green
Translucent Smoke Grey
Rainbow
Standard
Translucent Green
Clear
Pink
Standard
Standard
Standard
White
Translucent Black
Translucent Black
Black
Red
Translucent Orange
Cases Available
750
725
350
Mounting Plate
No
No
Yes
No
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
No
Yes
Yes
Yes
Yes
Yes
Yes
No
2018 Inventory
Cases Sold
25
50
100
75
25
25
50
100
50
50
100
50
150
50
50
150
150
100
75
50
200
100
50
17
28
36
37
13
10
28
33
28
37
37
35
106
32
31
58
94
37
56
36
81
53
15
Package ID
System ID
Display ID
Cost
P12018
4037 D2305
$ 72.90
P12084
4037 D1116
$ 69.45
P12085
4037 D9770
$ 74.90
P12113
4035 D2305
$ 66.45
P12167
4034 D4132
$ 65.90
P12292
4035 D4132
$ 68.45
P12367
4036 D2305
$ 69.45
P12381
4034 D2305
$ 63.90
P12382
4034 D9770
$ 65.90
P12414
3014 D4132
$ 63.95
P12574
4036 D9770
$ 71.45
P12614
4037 D9124
$ 76.90
P12617
4035 D9124
$ 70.45
P12618
4036 D4132
$ 71.45
P12687
4036 D1116
$ 66.00
P12777
4035 D9770
$ 68.45
P12823
4037 D4132
$ 74.90
P12879
4036 D9124
$ 73.45
P12913
4034 D1116
$ 60.45
P12959
4034 D9124
$ 67.90
P12970
4035 D9770
$ 68.45
Package Details
Package ID
P12687
System Name
System ID
Display Name
1.3″ LED – Green on Black
4036
Display ID
D1116
Cost
System ID
System Name
Display ID
3014 Fish Chip 2 Vinegar
D9770
4034 Fish Chip 3 Mod A
D2305
4035 Fish Chip 3 Mod B
D4132
4036 Fish Chip 3 Mod C
D9124
4037 Fish Chip 3 Mod C+
D1116
System Name
Fish Chip 3 Mod C+
Number of Fish Chip 3 Mod C+
Packages
Average Cost of Fish Chip 3 Mod
C+ Packages
System ID
4037
Display Name
1.4″ LED – Backlit
1.4″ LED – Square – Green on Black
1.4″ LED – Square – Backlit
1.4″ LED – Blue on White
1.3″ LED – Green on Black
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades