Assignment 8 Part A: Case Problem 1 Data File needed for this Case Problem: Golf.xlsx Early Bird Golf Group Camilla Cortez, organizer of the Early Bird Golf Group, has begun compiling a list of the group’s members. She has asked you to clean and format the data in the worksheet before she continues working on the project. Complete the following:

1. Open the Golf workbook located in the ExcelA ▸ Case1 folder and save the workbook in the Excel Workbook format as Early Bird in the location specified by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Members worksheet, create an Excel table for the data. Name the table Golf.

4. Apply the Phone Number format to the data in the Telephone column.

5. Split the data in the Name column into two columns. Store the first names in column B and the last names in column C. Change the column headers to First Name and Last Name, respectively.

 6. In the Member Since column, apply a custom format that displays only the year.

7. Split the City, State Zip column into three columns named City, State, and Zip, respectively. (Hint: Repeat the split twice. The second split is a fixed width.)

8. The locker numbers were entered from an old system. The only characters that are important are the three numbers after the letter. Use the MID function in column I (name it L Number) to separate those numbers. 9. 9. The last number in the locker number is the actual row that the locker is in. Use the RIGHT function in column J (name it L Row) to separate that value.

10. Save the workbook, and then close it.

 

Assignment 8 Part 2: Case Problem 2 Data File needed for this Case Problem: Lawn.xlsx Dianna’s Lawncare Every two weeks, Dianna Turley collects payroll information for the employees who work for her at her lawncare business. The worksheet with the information is sent to a payroll service that generates the paychecks. Dianna has started to collect the information, but she needs you to clean up the data before it is sent. Complete the following:

1. Open the Lawn workbook located in the ExcelA ▸ Case2 folder included with your Data Files, and then save the workbook in the Excel Workbook format as Lawncare in the location specified by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Employee Hours worksheet, create an Excel table named Hours with a blue table style.

4. Split the data in the Name column into two columns. Store the first name data in column A and the last name data in column B. Change the column headers to First Name and Last Name, respectively.

5. Split the data in the City, State Zip column into three columns. Store the city data in column D, the state data in column E, and the zip codes in column F. (Hint: Repeat the split twice.) Change the column headers to City, State, and Zip, respectively.

6. Use functions as needed to change the data in the City and State columns to use standard capitalization. (Hint: Remember to copy the data as values and remove any unnecessary columns.)7. Apply the Social Security Number format to the data in the SS Number column.

8. Use a function to change the data in the Type of Work column so that it is all lowercase. (Hint: Remember to copy the data as values and remove any unnecessary columns.)

9. Apply a custom format to the data in the Hourly Rate column that shows two decimal places.

10. Apply a custom format to the data in the Overtime Hours column so that full hours display only significant digits and partial hours display two digits whether significant or insignificant. (Hint: Refer to Figure A-11 to see a description of the digit placeholders used in custom formats for values.)

11. Save the workbook, and then close it.