1 100% Excel Assignment 2 Create a spreadsheet that your instructor could use to calculate final averages and letter grades for a fictitious class. Use the point values below for pop quizzes, regular exams, mid-term, final, and homework. Each individual grade will be recorded as points earned out of 100 total possible. Pop quizzes (4) 10% (drop lowest grade) Regular exams (4) 30% (drop lowest grade) Mid-Term 20% 20% Homework (5) 20% (drop lowest grade) 100% Final The spreadsheet should use a similar design and features of the spreadsheet created in your Excel tutorial for assignment 2. More specifically, the following requirements must be met: 1. Each student record should be on one row and each grade item or calculated value should be in one column similar to the tutorial 2. A table that includes cutoff's for each grade level, i.e. 90 for A, 80 for B, etc. 3. Column Headings 4. A minimum of 5 fictitious student records with grades entered for each student. 5. Columns for total points in each category of grades (Pop quizzes, Regular exams, Mid-Term Final and Homework). Create a table with the percentages for each grade category as listed in the instructions above. Use absolute cell referencing to use the percentages from the table to calculate total points for each category. This design would allow easy modification of the percentages so the modifications to the table would be reflected in each individual category F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 Pr 9 1 75% 5. Columns for total points in each category of grades (Popquizzes, Repular exams, Mid-Term Final and Homework). Create a table with the percentages for each grade category as listed in the instructions above. Use absolute cell referencing to use the percentages from the table to calculate total points for each catepory. This design would allow easy modification of the percentages so the modifications to the table would be reflected in each individual category 6. A calculated final average 7. A final letter grade. Use an IF statement and absolute cel referencing for grade cutoff's from the table created in 2 above (similar to what was done in assignment 2 tutorials). Reference the grade level cut off levels from the table so the cut-off levels for each letter grade can be changed in the table and reflected in each individual letter grade. 8. A dass average for each individual grade item, combined grade categories (Pop quizzes, Regular exams, Mid-Term Final and Homework), and final average. Use the average function. 9. The highest (use max function)grade for each individual grade item, combined grade categories (Pop quizzes, Regular exams, Mid-Term Final and Homework), and final average. 10 The lowest (use min function) grade for each individual grade item, combined grade categories (Pop quizzes, Regular exams, Mid-Term, Final and Homework), and final average. 11. Center all column headines 12. Bold columns for total pop quiz points, total exam points, total homework points, mid-term points, final average, and letter grade. 13. Format all calculated values (not individual scores) as number with one decimal point. P DIL
