How to Calculate a Weighted Average in Excel Using SUMPRODUCT to Calculate Weighted Average
FBTwitterReddit
video description
Rating: 4.0; Vote: 1
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. Lets 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, lets select the entire range of marks obtained. Now lets 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.
Lets close the brackets and now we will enter the divided by sign. After that, lets enter SUM followed by brackets open. Now lets 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
Poonam: Wanted to know about let say there are 5 columns ( revenue, copies sold, count employee, user review, daily active user) and 15%, 20%, 10%, 25%, 30% has been assigned from 100% how can we calculate the overall weightage against each row of these columns Date: 2023-07-08
