Summary

To calculated a weighted average, you can use the SUMPRODUCT function together with the SUM function. In the example shown, the formula in G5, copied down, is:

=SUMPRODUCT(weights,C5:E5)/SUM(weights)

where weights is the named range I5:K5.

Generic formula

=SUMPRODUCT(weights,values)/SUM(weights)

Explanation 

A weighted average, also called a weighted mean, is an average where some values count more than others. In other words, some values have more "weight". We can calculate a weighted average by multiplying the values to average by corresponding weights, then dividing the sum of results by the sum of weights. In Excel, this can be represented with the generic formula below, where weights and values are cell ranges:

=SUMPRODUCT(weights,values)/SUM(weights)

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:

=SUMPRODUCT(weights,C5:E5)/SUM(weights)

Working from the inside out, we first use the SUMPRODUCT function to multiply weights by corresponding scores and sum the result:

=SUMPRODUCT(weights,C5:E5) // returns  88.25

SUMPRODUCT first multiplies corresponding elements of the two arrays together, then returns the sum of the product:

=SUMPRODUCT({0.25,0.25,0.5},{90,83,90})
=SUMPRODUCT({22.5,20.75,45})
=88.25

The result is then divided by the sum of the weights:

=88.25/SUM(weights)
=88.25/SUM({0.25,0.25,0.5})
=88.25/1
=88.25

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:

=AVERAGE(C5:E5)

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:

Excel weighted average with custom weights

In cell G5, the formula is solved like this:

=SUMPRODUCT(weights,C5:E5)/SUM(weights)
=SUMPRODUCT({1,1,2},{90,83,90})/SUM(1,1,2)
=SUMPRODUCT({90,83,180})/SUM(1,1,2)
=353/4
=88.25

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:

Excel weighted average with TRANSPOSE

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:

=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)

After TRANSPOSE runs, the vertical array:

=TRANSPOSE({0.25;0.25;0.5}) // vertical array

becomes:

={0.25,0.25,0.5} // horizontal array

And from this point, the formula behaves as before.

Read more: vertical and horizontal arrays.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.