Repair and Share Club Excel Project

Anonymous
timer Asked: Apr 15th, 2019
account_balance_wallet $10

Question Description

Project Description:

You have been asked to advise a new club on campus called the Repair and Share Club. The purpose of the club 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. The dean requires a report of the club’s activities over the academic year in a spreadsheet format that tracks student involvement as well as donation information.


The WORD FILE IS AN INSTRUCTION ONLY. SO YOU DON'T HAVE TO DO ANYTHING ON IT.

Unformatted Attachment Preview

Guilford College Repair and Share Club StudentID 30456 58965 56792 48795 23597 21486 64687 48946 54684 46846 41123 LastName Boucher Garcia Glonden Blasise Linde Benoit Cyr Chu Villani Zacheris Ching FirstName Tom Franko Thomas Cheryl Alice Alexia Judy Lin Mark John Lau Year Senior Senior Junior Sophomore Senior Senior Junior Sophomore Junior Sophomore Junior Major Computer Science Computer Science MIS Networking Networking MIS Computer Science Computer Science Engineering Engineering MIS Programming Course Y Y Y Y Y N Y Y N Y N Student Report Analysis Headcount Total Total Hours Senior Junior Sophomore "A" Students "D-" Students Company Safety Inc. First National Bosco's Software City Sacari Financial Value $ $ $ $ $ Company Donation Information Donated Desktops Laptops 4,600 23 10 13 450 33 22 11 575 25 11 14 1,950 17 4 13 1,600 8 2 6 20190415025930alfarj_e03_grader_h2_shareclub.xlsx Status Network Course Y Y N Y Y N Y Y N Y N Volunteer Hours GPA Grade 152 3.21 182 4.00 133 1.23 58 3.67 110 3.91 149 4.15 145 2.10 94 3.50 72 1.77 113 1.98 109 2.40 GPA 0.00 1.00 1.25 1.75 2.00 2.25 2.75 3.00 3.25 3.50 3.90 4.00 4.25 Grade F DD D+ CC C+ BB B+ AA A+ 20190415025930alfarj_e03_grader_h2_shareclub.xlsx 1. How many students have an A or B grade? 2. Which student has the highest GPA? 3. Which student logged the most volunteer hours? Which student logged the least? 20190415025930alfarj_e03_grader_h2_shareclub.xlsx Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_BU03_GRADER_PS2_HW - ShareClub 1.4 Project Description: You have been asked to advise a new club on campus called the Repair and Share Club. The purpose of the club 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. The dean requires a report of the club’s activities over the academic year in a spreadsheet format that tracks student involvement as well as donation information. 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 e03_grader_h1_ShareClub.xlsx. Save the file with the name e03_grader_h1_ShareClub_LastFirst, replacing LastFirst with your last and first name. 0.000 2 On the ClubInformation worksheet, format the range A5:J16 as a Table. Assign named ranges using the Top row. 10.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 absolute references in your formula as necessary. 6.000 4 Insert a new column to the left of column H. In cell H5, type Courses Completed. Resize the column as needed. Assign the named range using the top row. 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. 8.000 In cells B22:B24, using named ranges, create functions that count the number of students if their year in school matches the years listed in A22:A24. 6 7 In cells C22:C24, using named ranges, create functions that total the number of volunteer hours if their year in school matches the years listed in A22:A24. In cells C25:C26, using named ranges, create functions that total the number of volunteer hours if the criteria n A26:A27 is met. In cell A38, type Totals. Create a formula in cells B38:E38 to find the total sum for the Value, Donated, Desktops, and Laptops columns respectively. 9.000 4.000 8 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 range F34:F37. 8.000 9 Using the data in the table, insert a PivotTable in cell A10 on the PivotAnalysis worksheet. Use the following criteria to create the PivotTable: 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). 10.000 Updated: 10/11/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step Excel Project Points Possible Instructions 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 Total Hours. In cell C10, type Total Students. Resize the columns as needed. 10 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 F10. 8.000 11 In the slicer, select A, A-, B+, and B-. View the PivotTable data to determine how many students have a grade of an A or a B. Click cell A2, click the drop-down arrow, and then select the number of students. 8.000 Clear the filter in the slicer, and then in the slicer select the grade of an A. In cell B8, filter the data so the student with the highest GPA displays. 12 13 14 View the PivotTable data to determine which student has the highest GPA. Click cell A4, click the drop-down arrow, and the select the name of the student with the highest GPA displayed. Clear the slicer filters. Remove the Grade and Year fields from the ROWS area of the PivotTable. Sort the Total Hours field in largest to smallest order. View the PivotTable data to determine the student with the most volunteer hours and the least volunteer hours. Click cell A6, click the drop-down arrow, and the select the name of the student with the most volunteer hours. Click cell B6, click the drop-down arrow, and the select the name of the student with the least amount of volunteer hours. Apply Pivot Style Medium 9 to the PivotTable. Apply Slicer Style Dark 1 to the slicer. Sort the PivotTable by Grades A to Z. 8.000 8.000 3.000 Note, depending upon the version Office being used, the Pivot style name may be Light Blue, Pivot Style Medium 9. The slicer style name may be Light BLue, Slicer Style Dark 1. 15 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 Volunteer Hours by Student. Apply Style 4 to the PivotChart. 7.000 16 Ensure that the worksheets are correctly named and placed in the following order in the workbook: ClubInformation, PivotChartAnalysis, PivotAnalysis. Save the workbook, exit Excel, and then submit your file as directed by your instructor. 0.000 Total Points Updated: 10/11/2017 2 100.000 Current_Instruction.docx ...
Purchase answer to see full attachment

Tutor Answer

ProfessorArthur
School: New York University

Attached.

Guilford College Repair and Share Club

StudentID
30456
58965
56792
48795
23597
21486
64687
48946
54684
46846
41123

LastName
Boucher
Garcia
Glonden
Blasise
Linde
Benoit
Cyr
Chu
Villani
Zacheris
Ching

FirstName
Tom
Franko
Thomas
Cheryl
Alice
Alexia
Judy
Lin
Mark
John
Lau

Year
Senior
Senior
Junior
Sophomore
Senior
Senior
Junior
Sophomore
Junior
Sophomore
Junior

Major
Computer Science
Computer Science
MI...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors