You can use the following formula to calculate a weighted percentage in Google Sheets:
=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: Weighted Percentage in Google Sheets
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:
Click the Format as percent button to convert this decimal to a percentage:
The value will automatically be formatted as a percentage:
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 Google Sheets here.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Calculate Frequencies in Google Sheets
How to Calculate Cumulative Percentage in Google Sheets