In Google Sheets, there are two methods to calculate the weighted average.
- SUMPRODUCT + SUM
- AVERAGE.WEIGHTED
The first method is a formula that combines two functions, and the second is the pre-build function that you can use directly. And in this tutorial, we will learn about both methods in detail.
Weighted Average with SUMPRODUCT and SUM
In the below data, we need to calculate the weighted average for quantity.
- First, enter the SUMPRODUCT in a cell.
- After that, refer to the quantity column in the first argument, then to the price quantity column in the second argument, and close the function.
- Next, enter the forward slash to divide, and then enter the SUM function.
- In the end, refer to the price column, and close the function.
=SUMPRODUCT(A2:A12,B2:B12)/SUM(B2:B12)The moment you hit enter, it returns 133.90 in the result, which means the weighted average of quantity is 133.90.
How this Formula Works
To understand this formula, you need to split it into three parts.
In the first part, we used SUMPRODUCT, where we used quantity and price. And SUMPRODUCT, as its name suggests, multiply each quantity with the corresponding price, then sums all those values in as a single value.
In the second part, we have used SUM to sum the prices in as a single value.
And in the third part, you need to divide the value returned by the SUMPRODUCT by the values returned by the SUM.
In the backend, this formula works something like this:
= ((A2*B2) + (A3*B3) + (A4*B4) + (A5*B5) + (A6*B6) + (A7*B7) + (A8*B8) + (A9*B9) + (A10*B10) + (A11*B11) + (A12*B12))/ (B2 + B3 + B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11 + B12)
And, if you want to get a weighted average of price instead of the quantity, you need to sum quantities.
Using AVERAGE.WEIGHTED Function to Calculate the Weighted Average
In Google Sheets, there’s a specific function called AVERAGE.WEIGHTED that you can use to calculate the weighted average. This function is quite simple to use than the method we used earlier.
- First, enter the function in a cell.
- After that, in the first argument (values), refer to the quantity column.
- Next, the second argument (weights) refers to the price column.
- In the end, hit enter to get the result.
=AVERAGE.WEIGHTED(A2:A12,B2:B12)As I said, this function is easier to use.
And let’s say you want to get the weighted average of price instead of the quantity; you can swap the references. In the example below, we have used prices in the values argument and quantities in the weights.
