Homework answers / question archive / Activity 4 — Weighted Average Gradebook To get started: Download the two files from Canvas: Activity 4 — Gradebook Instructions

Activity 4 — Weighted Average Gradebook To get started: Download the two files from Canvas: Activity 4 — Gradebook Instructions

MS Excel

Share With

---

Activity 4 — Weighted Average Gradebook

To get started: Download the two files from Canvas:

Activity 4 — Gradebook Instructions.pdf - this file with instructions for the Activity

Activity 4 — Gradebook Template.xlxs - File for your work

Save the Template file and change the name to:

Activity 4 — Gradebook Lastname _Firstname.xlxs using your own name instead.

You will submit your completed Excel file. The cells that require input of either data or formulas are lightly shaded in tan color.

Discussion and Instructions:

In this Activity you will create a spreadsheet which you can use to calculate your grade in this or other courses. You will calculate the weighted average of the various components of the course that make up your final grade.

In addition to simply calculating your earned grade, you will also calculate an answer to the age- old question of “What grade do I need to get on the Final Exam to get an A (or other grade) in the course?” Finally, you will construct a GPA calculator that you can use to track your GPA given all the courses you have taken.

First, review the concept of weighted average from Section 6.C in your textbook. Weighted averages are commonly used in the real world, where some results are more important than others. The more important ones have larger weights and contribute more to the final result than the lower weighted ones. Weights can be expressed as percentages adding up to 100%, or may simply be numbers that add up to any total at all. That’s why the sum of the weights appears in the denominator of the formula below.

If there are k scores x; through xx, and corresponding weights weight; through weight,, then the weighted average is given by:

Weighted Average = S(weight_k . X_k)/Sweight_k

In this course, your grade is made up of the following components with weights as shown. You can check this in the Syllabus.

Grade Category	Weight
Homework Problems	10%
Activity Problems	15%
Excel-based Projects (2)	20%
Quizzes	15%
Mid-term Exam	20%
Final Exam	20%

 

You will assume that an imaginary student “Oliver” has earned the following grades for each of these categories: Homework = 88, Activities = 84, Projects = 77, Quizzes = 82, Mid Term = 75. Use these as data for your calculations. Note that Oliver doesn’t know his Final Exam grade yet.

Relative cell addressing:

In each of these calculation blocks below, remember what you have learned about relative and absolute cell addressing. A relative cell address (such as a cell like C8) will automatically update to C9 when you copy it to row 9, or to D8 if you copy it to column D. Use a dollar sign $ in front of either the column or row number or both to make the reference absolute and stop that element from updating when copied.

When you get the formulas correct for the first row in a block, you may be able to copy them down the column if you have used the proper type of addressing.

Part 1 - Basic Gradebook (40 points):

• In the worksheet Template, enter the weights as indicated in cells C10 to C15, and enter the grades for each category in cells D10 to DI5. Create a formula for the sum of weights in cell C16.
• Then create formulas for the Weighted Pts Earned in cells E10 to E15 as the weight times the score for each row.
• Now, insert the formula for the Weighted Average Grade in cell E17 using the results you have calculated so far.

You now should have a working gradebook. By plugging in different Final Exam or other grades, you can explore “what if’ scenarios and see the impact of changes.

Part 2 - Projection for Final exam grades needed (20 points):

Suppose, instead of having all the grades posted, Oliver has all his grades EXCEPT for the Final Exam. He is wondering what grade he needs on the Final to earn the overall grade he desires for the course. You will create formulas for what grades he would need to achieve an A, B, C, or D.

In words, the calculation Oliver needs is:

(Final grade * Final weight) + (Sum of other weighted pts earned) = (Desired grade) therefore

Final grade = [(Desired grade) - (Sum of other weighted pts earned)] / (Final weight)

Create formulas in cells E20 through E23 to calculate the grades he needs to achieve an A, B, C or D. You should use the appropriate values in cells you have already calculated in the grade book above.

Questions (20 Points): Enter answers in the text boxes on worksheet.

Question 1: Can Oliver get an A for the course? What grade would he require to get an A?

Question 2: Will Oliver pass the course? What is the lowest grade he could get and still pass?

Part 3 - GPA calculator (20 Points):

A student’s Grade Point Average (GPA) 1s the weighted average of the grades in each course weighted by the number of credit hours for each course. In this case, the weights do not add up to 100%, but instead add up to the total of the credit hours he has taken.

The Template has room for up to 20 course grades. You need to:

• Enter data (course title, credit hours, and grade points earned) for 5 or more courses beginning with row 28. These can be either real courses, or made up data. Include at least one 1-credit course, and the rest can be a mix of either 2, 3 or 4 credits. Do NOT use the same credit hours for all courses.
•  Insert a formula in each cell in Col E from E28 through E47 calculating the weighted grade points earned for each course (product of credit hours times grade). Copy your formulas to all rows so that you can easily add courses later.
• Insert formulas in cell C49 totaling the credit hours and cell E49 totaling the weighted grade points earned
• Insert a formula in cell E51 to calculate the GPA. The GPA is the total of the weighted grade points earned divided by the total of the weights (credit hours taken) — that is, it’s the weighted average of course grades.

Bonus — Reflections (5 Points):

In the yellow text box, share a few sentences of your thoughts about this assignment. These might include what you learned about weighted averages, how they may apply in real life situations, or things you learned about Excel from this Activity.