A weighted average is one that takes into account the relevance or weight of each value. This post will show you how to use Excel's SUMPRODUCT and SUM functions individually and how to combine the two to calculate a weighted average..
What is a weighted average?
A weighted average is an average that takes into account the relevance or weight of each value. A good example would be calculating a student's final grade based on their performance on a range of different assignments and tests.. In general, individual assignments do not count as much towards the final grade as the final exam; things like quizzes, tests and final exams will have a different weight. The weighted average is calculated as the sum of all the values multiplied by their weights divided by the sum of all the weights.
The following example will demonstrate how to use the SUMPRODUCT and SUM functions in Excel to calculate a weighted average.
Let's see an example
For our example, Let's look at a student's test and quiz scores. There are six questionnaires each with a value of 5% of the total rating, two exams with a value of 20% of the total grade and a final exam with a value of 30% of the total rating. 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 from our table below, we have already assigned the weights relative to each test and exam in column D.
Step one: Calculate the SUMPRODUCT
First, let's see how the SUMPRODUCT function works. Start by selecting the cell where you want the result to appear (in our example, that's cell D13). Next, navigate to the menu “Formulas”, select drop down menu “Mathematics and activation”, scroll to the bottom and click on the function “SUMPRODUCTO”.
The window will appear “Function arguments”.
for the painting “Array1”, select student scores. Here, we select all cells with real scores in column C.
Next, use the box “Array2” to choose the weights of tests and exams. For us, those are in column D.
Click on “To accept” When it's over.
The SUMPRODUCT function will multiply each score by its respective 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, that's cell D14). Next, navigate to the menu “Formulas”, select drop down menu “Mathematics and activation”, scroll to the bottom and click on the function “SUM”.
The window will appear “Function arguments”.
for the painting “Number 1”, select all weights.
Click OK.”
The SUM function will sum all the values.
Step three: combine SUMPRODUCT and SUM to calculate weighted average
Now we can combine the two functions to establish the final grade of the student based on their scores and the weights of each score. Select the cell where the weighted average should go (for us that is cell D15) and then type the next formula in the function bar.
=SUMPRODUCT(C3:C11,D3:D11)/SUM(D3:D11)
Press “Enter” after writing the formula to see the weighted average.
And there you have it. It's a pretty simple example, but it's good to show how weighted averages work.
