The SUMPRODUCT function will multiply each score by its corresponding weight and then return the sum of all of those products. Next, use the “Array2” box to select the weights of the quizzes and exams. Here, we’re selecting all the cells with actual scores in the C column. The student’s final grade will be a weighted average, and we will use the SUMPRODUCT and SUM functions to calculate it.Īs you can see in our table below, we’ve already assigned the relative weights to each quiz and exam in the D column.įor the “Array1” box, select the student’s scores. There are six quizzes each worth 5% of the total grade, two exams each worth 20% of the total grade, and one final exam worth 30% of the total grade. Let’s Look at an Exampleįor our example, let’s look at a student’s quiz and exam scores.
The following example will demonstrate how to use Excel’s SUMPRODUCT and SUM functions to calculate a weighted average. The weighted average is calculated as the sum of all of the values multiplied by their weights divided by the sum of all of the weights. Individual assignments usually don’t count as much towards a final grade as the final exam-things like quizzes, tests, and final exams will all have different weights. A good example would be calculating a student’s final grade based on their performance on a variety of different assignments and tests. What is a Weighted Average?Ī weighted average is an average that takes into account the importance, or weight, of each value. This article will show you how to use Excel’s SUMPRODUCT and SUM functions individually and how to combine the two to calculate a weighted average. A weighted average is one that takes into account the importance, or weight, of each value.