Quick Answer & Formula
Excel
Sheets
Beginner
=SUMPRODUCT(values_range, weights_range) / SUM(weights_range) To calculate a weighted average, multiply values by their corresponding weights using SUMPRODUCT, then divide by the total sum of weights: =SUMPRODUCT(Values, Weights) / SUM(Weights).
How to Calculate Weighted Average in Excel (SUMPRODUCT Method)
A standard simple average (AVERAGE) assumes all items have equal importance. When items have different quantities, share weights, or credit hours, you must calculate a Weighted Average.
1. The Core Formula
$$\text{Weighted Average} = \frac{\sum (\text{Value}_i \times \text{Weight}_i)}{\sum \text{Weight}_i}$$
=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)2. Investment Portfolio Return Example
| Asset Class (Col A) | Return Rate % (Col B) | Portfolio Allocation $ (Col C) | Value * Weight |
|---|---|---|---|
| US Equities (S&P 500) | 10.5% | $600,000 | $63,000 |
| International Stocks | 6.2% | $200,000 | $12,400 |
| Real Estate (REITs) | 8.0% | $100,000 | $8,000 |
| Treasury Bonds | 4.2% | $100,000 | $4,200 |
| Total | $1,000,000 |
Calculations:
- Simple Average (
=AVERAGE(B2:B5)):7.23%(Misleading because it ignores portfolio weights) - True Weighted Return:
Returns:=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)8.76%(Accurately reflecting the 60% heavy equity weighting).
?
Frequently Asked Questions
Why can't I just use the AVERAGE function?
AVERAGE treats every item equally. In real life, larger investments or higher-credit courses have more impact on the total outcome. Weighted average accounts for relative importance/size.