Reference no: EM13999548
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 notes about student progress and behavior. The school uses a special database system in which homework, quiz, and exam scores are entered automatically during class, and then exported to text files. The workbook must connect to these text files and extract the student scores. You'll finish the workbook, and then save it as a template file. Complete the following:
1. Open the Grading workbook located in the AddCases ? Case1 folder included with your Data Files, and then save it as Grading Book in the location specified by your instructor.
2. In the Documentation sheet, enter your name and the date.
3. 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.
4. In the Class Summary sheet, assign the defined name Grade_Scale to the range B13:C26.
5. In the Student List worksheet, create a connection to the student list stored in the tab-delimited Student List text file located in the AddCases ? Case1 folder. Import the data starting at row 7. Set the import properties so that Excel does not adjust the column width when importing the data. Place the imported data in the range B6:C25.
6. Assign the defined name Student_List to the range B6:B25 of the Student List worksheet.
7. 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 Resolved? Column (the range E5:E40), limit entries to either Yes or No.
8. Add the following notes to the worksheet:
9. 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.
10. Add the following attendance issues to the worksheet:
11. In the Homework worksheet, insert references in the range A9:A28 to the names in the Student List worksheet.
12. Create a connection to the homework scores from the Homework Grades text file AddCases ? Case1 folder. Import the data starting at row 6, excluding the first column. Set the import properties so that Excel does not adjust the column width when importing the data. Import the data into the range B9:O28 in the Homework worksheet.
13. In the Homework worksheet, freeze panes at cell B9.
14. Repeat Steps 11 through 13 for the Quizzes worksheet. In Step 12, import the grades from the Quiz Grades text file in to the range B9:E28 in the Quizzes worksheet.
15. Repeat Steps 11 through 13 for the Exams worksheet. In Step 12, import the grades from the Exam Grades text file into the range B9:C28 in the Exams worksheet.
16. In the Grades worksheet, which will calculate each student's final grade, insert references in the range B6:D6 to the three grading 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%.
17. In the range A7:A26, insert references to the student names in the Student List worksheet.
18. In cell B7, enter a formula to calculate the first student's homework percentage score by adding all 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.
19. Repeat Step 18 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.
20. 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.
21. In cell F7, calculate the student's final grade using the VLOOKUP function based on the total percentage. Use an approximate match to the scores in the Grade_Scale range.
22. In the Student List worksheet, insert references to the Grades worksheet to display each student's weighted average score and grade.
23. In the Class Summary sheet, 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.
24. In the range D14:D26, add green data bars to indicate the frequency of each grade in the class. Save the workbook.
Attachment:- exam_grades.zip