How to Calculate a Weighted Average in Excel | Using SUMPRODUCT to Calculate Weighted Average

In this video, we will teach you how to calculate Weighted Average in Excel.

To calculate weighted average, a predetermined weight is assigned to each number in the data set.

Over here you can see the marks a student has achieved during a semester, against each quiz, presentation, assignment and exam. In this scenario, each criteria is provided a weight. So, for example Quiz 1 here is worth Twenty Percent of the overall percentage. Therefore, in our calculation, we will have to consider the weights assigned against each criteria here to calculate the overall percentage at the end of the semester.

To calculate weighted average, each criteria is multiplied with its respective weight. The result for each criteria is then added up and divided by the sum of all the weights.

In Excel, this can be done by using the SUMPRODUCT and SUM function. Let’s type in the equals to sign followed by writing SUMPRODUCT and brackets open. Over here you can see that the sum product function multiplies multiple arrays, so for example over here we want to multiple the array of marks obtained against the array of weights assigned.

So, let’s select the entire range of marks obtained. Now let’s enter a comma and then select the range of weights assigned. This is essentially multiplying each mark obtained with the weight assigned for each criteria, and then addig up the result for all the five criterias here.

Let’s close the brackets and now we will enter the divided by sign. After that, let’s enter SUM followed by brackets open. Now let’s select all the weights. Over here we are basically dividing the output we get from the SUMPRODUCT function with the sum of all the weights in this data set.

Once we hit enter, excel will calculate the weighted average score over here.

That’s all!

❓💬 Is there any excel function you would like to know more about? Let us know in the comments below.

#HowTech #Excel
--------------------------------------------------------------------------------------------------------------
✅All our announcements are available here t.me/howtechtv
✅ Commercial questions [email protected]
✅ Instagram instagram.com/howtechprojects
✅ Twitter twitter.com/howtechprojects