Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesTo calculate a weighted average, multiply each value by its matching weight, add those products, then divide by the sum of the weights. In symbols: weighted average = Σ(value × weight) ÷ Σ(weight). You can do that arithmetic by hand or use either of two Excel formulas.
What the weighted-average formula means
A weighted average gives values different levels of influence. Each value is paired with a weight, and larger weights contribute more to the result. The weight for the first value must stay paired with that value, the second weight with the second value, and so on.
For values x and weights w, the formula is:
Weighted average = (x₁w₁ + x₂w₂ + … + xₙwₙ) ÷ (w₁ + w₂ + … + wₙ)
If the weights already add to 1—or to 100% when expressed as percentages—the denominator is 1 (or 100%), so the weighted average is the sum of the value-weight products using the matching weight scale. Dividing by the total weight also works when weights are counts, quantities, or percentages that do not add to 100%.
#1 Best Overall
Way 1: Expand the arithmetic
For a short list, write out each value multiplied by its corresponding weight. Add those products, then divide by the total of the weights.
Suppose scores of 80 and 90 have weights of 2 and 3:
Rank #2
- Ideal for Gifting
- Ideal for a bookworm
- Compact for travelling
(80 × 2 + 90 × 3) ÷ (2 + 3) = 430 ÷ 5 = 86
The score of 90 has greater influence because its weight is 3 rather than 2. For a few values, this method is easy to inspect with a basic calculator or on paper.
Ways 2 and 3: Calculate a weighted average in Excel
Suppose values are in C5:C9 and their corresponding weights are in D5:D9. Keep the two ranges the same length and in the same order. Both formulas below calculate the sum of the paired products divided by the sum of the weights.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallWay 2: Use SUM with an array expression
Enter:
=SUM(C5:C9*D5:D9)/SUM(D5:D9)
The expression C5:C9*D5:D9 multiplies corresponding entries; SUM adds those products. The second SUM totals the weights. ExcelDemy says this formula can be entered normally in Office 365 and Excel 2021, while earlier versions may require Ctrl+Shift+Enter; check the behavior for your Excel version. ExcelDemy’s weighted-average Excel tutorial describes the method.
Way 3: Use SUMPRODUCT divided by SUM
Enter:
=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)
This is a concise way to express “multiply each matching pair and add,” followed by normalization by the total weight. Microsoft defines SUMPRODUCT as returning “the sum of the products of corresponding ranges or arrays.”
Rank #4
| Method | Best fit | Practical trade-off |
|---|---|---|
| Expanded arithmetic | A few values, calculated by hand or checked line by line | Transparent, but cumbersome for long lists |
SUM(range*range)/SUM(weights) |
A spreadsheet formula using array multiplication | Compact; entry behavior can vary by Excel version |
SUMPRODUCT(values,weights)/SUM(weights) |
A compact formula for paired ranges | Directly represents paired multiplication and summing; ranges must have matching dimensions |
When the values and weights are aligned and the formulas are entered correctly, all three methods give the same result.
Check the weights and Excel ranges
- Keep pairs aligned. A weight applied to the wrong value changes the result.
- Divide by total weight. Leave out
/SUM(weights)only when the weights are known to total 1 in decimal form (or 100% on a percentage scale). - Match the dimensions. Microsoft says SUMPRODUCT array arguments must have the same dimensions; otherwise Excel returns
#VALUE!. - Check for text or blanks. Microsoft says SUMPRODUCT treats nonnumeric array entries as zero, which can affect an unexpected result.
- Use bounded references. Microsoft cautions against full-column references in SUMPRODUCT for performance: Excel may process all 1,048,576 rows in each referenced column. A defined range or table reference avoids needlessly processing entire columns.
When a weighted average is useful
Use a weighted average when the values do not contribute equally. For example, a price average weighted by quantities gives greater influence to prices associated with more units. A grade calculation can likewise reflect different weights for assessments. When each item is equally important, the ordinary mean is the equal-weight case.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Quick Recap
Best Value
- It can be a gift option
- Comes with secure packaging
- Helpful in various ways
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
