The quickest way to find a weighted average in Excel
A weighted average is an average where some numbers count more than others. In Excel, the fastest method is the SUMPRODUCT function. If you have values in column A and their weights in column B, the formula is:
=SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)
This multiplies each value by its weight, adds them all up, then divides by the sum of the weights. Replace A2:A10 and B2:B10 with your actual cell ranges. The result appears in whichever cell you type the formula into.
Key Takeaways
- SUMPRODUCT multiplies values by weights and adds the results, then you divide by the total weight to get the weighted average.
- You need two columns: one for the values you are averaging, and one for how much weight each value carries.
- The weights do not have to add up to 100 or any specific number — Excel handles the math automatically.
- If your data includes headers in row 1, start your range at row 2 (A2:A10 instead of A1:A10) so Excel does not try to do math on text.
Setting up your data the right way
Before you write the formula, arrange your spreadsheet so values and weights sit in separate columns. Put the numbers you want to average in one column — for example, test scores in column A. Put the weight for each score in the next column — for example, how many points that test is worth in column B.
If you have a header row (like "Score" in A1 and "Weight" in B1), leave it there. Just make sure your formula starts at row 2, not row 1. A formula that includes text will return an error or a zero.
The weights can be whole numbers, decimals, or percentages. They do not need to add up to 100. If you have three test scores weighted 30, 40, and 30 points, or weighted 0.3, 0.4, and 0.3, the formula works the same way.
Understanding what SUMPRODUCT actually does
SUMPRODUCT takes two lists and multiplies them pair by pair, then adds all the results. If your values are 80, 90, and 85, and your weights are 2, 3, and 1, SUMPRODUCT calculates (80×2) + (90×3) + (85×1) = 160 + 270 + 85 = 515.
Then you divide that total by the sum of the weights: 515 ÷ (2+3+1) = 515 ÷ 6 = 85.83. That is your weighted average. The SUMPRODUCT function handles all the multiplication and addition in one step, which is why it is faster than building the formula piece by piece.
An example with real numbers
Say you are tracking student grades. A student has three test scores: 78, 85, and 92. The first test is worth 20 percent of the grade, the second is worth 30 percent, and the third is worth 50 percent. Set it up like this:
| Score | Weight |
| 78 | 0.2 |
| 85 | 0.3 |
| 92 | 0.5 |
Put the formula =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) in a cell below. Excel calculates (78×0.2) + (85×0.3) + (92×0.5) = 15.6 + 25.5 + 46 = 87.1. That is the weighted average. If the weights were 20, 30, and 50 instead of decimals, the result would be the same: 87.1.
Using AVERAGE.WEIGHTED if your version of Excel has it
Some newer versions of Excel (particularly Excel 365) include a function called AVERAGE.WEIGHTED that does the same thing in one step. The syntax is =AVERAGE.WEIGHTED(values, weights). If this function is available in your version, it is slightly simpler than SUMPRODUCT, but SUMPRODUCT works in all versions of Excel and produces the same answer.
To check if you have AVERAGE.WEIGHTED, type it into a cell and see if Excel recognizes it. If you get an error, use SUMPRODUCT instead. Both are correct; SUMPRODUCT is just more widely available.
Common mistakes to avoid
The most common error is including the header row in your formula range. If your headers are in row 1 and you type =SUMPRODUCT(A1:A10,B1:B10)/SUM(B1:B10), Excel tries to multiply text by numbers and returns an error. Always start at the first row of actual data, not the header.
Another mistake is forgetting to divide by the sum of the weights. If you write =SUMPRODUCT(A2:A10,B2:B10) without the /SUM(B2:B10) part, you get the total of all weighted values, not the average. The division step is what brings it back down to a single number that represents the average.
If your weights are percentages entered as whole numbers (like 20, 30, 50 instead of 0.2, 0.3, 0.5), the formula still works correctly. Excel treats 20 as 20, not as 20 percent, but the math balances out because you are dividing by the sum of all the weights.
Frequently Asked Questions
What if my values and weights are in different rows instead of columns?
SUMPRODUCT works with rows too. If your values are in A1:D1 and weights are in A2:D2, use =SUMPRODUCT(A1:D1,A2:D2)/SUM(A2:D2). The ranges just need to be the same size and shape.
Can I use weighted average for more than two columns?
SUMPRODUCT only multiplies two lists at a time, so you need one column for values and one for weights. If you have multiple sets of values to average separately, create a separate formula for each pair.
Do the weights have to add up to 100?
No. The weights can add up to any number. The formula divides by the sum of the weights, so whether they total 100, 6, or 1.0, the weighted average is calculated correctly.
What happens if I leave a weight cell blank?
Excel treats a blank cell as zero. If a weight is zero, that value contributes nothing to the average. If you want a value to count, make sure its weight is not blank.