Compute Weighted Grades in Excel 2016

4 Parts:

Steps

Video: Compute Weighted Grades in Excel 2016

Questions and Answers

Comments

You're watching VisiHow. In this video, we'll show how to calculate weighted grade-point averages for a group of students.

1. 1
   We include students' names in the first column

   .

   Then, we have "Quiz 1" through "Quiz 3", "Midterm", and "Final". The quizzes are worth 10% of the final grade. The midterm is worth 30% of the final grade. The final is worth 40% of the final grade.

   

   Was this step helpful? Yes | No| I need help
2. 2
   To calculate the final grade, what we do is we press "="

   .

   We type in the formula: "SUMPRODUCT()" (we use parentheses).

   

   Was this step helpful? Yes | No| I need help
3. 3
   We highlight the first row of the student's grades

   .

   

   Was this step helpful? Yes | No| I need help
4. 4
   Then, we press ","

   .

   

   Was this step helpful? Yes | No| I need help
5. 5
   Then, we highlight the weighted numbers

   .

   

   Was this step helpful? Yes | No| I need help
6. 6
   It is important to make sure that the student's grades have a relative reference

   .

   When we fill down, the next row will be used to generate the formula numbers.

   

   Was this step helpful? Yes | No| I need help
7. 7
   For the weighted averages, we want to make sure that these are absolute references

   .

   So, we add "$" in front of the letter and the number to make them "$C$1" and "$G$1" in this video. Whenever we fill down, the formula will always refer to the "Weight" row. In this video, "SUMPRODUCT" will basically multiply 75 by 0.1, 35 by 0.1, etc., and then add the product of those numbers. In this case, it will be 7.5+3.5+3.5+etc.

   

   Was this step helpful? Yes | No| I need help
8. 8
   Once we get all of these numbers added up, we want to divide those by the total number of the weights

   .

   We'll use "SUM", add a parenthesis, and select the top section, adding in the dollar sign in front of the letters and the numbers to make sure that these are absolute references. We close the formula and press "Enter". In this video, the final grade for Billy Bob will be 58.

   

   Was this step helpful? Yes | No| I need help
9. 9
   We can fill the "Grade" column down

   .

   We do it by left-clicking at the bottom of the cell and simply dragging it down to get the final grades for both Suzy Q and Mike Holmes.

   

   Was this step helpful? Yes | No| I need help

This has been a tutorial on how to calculate weighted averages using Microsoft Excel. If you have any questions or comments, please leave them in the space below. Thanks for watching VisiHow!

If there is new student adding into the list can formula automatically do average?

How exactly we can automated the formula for new student entries in the list. Its different as new students do add up in every class

VisiHow QnA. This section is not written yet. Want to join in? Click EDIT to write this answer.

If you have problems with any of the steps in this article, please ask a question for more help, or post in the comments section below.

Comments