£15.01
Add to Cart
HW-1013 Grading Excel and Template
Comprehensive Exam II
Project Creating a Grading Workbook
Robert James teaches senior math at Country Day School in Grand Forks, North Dakota. He’s
been developing a grading workbook to calculate final grades based on a weighted average of
homework, projects, quizzes, and exams. It also contains worksheets for entering absences and
tardiness as well as any special notes about student progress and behavior. Robert has
automatically imported his own students’ scores into the Homework, Quizzes, and Exams
worksheets from text files downloaded from a special school database. You’ll complete the
workbook for Robert and then save it as a template file.
Complete the following:
1. Open the Grading workbook provided and save it as Grading Book. In the
Documentation sheet, enter your name and the current date.
2. In the Class Summary worksheet, enter Calculus for the course title, Robert James for
the instructor, and IV for the quarter. Assign the following weights to the grading
components: 25% for Homework, 45% for Quizzes, and 30% for Exams.
3. In the Class Summary worksheet, assign the defined name Grade_Scale to the range
B13:C26.
4. Assign the defined name Student_List to the range B6:B25 of the Student List
worksheet.
5. In the Student Notes worksheet, add data validation to the Student column (the range
B5:B40), limiting entries to the list of students from the Student_List range. In the
Resolve? Column (the range E5:E40), limit entries to either Yes or No.
6. Add the following notes to the worksheet:
Boyd, Jason 4/12/2016 Missing homework Yes
Walton, Marie 4/13/2016 Talking in class, disruptive behavior No
7. In the Attendance worksheet, limit the entries in the Student column to the student names
from the Student_List range. Limit the entries in the Attendance Issue column to either
Absent or Tardy. Limit the entries in the Excused? column to either Yes or No.
8. Add the following attendance issues to the worksheet:
Singleton, Ryan 4/13/2016 Absent Yes
Singleton, Ryan 4/14/2016 Absent Yes
Biggs, Sheila 4/15/2016 Tardy No
9. In the Homework, Quizzes, and Exams worksheets, insert references in the range A9:A28
to the student names in the Student List worksheet. Freeze the worksheets at cell B9.
10. In the Grades worksheet, which will calculate each student’s final grade, insert references
in the range B6:D6 to the three component percentages in the Class Summary worksheet
in the range C9:C11. In cell E6, calculate the total of the percentages and verify that the
sum of the percentages is 100%.
11. In the range A7:A26, insert references to the student names in the Student List worksheet.
12. In cell B7, enter a formula to calculate the first student’s homework percentage score on
homework by adding all of the values in that student’s row in the Homework worksheet
and then dividing the sum by the total number of homework points in row 7 of the
Homework worksheet. Fill the formula into the rest of the column to calculate each
student’s percentage.
13. Repeat Step 12 to calculate each student’s quiz percentage in column C based on values
in the Quizzes worksheet, and to calculate each student’s exam percentage in column D
based on values in the Exam worksheet.
14. In cell E7, use the SUMPRODUCT function to calculate the weighted percentage of each
component score multiplied by the weight assigned to that component. Copy the formula
into the rest of the column to calculate each student’s final overall grade percentage.
[hint: The SUMPRODUCT argument is called an array. An array is a range or group of
related data values. In a spreadsheet program such as Excel the values in an array are
normally stored in adjacent cells. Ergo, one array will be the student's scores; the other
array will be the corresponding weighted percentage of those respective scores.]
15. In cell F7, determine the student’s final grade using the VLOOKUP function based on the
total percentage and an approximate match to the scores from the Grade_Scale range.
Copy the formula into the rest of the column to determine grades for each student.
16. In the Student List worksheet, insert references to the Grades worksheet to display each
student’s final total percentage score and grade.
17. In the Class Summary worksheet, in the range D14:D26, use the COUNTIF function with
the grades in the Student List worksheet to calculate the total number of each grade in the
class.
Δ Save the workbook.
18. Prepare to create a template by deleting the current quarter’s data:
• In the Class Summary worksheet, delete the class data from the range C4:C6;C9:C11.
• In the Student List worksheet, delete the student name data from the range B6:C25.
• In the Student Notes and Attendance worksheets, delete the student notes data and the
attendance data.
• In the Homework worksheet, delete the homework descriptions in row 5, the homework
NOTE: PLEASE PERFORM STEPS 1, 19 and 20 yourself.
Answer will be sent by email as attachment.
Project Creating a Grading Workbook
Robert James teaches senior math at Country Day School in Grand Forks, North Dakota. He’s
been developing a grading workbook to calculate final grades based on a weighted average of
homework, projects, quizzes, and exams. It also contains worksheets for entering absences and
tardiness as well as any special notes about student progress and behavior. Robert has
automatically imported his own students’ scores into the Homework, Quizzes, and Exams
worksheets from text files downloaded from a special school database. You’ll complete the
workbook for Robert and then save it as a template file.
Complete the following:
1. Open the Grading workbook provided and save it as Grading Book. In the
Documentation sheet, enter your name and the current date.
2. In the Class Summary worksheet, enter Calculus for the course title, Robert James for
the instructor, and IV for the quarter. Assign the following weights to the grading
components: 25% for Homework, 45% for Quizzes, and 30% for Exams.
3. In the Class Summary worksheet, assign the defined name Grade_Scale to the range
B13:C26.
4. Assign the defined name Student_List to the range B6:B25 of the Student List
worksheet.
5. In the Student Notes worksheet, add data validation to the Student column (the range
B5:B40), limiting entries to the list of students from the Student_List range. In the
Resolve? Column (the range E5:E40), limit entries to either Yes or No.
6. Add the following notes to the worksheet:
Boyd, Jason 4/12/2016 Missing homework Yes
Walton, Marie 4/13/2016 Talking in class, disruptive behavior No
7. In the Attendance worksheet, limit the entries in the Student column to the student names
from the Student_List range. Limit the entries in the Attendance Issue column to either
Absent or Tardy. Limit the entries in the Excused? column to either Yes or No.
8. Add the following attendance issues to the worksheet:
Singleton, Ryan 4/13/2016 Absent Yes
Singleton, Ryan 4/14/2016 Absent Yes
Biggs, Sheila 4/15/2016 Tardy No
9. In the Homework, Quizzes, and Exams worksheets, insert references in the range A9:A28
to the student names in the Student List worksheet. Freeze the worksheets at cell B9.
10. In the Grades worksheet, which will calculate each student’s final grade, insert references
in the range B6:D6 to the three component percentages in the Class Summary worksheet
in the range C9:C11. In cell E6, calculate the total of the percentages and verify that the
sum of the percentages is 100%.
11. In the range A7:A26, insert references to the student names in the Student List worksheet.
12. In cell B7, enter a formula to calculate the first student’s homework percentage score on
homework by adding all of the values in that student’s row in the Homework worksheet
and then dividing the sum by the total number of homework points in row 7 of the
Homework worksheet. Fill the formula into the rest of the column to calculate each
student’s percentage.
13. Repeat Step 12 to calculate each student’s quiz percentage in column C based on values
in the Quizzes worksheet, and to calculate each student’s exam percentage in column D
based on values in the Exam worksheet.
14. In cell E7, use the SUMPRODUCT function to calculate the weighted percentage of each
component score multiplied by the weight assigned to that component. Copy the formula
into the rest of the column to calculate each student’s final overall grade percentage.
[hint: The SUMPRODUCT argument is called an array. An array is a range or group of
related data values. In a spreadsheet program such as Excel the values in an array are
normally stored in adjacent cells. Ergo, one array will be the student's scores; the other
array will be the corresponding weighted percentage of those respective scores.]
15. In cell F7, determine the student’s final grade using the VLOOKUP function based on the
total percentage and an approximate match to the scores from the Grade_Scale range.
Copy the formula into the rest of the column to determine grades for each student.
16. In the Student List worksheet, insert references to the Grades worksheet to display each
student’s final total percentage score and grade.
17. In the Class Summary worksheet, in the range D14:D26, use the COUNTIF function with
the grades in the Student List worksheet to calculate the total number of each grade in the
class.
Δ Save the workbook.
18. Prepare to create a template by deleting the current quarter’s data:
• In the Class Summary worksheet, delete the class data from the range C4:C6;C9:C11.
• In the Student List worksheet, delete the student name data from the range B6:C25.
• In the Student Notes and Attendance worksheets, delete the student notes data and the
attendance data.
• In the Homework worksheet, delete the homework descriptions in row 5, the homework
NOTE: PLEASE PERFORM STEPS 1, 19 and 20 yourself.
Answer will be sent by email as attachment.
