A weighted average is one that considers 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 them to calculate a weighted average
A good example would be calculating a student’s final grade based on their performance on a variety of different assignments and tests
Individual assignments usually don’t count as much toward a final grade as the final exam—things like quizzes, quizzes, and final exams will all have different weights
The weighted average is calculated as the sum of all values multiplied by their weights divided by the sum of all weights
The following example will demonstrate how to use Excel’s SUMPRODUCT and SUM functions to calculate a weighted average
For our example, let’s look at a student’s test and exam scores
The student’s final grade will be a weighted average and we will use the SUMPRODUCT and SUM functions to calculate it
As you can see in our table below, we have already assigned relative weights to each test and exam in column D
Step One: Calculate the SUM PRODUCT
First, let’s see how the SUMPRODUCT function works
Start by selecting the cell where you want the result to appear (in our example, this is cell D13)
Next, navigate to the “Formulas” menu, select the “Math & Trig” drop-down menu, scroll to the bottom and click on the “SUMPRODUCT” function
For the “Matrix1” box, select student scores
Here, we select all cells with actual scores in column C
Next, use the “Matrix2” box to select test and exam weights
The SUMPRODUS function will multiply each score by the corresponding weight and then return the sum of all those products
Step Two: Calculate the AMOUNT
Now let’s see how the SUM function works
Select the cell where you want the results to appear (in our example, this is cell D14)
Next, navigate to the “Formulas” menu, select the “Math & Trig” drop-down menu, scroll to the bottom and click on the “SUM” function
For the “Number1” box, select all weights
The SUM function will add all the values together
Step Three: Combine SUMPRODUCT and SUM to calculate the weighted average
Now we can combine the two functions to determine the student’s final grade based on their scores and the weights of each score
Select the cell where the weighted average should go (for us, this is cell D15) and then enter the following formula in the function bar
=SUMPRODUCT(C3:C11;D3:D11)/SUM(D3:D11)
It’s a fairly simple example, but it’s a good one to show how weighted averages work