The weighted average is a calculation that takes into account various degrees of importance of numbers in a data set. Such as, it can be used to calculate the overall score in a class or exam where different exams or assignments have different levels of importance.
In this article, we will go over the concept of weighted average and how to calculate it in two ways: by using the SUMPRODUCT and SUM functions in Excel.
Method 1: Calculate the Weighted Average with the SUM Function
Here are the steps to calculate the weighted average with the SUM function in Excel:
Step 1: Include the data in your excel sheet. Determine the importance or weight of each score. In this example, the weight of the viva exam is 100 percent, the weight of the pre-exam is 200 percent, and the final exam’s weight is 300 percent.
Step 2: Multiply each score by its corresponding weight. For example, the final exam score of 90 multiplied by a weight of 3 is 270.
Step 3: Sum up the resulting values from step 2. The sum of the products of the weighted score is 470.
Step 4: Divide the products’ sum by the weights’ sum. We can do this by using the SUM formula in this way =SUM(D2:D4)/SUM(C2:C4). Here, the D column contains the weighted scores, and the C column contains the weights.
Step 5: Press Enter to get the weighted average. Done!
Method 2: Using SUMPRODUCT Function
Follow the below steps to utilize the SUMPRODUCT function to calculate the score.
Step 1: We will need the same data (Score and Weight) to calculate the weighted average using the SUMPRODUCT function.
Step 2: Select a cell and enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Here, the B column contains weights, and the C column contains the weighted scores.
Step 3: Press Enter to get the weighted average.
Conclusion
By using either of the two methods, you can easily calculate the weighted average in Excel. You can also use this method for different types of calculations and data analysis. We hope this article has helped you understand the concept of the weighted average better, and we wish you luck with your future projects!
