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 test, mid-term, final, and homework. Each individual grade will be recorded as points earned out of 100 total possible.

Pop quizzes (4) Regular test(4) Mid-Term 20% Final 20%

Homework (5)

20% (drop lowest grade) 100%

10% (drop lowest grade) 30% (drop lowest grade)

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 cutoffs 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 test, 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.

6. A calculated final average.

7. A final letter grade. Use an IF statement and absolute cell referencing for grade cutoffs 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 class average for each individual grade item, combined grade categories (Pop quizzes, Regular test, 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 test, 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 test, Mid-Term, Final and Homework), and final average.

11. Center all column headings

12. Bold columns for total pop quiz points, total test 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. 14. Put your name at the top of the spreadsheet.

15. Upload your completed assignment to the Dropbox for assignment 2 in D2L by the due date. Verify that you assignment is uploaded correctly. This can be accomplished by opening the uploaded file to verify the file opens properly.

This is an individual assignment and you should create this spreadsheet on your own. Keep in mind that the tutorial is a learning tool and completion of the tutorial is not the assignment. You should start with a blank spreadsheet and build the assignment from scratch.