A weighted average calculation takes into account some numbers should influence the average more than others. I typically find myself needing this calculation when working with data that is already summarized in a table.
Two-Variable Weighted Average
Here’s a simple example of a summarized table where we have a list of prices and the quantity of items that fall within each bucket.
=SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)The formula utilizes the SUMPRODUCT function which first, multiplies each value in the same row, and then secondly, adds up each product (hence, sum-product). You then divide by the SUM of the non-target array. In this case, because I wanted the weighted average price, I took the sum of the quantity column in the denominator.
You’ll notice I have the Straight Average Price as well to illustrate that the weighted average takes into account the fact that my data set does not have equal distribution of prices. The weighted average is below the straight average because of the heavy concentration of items in $1.50 bucket.
Three-Variable Weighted Average
When I worked in investment banking, I always had to find the duration weighted yield which took into account three arrays. The amount in each yield bucket and the duration of each bond both influence the yield of the entire deal. In this case, you’ll use the SUMPRODUCT in the numerator and in the denominator. Leave out the target array, the yield in my example, from the denominator SUMPRODUCT formula.
=SUMPRODUCT(A2:A6,B2:B6,C2:C6)/SUMPRODUCT(B2:B6,C2:C6)