Reference no: EM131435799
Guilford College Repair & Share Club
Project Description:
You have been asked to advise a new club on campus called the Repair and Share Club. Its purpose is to collect used desktop and laptop computers donated from area companies and refurbish them for members of their college community. The students in this club are responsible for collecting and rebuilding these computers to give back to students on campus. The goal is to collect at least 30 computers from each company. The dean requires a report of the club's activities over the academic year in a spreadsheet format that tracks student involvement, company donations and students receiving the refurbished computers.
Instructions
1 Start Excel. Open the downloaded Excel file named e03ps3_grader_a1_start.xlsx. Save the file with the name e03ps3ShareClub_LastFirst, replacing LastFirst with your name.
2 On the Club Information worksheet, in range H6:H16, create a range named Volunteer_Hrs. In range D6:D16, create a range named Class.
3 A grade table appears in the spreadsheet assigning letter grades to grade point averages. In cell J6, use a VLOOKUP function to determine a letter grade for the grade point average in cell I6. Use AutoFill to copy the function to range J7:J16. Use absolute references in your formula as necessary. Fill the formula without formatting so the borders remain formatted.
4 Insert a new column to the left of column H. In cell H5, type Courses Completed. Resize the column as needed.
5 In cell H6, use an IF function with a nested AND function to create a function that analyzes the content of F6 and G6. Return Complete if both cells contain a Y, or Incomplete if one or both cells contain an N. Use AutoFill to copy the function to range H7:H16 without copying formatting.
6 In range A5:K16, insert a table. Use the headers in the top row to create named ranges for each of the columns. Resize the columns as needed.
7 The dean has requested a report of the student's information. She wants a breakdown of the student's year in high school, volunteer hours, and their grades. In cell A20, type Student Report Analysis. In cell B21, type Headcount Total. In cell C21, type Total Hours. Resize the columns as needed. In cell A22, type Seniors. In cell A23, type Juniors. In cell A24, type Sophomores. In cell A26, type "A" Students. In cell A27, type "D-" Students.
8 In cell B22, create a COUNTIF function that counts the number of Seniors in the list using the name range Class. In cell B23, create a COUNTIF function that counts the number of Juniors in the list using the name range Class. In cell B24, create a COUNTIF function that counts the number of Sophomores in the list using the name range Class.
9 In cell C22, using the name ranges Class and Volunteer_Hrs, create a SUMIF function that displays the total number of volunteer hours for Seniors. In cell C23, using the name ranges Class and Volunteer_Hrs, create a SUMIF function that displays the total number of volunteer hours for Juniors. In cell C24, using the name ranges Class and Volunteer_Hrs, create a SUMIF function that displays the total number of volunteer hours for Sophomores.
10 In cell C26, create a SUMIF function that totals the number of volunteer hours for "A" students. In cell C27, create a SUMIF function that totals the number of volunteer hours for "D-" students.
11 The dean has also requested an analysis regarding the companies involved in donating computers to the club. The club's goal is to have each company donate a minimum of 30 computers. In cell A39, type Totals. Use a function in B39:E39 to find the total sum for the Value, Donated, Desktops, and Laptops columns, respectively.
12 In cell F33, create an IF function that evaluates the total number of computers donated from Safety Inc. If a minimum of 30 computers have been donated, the function will return Goal Met. If fewer than 30 computers are donated, the function will return Under Goal. Use AutoFill to copy the function to cell F37.
13 Using the data in the table, insert a PivotTable in cell A10 on the Pivot Analysis worksheet. Use the following criteria to create the PivotTable and answer the questions in rows 1, 3 and 5. Add the Major and GPA fields to the FILTERS area (in that order). Add the Grade, Year, and FirstName fields to the ROWS area (in that order). Add the Volunteer Hours and LastName fields to the VALUES area (in that order). Display subtotals at the bottom of each group.
In cell A10, type Grades. In cell B10, type TotalHours. In cell C10, type TotalStudents. Resize the columns as needed.
14 Insert a slicer for the Grade field. Format the slicer with 3 columns. Resize the slicer to remove the extra white space at the bottom. Move the slicer so the top-left corner is in cell E10.
15 In the slicer, select A, A-, B+, and B-. Use the data in the PivotTable to answer question 1, and then type your answer in cell A2.
16 Clear the filter in the slicer, and then select A. In cell B8, filter the data so the student with the highest GPA displays. In cell A4, type the answer for question 2.
17 Clear the filters. Remove the Grade and Year fields from the ROWS area. Sort the Total Hours field in largest to smallest order. Type your answers for question 3 in cells A6:B6.
18 Apply Pivot Style Medium 9 to the PivotTable. Apply Slicer Style Dark 1 to the slicer. Remove the largest to smallest order sort on the Total Hours field.
19 Insert a Clustered Column PivotChart on a new worksheet named PivotChartAnalysis. Remove the Total Students field from the VALUES area. Delete the legend. In the chart title, replace Total with VolunteerHoursbyStudent. Apply Style 4 to the PivotChart.
20 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Club information, PivotChart Analysis, Pivot Analysis. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
Attachment:- mosken_e03ps3_grader_a1_start_1.xlsx