Illustrated Excel 2016 | Module 8: SAM Project 1b Bay Side Soccer Camp ANALYZING TABLE DATA GETTING STARTED Open the file IL_EX16_8b_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2016 | Module 8: SAM Project 1b

Bay Side Soccer Camp

ANALYZING TABLE DATA

GETTING STARTED

• Open the file IL_EX16_8b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX16_8b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • 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 IL_EX16_8b_FirstLastName_2.xlsx still 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 STEPS

1. Josh is the director of the Bay Side Soccer Camp. You are working as Josh’s assistant to help him organize the clinic information and analyze its income using Excel. You begin by improving the accuracy of input data.

Switch to the Members worksheet and use data validation to create an in-cell dropdown list that restricts data in the Clinic column in the table (F5:F33) using All Skills, Goal Keeper, Field Player as the source values.

2. Josh received a new registration from a member that needs to be added to the member information.

Add a new record to the table on the Members worksheet, entering the data shown in Table 1 below into row 34. Use the in-cell dropdown list to enter the text All Skills into cell F34.

Table 1: Data for Members Worksheet Table

3. Josh asks you to create a hard copy listing the members who haven’t submitted medical forms. 

On the Members worksheet, complete the following actions:

3. 1. Enter the text No into cell I5.
   2. Using the range I4:I5 as the criteria, create an advanced filter that extracts records from the table in the range A4:G34 to the range beginning at cell I7. (Hint: You will need to switch the action in the Advanced Filter dialog box to the “Copy to another location” option.)
4. Josh has created an area in the workbook where clinic information can be easily retrieved. He asks you to enter functions to help with the data retrieval.

Switch to the Clinics worksheet. In cell J8, enter a formula using the VLOOKUP function to display the number of students enrolled in the clinic with the ID # code listed in cell J5 as follows:

4. 1. Use cell J5 as the lookup value.
   2. Use Table2 (table on the Clinics worksheet) as the table array.
   3. The function should return the value in column 7 of the table array.
   4. Use FALSE as the Range_lookup argument.
5. In cell L8, enter a formula using the DSUM function to calculate the total payments corresponding to the gender listed in cell L5 as follows:
   1. Use Table2[#All] (the entire table on the Clinics worksheet, including the header row) as the formula database.
   2. Use the Payments field header (in cell H4) as the field argument.
   3. Use the range L4:L5 as the criteria argument.
6. In cell N8, enter a formula using the DCOUNTA function to count the number of clinics on the Clinics worksheet with fewer than 10 members enrolled as follows:
   1. Use Table2[#All] (the entire table on the Clinics worksheet, including the header row) as the formula database.
   2. Use the ID # field header (in cell A4) as the field argument.
   3. Use the range N4:N5 as the criteria argument.
7. Josh asks you for a list of clinics that are not at capacity organized by session.

Switch to the Open Clinics worksheet. Use a number filter in the Enrolled column to display clinics with openings (<10). [Mac Hint: In the filter dialog box, filter values that are less than 10.]

8. Sort the filtered table by Session in ascending (Smallest to Largest) order (Column D).
9. Josh asks you for help in getting a breakdown of the payments by session.

Switch to the Subtotals worksheet. Sort the table on the Subtotals worksheet by the Session value (Column D) in ascending (Smallest to Largest) order.

10. Convert the table to a range.
11. Add subtotals to the range A4:F43 that, for each change in the Session value, will use the Sum function to add subtotals to the Cost field. Keep the default settings of displaying the summary below data and replacing current subtotals.
12. Use the outline buttons to display only session numbers with subtotals and a grand total.
13. Josh is meeting with the camp director and asks you for hard copies of the worksheets in the workbook. You will add identifying information on each worksheet.

Group the Members, Clinics, Open Clinics, and Subtotals worksheets. Add a header with the text Bay Side Soccer Camp to the center header section of the four grouped worksheets, and use Header & Footer Elements to insert a footer with the Sheet Name in the center footer section. Switch to Normal view if necessary, and ungroup the worksheets.

Your workbook should look like the Final Figures below. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Members Worksheet

Final Figure 2: Clinics Worksheet

Final Figure 3: Open Clinics Worksheet

Final Figure 4: Subtotals Worksheet