How To Calculate A Weighted Average In Excel
The significance or weight of each individual number is taken into account when calculating a weighted average. Excel's SUMPRODUCT and SUM functions can be used individually or in combination to calculate a weighted average, as demonstrated in this article.
What is a Weighted Average?
The significance or weight of each individual number is taken into account when calculating a weighted average. For instance, a student's final grade could be dependent on how well they did on a variety of assignments and exams. On many cases, individual assignments carry a smaller weight in the final grade than the final exam does. Quizzes, tests, and exams all have different weights. The weighted average is calculated by multiplying all of the values by their weights and dividing the result by the total weights.
Excel's SUMPRODUCT and SUM functions can be used to create a weighted average in the following example.
Take the Following Illustration as an Example
An example of a student's quiz and exam results can be found. There are six tests, each worth 5% of the final grade, two exams, each worth 20% of the final grade, and a final exam, for 30% of the final grade. Final grades for students will be based on a weighted average calculated using SUMPRODUCT and SUM functions.
Column D in the table below shows the relative weights assigned to each quiz and test.
Step 1: Calculate the SUMPRODUCT
The SUMPRODUCT function will be examined first. Decide on the intended output by first picking the appropriate cell (in our case, cell D13). Once you've selected the "Math & Trig" option, scroll down and select "SUMPRODUCT."
The "Function Arguments" window will pop up now.
Select the student's grades in the "Array1" box. Column C has all of the real score cells, and we're selecting them all here.
Now select the weights of the quizzes and exams in "Array2" from the drop down menu. The D column is where we'll be putting these things.
Then click "OK" to close the window.
Using the SUMPRODUCT command, you may multiply each score by its corresponding weight and then get the sum of all the products.
Step 2: Calculate the Sum
Let's take a closer look at the SUM function. Cell D14 in our example is where we want the results to appear. In the "Math & Trig" drop-down menu, select "SUM," scroll down to the bottom, and click it.
The "Function Arguments" window will pop up now.
All of the weights in the "Number1" box should be checked off.
Click "OK"
Each value will be added to the total when using the SUM command.
Step 3: Combine the SUM PRODUCT and SUM to Calculate the Weighted Averge
To determine a student's final grade, we may now combine the two functions. Enter the following formula into the function bar after selecting the cell where the weighted average should appear (in our case, cell D15).
=SUMPRODUCT(C3:C11,D3:D11)/SUM(D3:D11)
To see the resulting weighted average, simply press the "Enter" key after entering the formula.
In the end, this is the result. Weighted averages can be explained using this simple example.
