Shelly CashmanExcel 2016 | Module 6: SAM Project 1a Fish (2024)

Table of Contents
GETTING STARTED PROJECT STEP

Homework answers / question archive / Shelly CashmanExcel 2016 | Module 6: SAM Project 1aFish Chips ElectronicsCreating, Sorting, and Querying a TableGETTING STARTEDOpen the fileSC_EX16_6a_FirstLastName_1

Shelly CashmanExcel 2016 | Module 6: SAM Project 1aFish Chips ElectronicsCreating, Sorting, and Querying a TableGETTING STARTEDOpen the fileSC_EX16_6a_FirstLastName_1

MS Excel

Shelly CashmanExcel 2016 | Module 6: SAM Project 1a

Fish Chips Electronics

Creating, Sorting, and Querying a Table

GETTING STARTED

Open the fileSC_EX16_6a_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file asSC_EX16_6a_FirstLastName_2.xlsxby changing the “1” to a “2”.

0. If you do not see the.xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the fileSC_EX16_6a_FirstLastName_2.xlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEP

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 theSystemsworksheet. Select the range A1:H9 and format the range as a table (with headers), using theTable Style Medium 14. (Hint: Depending on your version of Office, the Table style may appear as Green, Table Style Medium 14.)

UseSystemsTableas the name of the table.

Fish Chips Electronics approved a new third-party sensor for use with the Fish Chip Systems.

Switch to theSensorsworksheet.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

S487

Adi Humidity Sensor

Yes

Yes

No

$5.50

Sort the SensorsTable table fromsmallest to largestby the Costfield values.

On theSensorsworksheet, Cyril wants to display only sensors that are compatible with the Fish Chip 3 systems below the SensorsTabledata.

Use anadvanced filterto copy all the records for sensors compatible with the Fish Chip 3 systems from the SensorTable into a new range as described below:

a. EnterYesinto cell E19 to set up the value to filter on in the criteria range.

b. In the Advanced Filter, use the rangeA1:F15as the List range, the rangeA18:F19as the Criteria range, and copy the records to the rangeA21:F21. (Hint: 10 records will be copied from the SensorsTable data.)

Switch to theDisplaysworksheet. ApplyWrap Textformatting to the range E1:G1.

Remove the duplicate record in the DisplaysTable table.

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 Typecolumn value ofLED.

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 theCases Subtotalworksheet. Convert the CasesSubtotal table (in the range A1:I24) into a range.

After converting the table to a range, insert subtotals into the range A1:I24 using the following options:

c. Subtotals should be inserted at each change in theSystem Compatibilityvalue.

d. The subtotals should use theSumfunction.

e. Subtotals should be added to the2018 Inventory,Cases Sold,andCases Availablevalues only.

f. 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 theCasesworksheet and complete the following steps to the identify how many case options are available for each case type.

g. In cell C2, enter a formula using theCOUNTIFfunction that counts the number of cases with the Unibodycase type. UseUnibodyas the criteria argument, andCasesTable[Body Type]as the range argument in your formula.

h. In cell C3, enter a formula using theCOUNTIFfunction that counts the number of cases with the 2-Piececase type. Use2-Pieceas the criteria argument, andCasesTable[Body Type]as the range argument in your formula.

i. In cell C4, enter a formula using theCOUNTIFfunction that counts the number of cases with the Compactcase type. UseCompactas the criteria argument, andCasesTable[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.

j. In cell E2, enter a formula using theSUMIFfunction that totals the value in theCases Soldcolumn for each case with a Unibodycase type.UseUnibodyas the criteria argument,CasesTable[Body Type]as the range argument, andCasesTable[Cases Sold]as the sum_range argument in your formula.

k. In cell E3, enter a formula using theSUMIFfunction that totals the value in theCases Soldcolumn for each case with a 2-Piececase type. Use2-Pieceas the criteria argument,CasesTable[Body Type]as the range argument, andCasesTable[Cases Sold]as the sum_range argument in your formula.

l. In cell E4, enter a formula using theSUMIFfunction that totals the value in theCases Soldcolumn for each case with a Compactcase type. UseCompactas the criteria argument,CasesTable[Body Type]as the range argument, andCasesTable[Cases Sold]as the sum_range argument in your formula.

In the range H8:H30 (the Cases Sold column), create a newIcon Setconditional formatting rule as described below:

m. Use the3 Signsicon set in your conditional formatting rule.

n. Display the green, circular icon in all cells with aNumbertypevaluegreater than or equal to 60.

o. Display the yellow, triangular icon in all cells with aNumbertype value less than 60 andgreater than or equal to 25.

p. Display the red, diamond icon in all cells with a Numbertype value less than 25.

Apply a custom sort on multiple fields to the CasesTable,so that the table is first sorted by theSystem Compatibilityfield indescending(Z to A) order, then by theBody Materialfield inascending(A to Z)order, and finally by theBody Typefield inascendingorder.

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 theSUMof the2018 Inventoryin cell G31.

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:

q. In cell I7, enter the textCases Availableas the calculated column’s heading.

r. In cell I8, enter a formula without a function using structured references that subtracts the value in theCases Soldcolumn from the value in the2018 Inventorycolumn. 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.

s. Using the table’s total row (in row 31),SUMthe values in theCases Availablecolumn.

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 thePackagesworksheet 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 thePackagesworksheet. In cell H3, create a formula using theVLOOKUPfunction to determine the type of system included with the package using the following parameters:

t. The formula should use cellJ3(the System ID) as the lookup_value, the rangeG7:H12as the table_array, and2as the col_index_num.

u. 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:

v. The formula should use cellH2(the Package ID) as the lookup_value, the table namedPackagesTableas the table_array, and4as the col_index_num.

w. 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 theDCOUNTfunction to count the number of packages associated with the Fish Chip 3 Mod C+ systems, using the rangeA1:D22as the database,“System ID”as the field, and the rangeH14:H15as the criteria.

In cell H17, create a formula using theDAVERAGEfunction to determine the average cost of a Fish Chip 3 Mod C+ package, using using the rangeA1:D22as the database,“Cost”as the field, and the rangeH14:H16as 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.

Final Figure 1: Systems Worksheet

Shelly CashmanExcel 2016 | Module 6: SAM Project 1a Fish (1)

Final Figure 2: Sensors Worksheet

Shelly CashmanExcel 2016 | Module 6: SAM Project 1a Fish (2)

Final Figure 3: Displays Worksheet

Shelly CashmanExcel 2016 | Module 6: SAM Project 1a Fish (3)

Final Figure 4: Cases Subtotal Worksheet

Shelly CashmanExcel 2016 | Module 6: SAM Project 1a Fish (4)

Final Figure 5: Cases Worksheet

Final Figure 6: Packages Worksheet

Shelly CashmanExcel 2016 | Module 6: SAM Project 1a    Fish (2024)
Top Articles
Latest Posts
Article information

Author: Allyn Kozey

Last Updated:

Views: 5849

Rating: 4.2 / 5 (63 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Allyn Kozey

Birthday: 1993-12-21

Address: Suite 454 40343 Larson Union, Port Melia, TX 16164

Phone: +2456904400762

Job: Investor Administrator

Hobby: Sketching, Puzzles, Pet, Mountaineering, Skydiving, Dowsing, Sports

Introduction: My name is Allyn Kozey, I am a outstanding, colorful, adventurous, encouraging, zealous, tender, helpful person who loves writing and wants to share my knowledge and understanding with you.