I have project to finish, I need help. Please see an attachment.

Thank you.

Instructions for Assignment 5 Step Instructions for Assignment 5 1. Complete the following on both the class 1 and the class 2 worksheets: a. Freeze the panes (both directions) at cell B2. b. Calculate the average homework grades c. Calculate the total quiz points and subtract the lowest quiz grade d. Calculate the numerical grade with the following: Homework Average * 70% Quiz Average * 20% Final Exam * 10% e. Name the range of the Grading Table (to use in the next step). f. Use a VLookUP function with the grading table to display the letter grade. g. Calculate averages for the following: Average grade for all homework assignments Average grade for all quizzes Average grade for all final exams Average overall numerical grade Average overall letter grade 2. Complete the following on the Summary worksheet: a. Display the average grade for all homework assignments for classes 1 and 2 b. Display the average grade for all quizzes for classes 1 and 2 c. Display the average grade for all final exams for classes 1 and 2 d. Display the average grade for the overall numerical grade for classes 1 and 2 e. Display the average grade for the overall letter grade for classes 1 and 2 f. Display the average of both classes for all columns. 3. Complete the following on the Faculty Data worksheet: a. Use an IF function to calculate the amount matched (3% of salary) if the faculty member participates in the retirement plan. If not, display 0. b. Create a Pivot Table with the faculty data (display the pivot table on a separate sheet). The pivot table should show the relation between gender and rank. c. Create a Pivot Chart with the same data (on a separate sheet). d. Copy the Faculty Data worksheet information and paste it on a new sheet called Subtotals. e. On the Subtotals sheet, sort the data by faculty rank. f. Add subtotals (sorting by rank) for salaries. 4. Import the newFaculty.xml file into a new worksheet named New Faculty. 5. Save all the changes on this workbook and upload your final product to Blackboard. Student Assignment 1 100 pts Assignment 2 100 pts Assignment 3 100 pts Assignment 4 100 pts Amy Bruce Molly Zach Colin Carley Jeff Michelle Mona Andrea Bret Alex Anthony Stephen Rose Nicholas Lisa David Jason Trevor Janet 100 0 100 93 100 97 85 0 70 74 100 100 90 92 84 80 88 98 98 92 84 83 0 86 94 86 100 96 0 50 0 90 98 78 20 98 90 96 86 94 88 98 78 0 90 90 0 78 100 0 75 0 100 96 93 0 45 80 70 85 55 90 90 92 0 92 74 0 86 99 0 74 0 100 94 89 0 0 82 86 88 81 96 88 Averages Assignment 5 100 pts Assignment 6 100 pts Assignment 7 100 pts 59 0 100 89 89 84 95 0 74 0 80 97 90 0 0 97 80 77 89 100 90 75 0 0 95 80 95 97 0 75 0 100 90 100 0 0 78 95 80 80 95 85 90 0 100 95 83 90 95 0 60 0 95 100 100 0 0 95 70 100 60 100 0 HomeWork Average Quiz 1 Quiz 2 Quiz 3 80 0 61 73 79 89 58 0 32 10 50 81 53 47 32 9 10 68 66 21 46 57 0 73 40 53 73 67 0 67 0 50 70 90 67 23 63 20 90 30 67 60 77.5 0 100 88.5 85 84.8 92.5 0 72.3 0 82.5 92.5 86 0 0 81.7 92.5 87.5 95 91.7 60 Quiz 4 Quiz 5 Quiz 6 70 0 90 70 100 60 80 0 80 0 90 70 70 0 20 80 40 100 80 50 40 81 0 0 92 88 81 77 0 81 0 65 65 77 0 0 69 46 92 42 73 88 70 0 40 90 100 50 85 0 30 0 40 90 100 0 0 60 20 30 40 30 0 Quiz Pts minus lowest Final Exam 88 0 58 80 78 67 80 0 58 0 53 93 88 0 0 35 43 88 85 88 73 Numerical Grade Letter Grade Student Tim Maryann Bob Dan Tina Lillian Stephanie James Sheila Lorie Tom Sue Mark Tammy Sarah Jacob Seth Ethan Isabelle Ray Averages Assignment 1 100 pts Assignment 2 100 pts Assignment 3 100 pts Assignment 4 100 pts 69 92 96 83 85 86 88 100 88 88 90 89 88 100 94 98 100 100 90 98 0 100 98 71 0 81 97 100 96 98 98 0 75 100 98 91 96 100 95 98 0 0 42 100 0 99 0 88 70 0 0 0 85 100 99 80 100 70 50 95 0 90 77 88 0 91 0 95 80 0 85 0 92 100 92 98 100 100 96 96 Assignment 5 100 pts Assignment 6 100 pts Assignment 7 100 pts 0 68 88 92 0 92 0 100 66 0 67 0 77 95 90 95 100 82 82 94 0 80 90 95 0 90 0 80 93 0 95 0 100 100 100 93 95 70 70 93 0 100 100 100 0 95 0 100 95 0 30 0 75 100 100 100 100 95 80 100 HomeWork Average Quiz 1 Quiz 2 Quiz 3 Quiz 4 0 88 50 75 0 66 61 48 60 0 54 0 63 80 51 32 100 58 34 63 0 70 33 57 0 50 0 83 43 0 0 0 50 93 60 73 87 53 43 83 0 0 71.2 93.5 0 100 0 83.2 0 0 97.5 0 80 91 87.3 100 100 86.5 86.5 100 0 80 70 90 0 70 0 70 70 0 70 0 90 80 70 80 100 90 0 80 Quiz 5 Quiz 6 0 46 35 62 0 73 0 65 54 0 77 0 77 85 0 42 77 27 35 65 0 100 30 10 0 20 0 70 10 0 0 0 60 90 80 45 85 10 20 100 Quiz Pts minus lowest Final Exam 0 70 48 42 0 40 0 62 55 0 55 0 68 98 68 45 82 52 30 80 Numerical Grade Letter Grade Class 1 2 Average Homework Avg Quiz Avg Final Exam Avg Numerical Grade Letter Grade Numerical Grade 0 59.5 62.5 66.5 69.5 72.5 76.5 79.5 82.5 86.5 89.5 92.5 Letter Grade F DD D+ CC C+ BB B+ AA Grade Point 0 0.7 1 1.3 1.7 2 2.3 2.7 3 3.3 3.7 4 Max Percent LastName FirstName Department Smith Jacobson Comensoli Nelsen Coats Jackson Bressette Doepke Wolter Mikkola Downs Nelson True Blackwell Scheib Fitzgerald Rothenberger Smythe Young Lu Weeks Garafano Lewis Bordeau Lee Neumann Mack Anderson Stewart Rais Parker Halpern Weaver Ball Chin Palermo McKaye Smith Hill Garabizien Gao Alicia Andrew Angela Beth Bill Carole Cheryl Cheryl Christine Claudia Clifton Dale David Dean Earl Edmond James Janice Jef Jim Jodie Karen Karl Katherine Kell Kenneth Kevin Kristen Mark Mary Mathew Murray Robert Robin Roger Sheryl Susan Tom Trevor Wendy Xiaoming Rank Accounting Full Prof Management Full Prof Finance Associate Finance Full Prof Accounting Assistant Accounting Instructor Accounting Instructor Accounting Full Prof Accounting Associate Finance Associate Accounting Associate Accounting Full Prof Accounting Full Prof Finance Instructor Management Instructor Management Associate Management Full Prof Management Associate Management Assistant Accounting Instructor Finance Instructor Finance Associate Management Associate Management Assistant Finance Assistant Finance Instructor Management Assistant Accounting Assistant Accounting Assistant Finance Instructor Accounting Full Prof Finance Associate Accounting Full Prof Accounting Instructor Accounting Full Prof Accounting Associate Management Instructor Finance Full Prof Management Instructor Management Instructor Management Full Prof YearHired 1997 1979 1986 1985 1998 2000 1995 1986 1991 1984 1990 1996 1994 2000 1997 1998 1998 1994 1998 1998 1996 1987 1987 1994 2002 1996 1997 1996 1994 2001 1982 1997 1984 1995 1999 1996 1997 1980 1999 2002 1990 Gender Salary F M F F M F F F F F M M M M M M M F M M F F M F M M M F M F M M M F M F F M M F M ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### Participates in Retirement Plan Yes Yes Yes Yes Yes No No Yes Yes Yes Yes Yes Yes No No Yes Yes Yes Yes No No Yes Yes Yes Yes No Yes Yes Yes No Yes Yes Yes No Yes Yes No Yes No No Yes 3% RetirementMatching $