قالب وردپرس درنا توس
Home / Tips and Tricks / How to calculate a weighted average in Excel

How to calculate a weighted average in Excel



  Microsoft Excel Logo

A weighted average is one that takes into account the weight or weight of each value. This article shows 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 a mean value that takes into account the weight, or weight, of each value. A good example would be to calculate a student's final grade based on their results on various different assignments and tests. Individual assignments are usually not counted as much to the final grade as the final exam ̵

1; eg quizzes, tests and final exams must 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 shows how to use Excel's SUMPRODUCT and SUM functions to calculate a weighted average. 19659006] Let's look at an example

For our example, let's look at a student's quiz and exam results. There are six quizzes each worth 5% of the total grade, two samples each worth 20% of the total grade, and a final test worth 30% of the total grade. The student's final grade will be a weighted average and we use the SUMPRODUCT and SUM functions to calculate it.

As you can see in the table below, we have already assigned the relative weights for each quiz and exam in D-column.

 Excel table showing points and weight assigned to multiple quizzes and exams

Step 1: Calculating SUMPRODUCT

Let's first look at how the SUMPRODUCT function works. Start by selecting the cell where you want the result to appear (in our example, it is cell D13). Then navigate to the "Formulas" menu, select the "Math & Trig" drop-down menu, scroll to the bottom and click on the "SUMPRODUCT" function.

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

The "Function arguments" window appears.

 Function arguments window

For the box "Array1", select the student's points. Here we select all cells with actual points in the C column.

 In the Array1 box, select the cells with the degrees

Next, use the "Array2" box to select the weights of the questions and the exams. For us, they are in the D column.

 In the Array2 box, select the cells with the weights

Click "OK" when done.

 Click OK in the Function arguments window

The SUMPRODUCT function multiplies each point by the corresponding weight and returns the sum of all these products.

 The Excel table now shows the SUMPRODUCT value

] Step two: Calculate SUM

Now let's look at how the SUM function works. Select the cell where you want the results to appear (in our example, it is cell D14). Then navigate to the "Formulas" menu, select the "Math & Trig" drop-down menu, scroll to the bottom and click on the "SUM" function.

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

The "Function arguments" window appears.

 Function arguments window

For the "Number1" box, select all weights. [19659005]   In the Number1 field, select the cells with the weights

Click "OK".

 Click OK in the Function arguments window

The SUM function will add all values ​​together.

 The Excel table now shows the SUM value

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 score and the weight of each score. Select the cell where the weighted average should go (for us who are cell D15) and then type the following formula in the function field.

  = SUMPRODUCT (C3: C11, D3: D11) / SUM (D3: D11) [19659037] select the weighted average cell and then enter the formula " width="650" height="385" src="/pagespeed_static/1.JiBnMqyl6S.gif" onload="pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);" onerror="this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);"/> 

Press "Enter" after writing the formula to see the weighted average.

 The table now shows the weighted average

And there you have it. It is a fairly simple example, but it is a good way to show how weighted averages work.


Source link