How to Calculate a Weighted Average in Excel - WindowsTips.net - Windows Tips and Tricks with Geek

Thursday, February 10, 2022

How to Calculate a Weighted Average in Excel

 Microsoft Excel Logo

What is a Weighted Average?

A weighted average is an average that takes into account the importance, or weight, of each value.  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 towards a final grade as the final exam—things like quizzes, tests, and final exams will all have different weights. 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.

The following example will demonstrate how to use Excel’s SUMPRODUCT and SUM functions to calculate a weighted average.

Let’s Look at an Example

For our example, let’s look at a student’s quiz and exam 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.  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’ve already assigned the relative weights to each quiz and exam in the D column.

Excel table showing scores and weights assigned to several quizzes and exams

Step One: Calculate the SUMPRODUCT

First, let’s look at 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 “Formulas” menu, select the “Math & Trig” drop-down, scroll to the bottom, and click on the “SUMPRODUCT” function.

On the Formulas tab, click Math & Trig, then select SUMPRODUCT

The “Function Arguments” window will appear.

the Function Arguments window

For the “Array1” box, select the student’s scores. Here, we’re selecting all the cells with actual scores in the C column.

In the Array1 box, select the cells with the grades

Next, use the “Array2” box to select the weights of the quizzes and exams. For us, those are in the D column.

In the Array2 box, select the cells with the weights

Click “OK” when you’re done.

Click OK in the Function Arguments window

The SUMPRODUCT function will multiply each score by its corresponding weight and then return the sum of all of those products.

The Excel table now shows the SUMPRODUCT value

Step Two: Calculate the SUM

Now let’s look at 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 “Formulas” menu, select the “Math & Trig” drop-down, scroll to the bottom, and click on the “SUM” function.

On the Formulas tab, click Math & Trig, then select SUM

The “Function Arguments” window will appear.

The Function Arguments window

For the “Number1” box, select all of the weights.

In the Number1 box, select the cells with the weights

Click “OK.”

click OK in the Function Arguments window

The SUM function will add all of the values together.

The Excel table now shows the SUM value

Step Three: Combine the 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 that’s cell D15) and then type the following formula into the function bar.

=SUMPRODUCT(C3:C11,D3:D11)/SUM(D3:D11)

select the weighted average cell and then type the formula

Press “Enter” after typing the formula to view the weighted average.

The table now shows the weighted average

And there you have it. It’s a fairly simple example, but it’s a good one for showing how weighted averages work.

No comments:

Post a Comment