Table of Contents
In Excel, you can calculate a weighted percentage by first multiplying the relative weight of each category to its corresponding value and then summing up the results. For example, if a student had a grade of 80 in one category and a grade of 90 in another, and the relative weights of the two categories were 0.4 and 0.6 respectively, you would calculate the weighted percentage by multiplying 0.4 by 80 and 0.6 by 90 and then summing the results. The result would be the weighted percentage.
You can use the following formula to calculate a weighted percentage in Excel:
=SUMPRODUCT(A:A, B:B)/SUM(B:B)
This formula assumes column A contains the percentage values and column B contains the weights.
The following example shows how to use this formula in practice.
Example: Calculate Weighted Percentage in Excel
Suppose we have the following dataset that shows the scores that some student received on various exams along with the weights for each exam:
We can use the following formula to calculate a weighted percentage for their final grade in the class:
=SUMPRODUCT(B2:B6, C2:C6)/SUM(C2:C6)
The following screenshot shows how to use this formula:
The final grade turns out to be 85%.
We can verify that this is correct by manually calculating the weighted percentage of grades:
- Weighted Percentage: (90%*10%) + (91%*10%) + (81%*10%) + (78%*10%) + (85%*60%)
- Weighted Percentage: 9% + 9.1% + 8.1% + 7.8% + 51%
- Weighted Percentage = 85%.
This weighted percentage matches the value that we calculated using the formula from earlier.
Note: You can find the complete documentation for the SUMPRODUCT function in Excel .