Руководство Excel

How to Calculate a Weighted Average in Excel

A weighted average accounts for the relative importance of each value. Using SUMPRODUCT divided by SUM is the most robust way to perform this calculation in Excel.

Сделать с помощью ИИ

Пошагово

1
Prepare your data
List your values in one column (e.g., A2:A10) and their corresponding weights in an adjacent column (e.g., B2:B10).
2
Enter the formula
Click an empty cell and type '=SUMPRODUCT(A2:A10, B2:B10) / SUM(B2:B10)'.
3
Calculate the result
Press Enter to see the weighted average result in the selected cell.

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

Советы

Ensure your values range and weights range are the same size, or Excel will return a #VALUE! error.

Вопросы и ответы

Why use SUMPRODUCT instead of multiplying columns manually?

SUMPRODUCT handles arrays internally, saving you from creating an extra helper column for products.