where weights is the named range I5:K5. In the worksheet shown, scores for 3 tests appear in columns C through E, and weights are in the named range weights (I5:K5). The formula in cell G5 is: Working from the inside out, we first use the SUMPRODUCT function to multiply weights by corresponding scores and sum the result: SUMPRODUCT first multiplies corresponding elements of the two arrays together, then returns the sum of the product: The result is then divided by the sum of the weights: As the formula is copied down column G, the named range weights I5:K5 does not change, since it behaves like an absolute reference. However, the scores in C5:E5, entered as a relative reference, update in each new row. The result is a weighted average for each name in the list as shown. The average in column F is calculated for reference only with the AVERAGE function:
Weights that do not sum to 1
In this example, the weights are configured to add up to 1, so the divisor is always 1, and the result is the value returned by SUMPRODUCT. However, a nice feature of the formula is that the weights don’t need to add up to 1. For example, we could use a weight of 1 for the first two tests and a weight of 2 for the final (since the final is twice as important) and the weighted average will be the same:
In cell G5, the formula is solved like this: Note: the values in curly braces {} above are ranges expressed as arrays.
Transposing weights
The SUMPRODUCT function requires that array dimensions be compatible. If dimensions are not compatible, SUMPRODUCT will return a #VALUE error. In the example below, the weights are the same as the original example, but they are listed in a vertical range:
To calculate a weighted average with the same formula, we need to “flip” the weights into a horizontal array with the TRANSPOSE function like this: After TRANSPOSE runs, the vertical array: becomes: And from this point, the formula behaves as before. Read more: vertical and horizontal arrays.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
