YOF EM03 A1 – Guilford College Repair & Share Club 1.1
Brazil2013 bought 14 out of 15 answered question(s)
Excel III
Information are in the att
Attachments:
YOF EM03 A1 – Guilford College Repair & Share Club 1.1
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:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
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. 0.000
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. 4.000
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.000
4 Insert a new column to the left of column H. In cell H5, type Courses Completed. Resize the column as needed. 3.000
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.000
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. 5.000
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. 5.000
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. 7.000
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. 7.000
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. 7.000
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. 10.000
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. 4.000
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. 8.000
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. 0.000
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. 8.000
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. 4.000
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. 8.000
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. 3.000
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. 7.000
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. 0.000
Total Points 100.000
Guilford College Repair & Share Club
Student Information
StudentID LastName FirstName Year Major “Programming
Course” Network Course Volunteer Hours GPA Grade GPA Grade
30456 Boucher Tom Senior Computer Science Y Y 152 3.21 0.00 F
58965 Garcia Franko Senior Computer Science Y Y 182 4.00 1.00 D-
56792 Glonden Thomas Junior MIS Y N 133 1.23 1.25 D
48795 Blasise Cherly Sophomore Networking Y Y 58 3.67 1.75 D+
23597 Linde Alice Senior Networking Y Y 110 3.91 2.00 C-
21486 Benoit Alexia Senior MIS N N 149 4.15 2.25 C
64687 Cyr Judy Junior Computer Science Y Y 145 2.10 2.75 C+
48946 Chu Lin Sophomore Computer Science Y Y 94 3.50 3.00 B-
54684 Villani Mark Junior Engineering N N 72 1.77 3.25 B
46846 Zacheris John Sophomore Engineering Y Y 113 1.98 3.50 B+
41123 Ching Lau Junior MIS N N 109 2.40 3.90 A-
4.00 A
4.25 A+
Company Donation Information
Company Value Donated Desktops Laptops Status
Safety Inc. $4,600 23 10 13
First National $450 33 22 11
Bosco’s $575 25 11 14
Software City $1,950 17 4 13
Sacari Financial $1,600 8 2 6