You can calculate the weighted average in many ways in Google Sheets. The easiest way is by using the AVERAGE.WEIGHTED function.
How does the weighted average differ from the average?
An average is the sum of the values in a dataset divided by the number of values. For example, if you have the heights of 10 students, you would total the heights and divide that number by 10. It is used to measure the central tendency of a set of values.
A weighted average, on the other hand, is different. In this case, each value in the dataset is multiplied by a weight, and then the weighted values are summed. Instead of dividing this sum by the count of values, you divide it by the sum of the weights to get the weighted average.
For example, consider the weighted average of the working hours of an employee who worked 8 hours per day for 5 days (Monday to Friday) and 5 hours on 1 day (Saturday).
To find the average of hours worked, you can sum the hours:
- 8+5=13, and divide that by 2 (the number of days). The result would be 6.5.
But to calculate the weighted average, you should multiply the hours by the number of days, like so:
- 5×8=40 (for the 5 days)
- 1×5=5 (for Saturday)
Then, you sum those totals:
- 40+5=45
Next, divide the sum by the total weight (the number of days):
- 45/6 = 7.5
So, in the calculation of a weighted average, the values with higher weights have a greater influence on the final average.
Below is how to calculate the weighted average using the AVERAGE.WEIGHTED function in Google Sheets:
AVERAGE.WEIGHTED Function: Syntax and Arguments
Syntax:
AVERAGE.WEIGHTED(values, weights, [additional_values], [additional_weights])Arguments:
- values: The range of cells containing the values to be averaged.
- weights: The corresponding range of cells containing the weights to apply. Weights should be greater than or equal to 0. Empty cells are not supported.
You can hardcode the values and weights instead of referencing ranges of cells. - additional_values: An optional parameter to specify additional values to average.
- additional_weights: The weights corresponding to the additional values.
Note:
The formula supports empty rows. That means you can use an open range when using the AVERAGE.WEIGHTED function. However, if a value is present, there must be a corresponding weight and vice versa; otherwise, the formula may return an error.
Example
Assume the following sample data in A1:B:
| Days Worked | Hours Worked per Day |
| 5 | 8 |
| 1 | 5 |
Let’s find the weighted average of hours worked using the formula:
=AVERAGE.WEIGHTED(B2:B, A2:A)The formula would return 7.5, which is equal to:
=((5*8)+(1*5))/(5+1)If you want to treat empty cells as 0 and avoid errors, wrap both ranges with the N function and enter the formula as an array formula:
=ArrayFormula(AVERAGE.WEIGHTED(N(B2:B), N(A2:A)))Additional Tip: Calculating Weighted Average Using SUMPRODUCT
In Excel, the AVERAGE.WEIGHTED function is not available at the time of this post. So if you switch between Excel and Sheets, you may find the following SUMPRODUCT and SUM combo formula useful to calculate the weighted average. This will work in both applications.
Generic Formula:
SUMPRODUCT(values, weights) / SUM(weights)Formula as per the sample data:
=SUMPRODUCT(B2:B, A2:A) / SUM(A2:A)
