# Excel Grade Book Project

Excel Grade Book Project

Understanding how weighted averages work will be a very important topic for you in this course, since your grades are determined in this way. In this project, you will create an Excel spreadsheet that can calculate a math 123 student's final grade for the course. You will then write a reflection paper answering several questions regarding youra project.

In this project you are required to do the following:

1. Create an Excel spreadsheet that will be capable of calculating a student's final grade in this course. Keep in mind that weighting is involved in determining the final grade. Please refer to your math 123 syllabus for specific grading information and use this in your calculations.

Since it is early in the semester, you will only be using the sample student data provided on the following page to test your spreadsheet.

It is recommended that your Excel spreadsheet contain the following:

o Your name, course name and project name

o The scores provided on the next page

o Averages for each category being graded

o A legend which lists the categories used to weight the grade and their assigned weight in the overall course grade

o A labeled cell with the final course grade that uses a formula to calculate the weighted average grade for the course. **This cell SHOULD NOT use Excel's built-in 'SUMPRODUCT' function, come up with your own formula for this cell. If a score is changed in your spreadsheet, your final calculation should recalculate.

In addition to correctly calculating the final grade, your spreadsheet should be well-organized with clear labels.

2. Write a typed reflection paper (minimum one page). It must include the following information:

o Explain how Excel can be used to calculate weighted average grades.

o What is the predicted score based on the data provided? What letter grade is this?

o What would the student need to get on the last exam to bring the weighted average up to the next letter grade higher?

o What would the predicted final score be )based on the original sample student data provided) if the student missed the last 2 homework assignments AND did not turn in the last project?

o How would you respond if a student made the following statement at the end of the semester?, "My final grade is a 58% and I only need 2 more points to get a "D", can't I have some extra credit on the homework so I can pass the course?"

o Professor Ivy calculates her grades as points out of total points for quizzes and homework:

o Example HW1 = 89/90, HW2 = 56/72, HW3 = 25/25, Q! = 56/72 and Q2 = 25/30. Since each grade is out of a different point value, Professor Ivy does not want to turn these grades into percentages. How would you have to modify (if any) your spreadsheet to calculate a student's final grade in Professor Ivy's class?

o Reflect on your experience creating this spreadsheet - What have you learned from doing this project?

o What did you notice about Excel spreadsheets and how can this help you determine your future grade in this course?

o How might you use Excel in the future?

In addition to addressing the statements above, your paper should:

• Have a logical organization.

• Be able to be understood by an audience of your peers (i.e. don't assume that you are writing this to your instructor, make sure anyone could understand your statements.)

• Provide support for you statements. E.g. explain how values were found.

• Give specific values (i.e. quantify statements).

Use the following sample student data set to compute the final grade for this course using an Excel spreadsheet. Assume all scores are out of 100 and that all grades have been recorded for this student.

https://brainmass.com/statistics/descriptive-statistics/excel-grade-book-project-618324

#### Solution Preview

The solution to question #1 is provided in the Excel file attached. All computations have been shown in the Excel file using a grading scale (which is also provided in the Excel file).

The solution to question #2 is as follows.

2. Write a typed reflection paper (minimum one page). It must include the following information:

o Explain how Excel can be used to calculate weighted average grades.

Excel can be used to calculate weighted average grades by first finding the average of each category. the average can be found using Excel's AVERAGE function, or by using Excel's SUM function, then dividing by the number of numbers in that category. These averages are then multiplied by the corresponding weights for that category. The weights used are those supplied in the course syllabus for the class. Make sure to correspond the category with the correct weight given in the syllabus. Do not assign the weights incorrectly or the weighted average grades will be incorrect. The weighted average grade is then summed. This sum gives the Final Course Grade for the student.

o What is the predicted score based on the data provided? What letter grade is this?

The predicted score ...

#### Solution Summary

An Excel file with the solution to #1 is provided. The file shows all computations for calculating the final course grade. The answer to #2 is provided both in a Word file and in text form. The solution to #2 references spreadsheets and data in the accompanying Excel file.