# How to calculate a weighted average in Excel

Multiply each value by its weight, add them up and divide by the total weight: =SUMPRODUCT(values,weights)/SUM(weights).

**Excel:** `=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Assessment | Score | Weight |  | Final grade |
| Homework | 92 | 0.2 |  |  |
| Quizzes | 78 | 0.15 |  |  |
| Midterm | 84 | 0.25 |  |  |
| Project | 95 | 0.15 |  |  |
| Final exam | 81 | 0.25 |  |  |

Formula in F1: `=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)`

Result: 85.60

## How it works

SUMPRODUCT multiplies each score by its weight and adds the results. Dividing by the sum of the weights turns that into an average, and it still works when the weights don't add up to 100% or are counts like units sold.

## Other ways

- `=SUM(B2:B6*C2:C6)/SUM(C2:C6)`: The same calculation with an array multiplication; in Excel 2019 and older it needs Ctrl+Shift+Enter.

## Common mistakes

- AVERAGE(B2:B6) treats every score equally and gives 86, not the weighted 85.6.
- The values and weights ranges must be the same size, or SUMPRODUCT returns #VALUE!.

## Questions

**Do the weights have to add up to 100%?**

No. Dividing by SUM(weights) handles any total, such as weighting prices by units sold.

**How do I calculate a weighted average price?**

Use quantities as weights: `=SUMPRODUCT(prices,quantities)/SUM(quantities)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/weighted-average