values - Required. Range of values to be weighted (e.g., test scores, prices, returns)
weights - Required. Range of importance factors (e.g., percentages, quantities, credit hours)
| A | B | C | |
|---|---|---|---|
| 1 | Item | Score | Weight |
| 2 | Homework | 80 | 20% |
| 3 | Midterm | 90 | 30% |
| 4 | Final | 85 | 50% |
| 5 | Weighted Avg | =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) 86.5 |
Values in A1:A3, weights in B1:B3
Weighted mean of all values
When weights sum to 100% or 1.0
Direct weighted result
Returns weighted by investment amounts
Overall portfolio return %
Average price weighted by quantity purchased
True average price per unit
Calculate student final grade where different assignment categories have different weights. The weighted average formula in Excel handles multi-tier grading where homework counts 20%, midterm exam 30%, and final exam 50% of the total grade. This weighted mean calculation is essential for teachers, professors, and academic administrators managing course grading policies, ensuring fair assessment across different assignment types, and maintaining transparent grading standards that accurately reflect student performance based on institutional weighting policies.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Score | Weight |
| 2 | Homework | 92 | 0.20 |
| 3 | Midterm | 85 | 0.30 |
| 4 | Final Exam | 88 | 0.50 |
| 5 | Final Grade | =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) 88.4 |
Calculate overall portfolio return when different investments have different amounts invested. The Excel weighted average function accurately computes portfolio performance by weighting each investment return by its dollar amount, providing true portfolio ROI that accounts for position size. This weighted mean formula is critical for financial analysts, portfolio managers, and investors tracking multi-asset performance, making informed rebalancing decisions, and reporting accurate returns to stakeholders based on actual capital allocation.
| A | B | C | |
|---|---|---|---|
| 1 | Investment | Return % | Amount |
| 2 | Stock A | 12% | $50,000 |
| 3 | Stock B | 8% | $30,000 |
| 4 | Stock C | -5% | $20,000 |
| 5 | Portfolio Return | =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) 7.4% |
❌ The Problem:
✅ Solution:
=SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3)Use SUMPRODUCT weighted average formula to properly account for each value's importance. The weighted mean formula multiplies each value by its weight, ensuring accurate calculations for grades, portfolio returns, and any scenario where not all values should count equally in the final average.
❌ The Problem:
✅ Solution:
=SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3)Always divide by SUM(weights) to normalize the result back to average scale. The Excel weighted average formula requires this division to convert the weighted sum into a proper weighted mean that can be compared to the original values and used in further analysis.
❌ The Problem:
✅ Solution:
Weights: 20%, 30%, 50% (Total: 100%)Verify weights sum to 100% (or 1.0 for decimals) before calculating weighted average in Excel. While the SUMPRODUCT formula will still work mathematically, checking weight totals catches data entry errors, missing categories, and ensures your weighted mean reflects the complete picture for accurate business decisions.
Master these Weighted Average variants:
From basics to advanced - AI generates perfect formulas instantly.