In Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights. SUMPRODUCT multiplies each value by its corresponding weight, and dividing by the total weight gives the weighted average.
What a weighted average tells you
A simple average gives every observation equal importance. A weighted average does not: it gives more influence to entries with larger weights. That makes it useful when values represent items with different quantities, importance, durations, or shares of a whole.
For example, a student’s final grade may combine assignments worth different percentages. A product’s average selling price should account for how many units sold at each price. A portfolio’s overall return, considered as a mathematical calculation rather than investment advice, can be weighted by the share assigned to each holding. In each case, the weight explains how much each value contributes.
The formula is a ratio: add the weighted values, then divide by the sum of the weights. If the weights already add up to 100%, the result is still a weighted average. If they add up to 1, the same principle applies. Dividing by the weight total makes the formula work with either convention and with weights that are not already normalized.
How SUMPRODUCT does the calculation
SUMPRODUCT multiplies corresponding items in one or more arrays and adds the resulting products. In a weighted average, each value is paired with the weight in the same row. The order matters: the first value must match the first weight, the second value the second weight, and so on.
Suppose four assessment scores are 80, 90, 70, and 100, and their weights are 20%, 50%, 20%, and 10%. The weighted contributions are 16, 45, 14, and 10. Their total is 85. Since the weights total 100%, dividing 85 by 100% returns 85. In Excel, percentages are stored as decimal values, so the formula divides the weighted sum by 1. If the weights are entered as 20, 50, 20, and 10 instead, both the weighted sum and weight total are 100 times larger, and the result remains 85.
With values in A2:A5 and percentage weights in B2:B5, use =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5). The formula returns 85 for the example above. In this particular case, because the weights total exactly 100%, =SUMPRODUCT(A2:A5,B2:B5) also returns 85. The version with the division is more reusable: it still returns the right average if someone changes the weights and they no longer total exactly 100%.
Set up the worksheet and enter the formula
Keep the values and their corresponding weights in separate columns, and use a clear heading for each. For example, put assignment names in column A, scores in column B, and weights in column C. The names help you check that each score is paired with the intended weight; the formula uses the numeric columns.
- Enter each value and its weight on the same row. Store percentage weights as percentages, such as 20%, or use a consistent scale such as 20, 50, and 30.
- Check that every value has the correct weight and that the selected ranges cover the same rows.
- Choose an empty cell for the result. With scores in B2:B5 and weights in C2:C5, enter
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). For the scores 80, 90, 70, and 100 with weights 20%, 50%, 20%, and 10%, it returns 85. - Format the result as a number, percentage, or currency according to what the values represent. In Excel for Microsoft 365 on Windows, use Home > Number to choose a number format. The same basic formula works in Excel for Mac and Excel for the web, though ribbon layout can vary.
Using descriptive headers and keeping the rows together makes the calculation easier to audit. If you insert another observation, expand both formula ranges to include its value and weight. For a growing table, converting the data to an Excel table can make references easier to maintain, but check that the formula includes the intended rows.
Choosing weights and checking the result
Weights should represent the relative influence you intend, not merely convenient numbers. If one component should count twice as much as another, weights of 2 and 1 express that relationship. The formula does not require weights to sum to a particular total, but weights should be on a consistent scale. Mixing 20% with 30, for instance, would make the calculation misleading.
A quick check is to compare the answer with the smallest and largest valid values. With nonnegative weights, the weighted average should fall between those values. If it falls outside that range, inspect the inputs, signs, and row pairings. Another useful check is to calculate the total weight in a separate cell with =SUM(C2:C5), assuming the weights are in C2:C5. That formula returns 100% for the example, or 1 when Excel displays percentages as decimals.
Weights do not have to be percentages. Quantities often make better weights: if prices are in A2:A4 and units sold are in B2:B4, the result is the average price per unit, weighted by sales volume. With prices of $10, $12, and $15 and quantities of 2, 3, and 5, the weighted total is $131 across 10 units, so the average is $13.10.
With prices in A2:A4 and quantities in B2:B4, enter =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). It returns 13.1, which can be formatted as currency. A simple average of the three prices would be about $12.33 and would treat each price as if the same number of units sold at it.
Common errors and how to avoid them
SUMPRODUCT is straightforward, but small data issues can change the result or cause an error. Check these points before assuming the function itself is at fault:
- Unequal range sizes: The value and weight ranges should have matching dimensions. Pairing five values with four weights can produce a calculation error.
- Misaligned rows: Sorting one column without the other breaks the pairings. Sort the entire data range so each value stays with its weight.
- A zero total weight: The denominator cannot be zero. Excel returns a division error if all included weights are zero or if positive and negative weights cancel out. For an ordinary average, weights are usually nonnegative and their sum should be greater than zero.
- Numbers stored as text: A value that looks numeric may be text, especially after importing data. Convert it to a number before relying on the calculation. Also check for error values such as #N/A in either range; those can propagate into the result.
- Blank or incomplete rows: A blank value paired with a weight should not automatically be treated as a meaningful zero. Decide whether that row should be excluded, and make sure its weight is not still counted in the denominator. The safest approach for a small dataset is to fix or remove incomplete rows, or to build the formula around a clearly defined inclusion rule.
- Mixed units or scales: Do not combine values expressed in different units, or weights entered partly as percentages and partly as whole numbers. Standardize the data first.
When values may contain text or blanks, do not assume the formula’s result reflects your intended policy for missing data. Decide whether missing entries should be excluded, treated as zero, or investigated. Those choices have different meanings, so a formula cannot choose correctly without that rule being specified.
Weighted average versus a simple average
Use AVERAGE when every value deserves equal influence. Use a weighted average when each observation has a different contribution, and you can justify the weights. For example, a plain average of monthly prices gives each month the same influence, while a volume-weighted average gives more influence to months with more units sold.
For values in A2:A5, the unweighted calculation is =AVERAGE(A2:A5). It returns the arithmetic mean without considering any separate weights. For the weighted result, with corresponding weights in B2:B5, use =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5). If all weights are identical, the weighted and simple averages are the same. If weights vary, they will usually differ.
Do not confuse a weighted average with a total. SUMPRODUCT by itself adds the weighted contributions; dividing by the sum of weights converts that total into an average. If the question is total revenue from prices and quantities, for example, the numerator may be the answer you want. If the question is average price per unit, divide that numerator by total quantity.
Calculate a weighted average for one category
You can also calculate a weighted average for rows that meet a condition, such as one product category or one department. Suppose A2:A10 contains categories, B2:B10 contains values, C2:C10 contains weights, and E2 contains the category to include. The condition creates a set of 1s for matching rows and 0s for other rows, so nonmatching rows contribute nothing to either the numerator or denominator.
With that layout, enter =SUMPRODUCT((A2:A10=E2)*B2:B10*C2:C10)/SUMPRODUCT((A2:A10=E2)*C2:C10). It returns the weighted average for the category named in E2. This formula requires a nonzero total weight among matching rows; if no rows match, the denominator is zero and Excel returns a division error. Use a separate message or check for that case if the category may not exist.
When you need several conditions, the same general idea can be extended by multiplying another true-or-false test into both SUMPRODUCT calculations. Keep the conditions, values, and weights aligned row by row, and test the formula on a small sample before using it across a larger report.
Frequently Asked Questions
Do the weights have to add up to 100%?
No. The formula divides by the sum of the weights, so weights such as 2, 3, and 5 work just as well as 20%, 30%, and 50%, provided the relative proportions are the same and the total weight is not zero.
Why is my SUMPRODUCT weighted average returning an error?
Check for ranges of different sizes, error values in the data, and a zero total weight. Also confirm that the formula’s denominator includes the intended weight cells and that numeric-looking entries are stored as numbers rather than text.
Can I use SUMPRODUCT when weights are percentages?
Yes. Excel stores percentages as decimal values, and the division by SUM of the weights accounts for their total. If percentages sum to 100%, that total is stored as 1.
What is the difference between SUMPRODUCT and AVERAGE?
AVERAGE gives each numeric value equal influence. SUMPRODUCT can multiply each value by a corresponding weight; dividing the product sum by the weight sum returns the weighted average.
Can SUMPRODUCT calculate a weighted average with a condition?
Yes. Include the condition in both the weighted-sum calculation and the weight-total calculation, as in the category example above. Check that at least one matching row has a nonzero weight.
Does this formula work in Excel for Mac or the web?
Yes. SUMPRODUCT and SUM are available in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The formula syntax is the same; the interface may look different across platforms.
Related
- Text to Excel Converter: Import Text Into Columns
- Excel SUM and Hidden Cells: What Gets Included in Totals?
- Excel SUM and Multiply: How to Combine Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Greater Than or Equal To: Formula and ExamplesIn Excel, the greater-than-or-equal-to operator is >=. Put it between two values in a formula, such as =A1>=B1,…
- Excel Formula for Name Arrangement: Sort Names A to ZFor Microsoft 365, use =SORT(A2:A20) to arrange a list of names alphabetically; replace the range with your own.…
- How to Round Numbers in Excel to the Nearest ThousandIf the value is in A2, enter =ROUND(A2,-3) to round it to the nearest thousand; for example, 1,234,567…
- Sort Excel Rows by Column Value: A Practical GuideTo sort rows in Excel by a column value, click a cell in the data, then choose Data…
- Excel ROUNDUP Examples: 10 Practical Ways to RoundExcel’s ROUNDUP function always rounds a number away from zero, which makes it useful when a decimal result…
- Excel Middle Column Extraction: Formulas and ExamplesTo extract the middle column of a range in Excel for Microsoft 365, enter =CHOOSECOLS(A2:E100,ROUNDUP(COLUMNS(A2:E100)/2,0)) in an empty…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.