There is a dedicated function to calculate the weighted average in Google Sheets. It’s AVERAGE.WEIGHTED. I think this function is not much popular. You know why?
The function SUMPRODUCT is very popular and already familiar to Google Sheets users. It can be used in many real-life examples and the weighted average is no exception.
From my point of view, this may be the reason for the less popularity of the AVERAGE.WEIGHTED function.
So in this tutorial, I am trying to explain to you what is weighted average calculation and how can you do it with two different functions – SUMPRODUCT and WEIGHTED.AVERAGE. You can use either of the ones but I recommend the latter.
Weighted Average in Google Sheets
What does the term weighted average means?
I am not taking you to any definition of weighted average. Here is a simple example to make you understand what is the weighted average aka weighted mean.
As you may probably know you can calculate the average by summing the values and divide it by the count.
Note: In spreadsheets, you can use the AVERAGE function to find the average.
But in weighted average before summing the values it will be multiplied by the corresponding weight.
Here is one example that can well explain this. After that, we can move to how to calculate the weighted average in Google Sheets using functions.
Example to Weighted Average
One person has worked 8 hours per day for 5 days and 6 hours per day for 3 days. Let’s see how to calculate his weighted average of working hours.
The manual calculation of the weighted mean would be like this.
(5*8+3*6)/(5+3) = 7.25
Now here are the formulas that you can use to simply calculate the weighted average in Google Sheets.
Formulas to Calculate the Weighted Average in Google Sheets
As I have mentioned in the beginning you can use either the dedicated AVERAGE.WEIGHTED function or the SUMPRODUCT alternative.
Weighted Average Calculation using the Sumporduct in Google Sheets
Formula:
=sumproduct(A2:A3,B2:B3)/sum(A2:A3)
Result: 7.25
This is the normal approach. Now let me explain how to use the function AVERAGE.WEIGHTED in Google Sheets.
Weighted Average Calculation using the Average.Weighted Function
Syntax:
AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])
In the AVERAGE.WEIGHTED function, you can use multiple sets of values and corresponding multiple sets of weights.
Formula:
=AVERAGE.WEIGHTED(B2:B3,A2:A3)
Result: 7.25
In this formula, cell B2: B3 (hours) contains the values and cell A2: A3 (days) contains the weight.
Additional Tips:
When you use the function AVERAGE.WEIGHTED, do fill all the cells with values. If any cell is left blank, the formula would return an error. So at least put 0 in blank cells. But the function SUMPRODUCT has no such usage barrier.
