Exceljet

Quick, clean, and to the point

Weighted average

Excel formula: Weighted average
Generic formula 
=SUMPRODUCT(weights,values)/SUM(weights)
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.

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.