How to Calculate a Weighted Average in Google Sheets
To calculate a weighted average in Google Sheets, you need to use the SUMPRODUCT function along with the SUM function. The weighted average is calculated by multiplying each value by its corresponding weight, and then dividing the sum of the products by the sum of the weights. Here's a step-by-step guide on how to calculate a weighted average in Google Sheets:
- Open your Google Sheets document or create a new one.
- Enter your data, including values and their corresponding weights, in separate columns. For example, you can put your values in column A and their weights in column B.
Example
Let's assume you have the following data:
| Value (A) | Weight (B) |
|---|---|
| 80 | 5 |
| 90 | 3 |
| 70 | 2 |
- In an empty cell where you want to display the weighted average, type the following formula:
=SUMPRODUCT(A2:A4, B2:B4) / SUM(B2:B4)
In this example, A2:A4 refers to the range of values, and B2:B4 refers to the range of weights.
- Press Enter to get the weighted average.
The weighted average should now be displayed in the cell where you entered the formula. In this example, the weighted average is 80.
Note: Make sure to replace the cell references in the formula with the appropriate references for your data.