How to work out a weighted average with SUMPRODUCT

A plain average treats every row the same. A weighted average lets some rows count for more, here marks weighted by their credits.

The formula

=SUMPRODUCT(B:B,C:C)/SUM(C:C) in column E

What each part does

SUMPRODUCT(B:B,C:C): Multiplies each value by its weight and adds them all up.
SUM(C:C): Total of the weights.

How it works, and what to watch for

SUMPRODUCT multiplies each mark by its credits and adds them all in one step. Dividing by the total credits turns that into the weighted average.

Both ranges must line up row for row. If one is longer than the other, SUMPRODUCT returns #VALUE!.

This is the standard way to average grades, prices, or scores when the rows do not carry equal weight.

Questions

How is this different from a normal average?

A normal average adds the values and divides by how many there are. A weighted one lets each value pull harder or softer, according to its weight.

Why the #VALUE! error?

The two ranges are different lengths. SUMPRODUCT needs them to cover exactly the same rows.

The example sheet

Module	Mark	Credits
Math	72	30
History	65	15
Physics	80	45

If this helped, these are close by

Build a running total — Where the dollar signs come in
Count cells greater than a number — A single count of the rows above the limit
Each row as a share of the total — Percentages that add to 100

When this formula goes wrong

Excel formula returns #DIV/0! and how to hide it — Dividing by an empty or zero cell

More on SUMPRODUCT, and every walkthrough that uses it.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.