Weighted Average in Excel: Formulas, Examples, and Tips

Formulas & functions · Updated November 22, 2026

To calculate a weighted average in Excel, multiply each value by its weight, add those products, and divide by the sum of the weights. If values are in A2:A4 and matching weights are in B2:B4, enter =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4).

What a weighted average tells you

A weighted average combines several values while giving some values more influence than others. In a simple, or arithmetic, average, every value counts equally. In a weighted average, the contribution of each value depends on its assigned weight. This makes it useful whenever observations represent different amounts, importance, or portions of a whole.

For example, a course grade might combine homework, quizzes, and exams, with exams carrying more weight than homework. A business might calculate average selling price from different quantities sold at different prices. In both cases, simply averaging the listed scores or prices would treat every entry as equally important, which would not match the question being asked.

Weights are often percentages, but they do not have to be. They can be any nonnegative numbers that express relative importance or quantity. A set of weights of 20%, 30%, and 50% has the same proportions as weights of 2, 3, and 5.

How the Excel formula works

SUMPRODUCT multiplies corresponding entries in two ranges and adds the results. Dividing that total by the sum of the weights normalizes the result, so the answer remains on the scale of the original values. The formula works whether weights are entered as percentages, decimals, or proportional counts, provided the value and weight in each row belong together.

With values in A2:A4 and their weights in B2:B4, the formula is =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). The ranges must contain the same number of corresponding rows. For example, if A2 is a quiz score, B2 must be that quiz’s weight; mixing up row alignment produces a mathematically valid result that answers the wrong question.

The denominator matters. If weights add to 1 or 100%, dividing by their sum leaves the weighted total properly scaled. If weights add to 10, 100, or another positive number, the division still normalizes them. This is why you generally do not need to convert whole-number weights into percentages before using the formula.

Step-by-step grade example

Suppose a class uses three components: a score of 80 weighted at 20%, a score of 90 weighted at 30%, and a score of 70 weighted at 50%. Put the scores in A2:A4 and the matching percentage weights in B2:B4. Format the weight cells as percentages if you want them displayed as 20%, 30%, and 50%; Excel stores those entries as 0.2, 0.3, and 0.5.

With scores in A2:A4 and percentage weights in B2:B4, enter =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). The calculation is (80 × 0.2) + (90 × 0.3) + (70 × 0.5), divided by 1, which equals 78. The weighted course result is 78, not the simple average of 80, 90, and 70, which is 80. The lower exam score has greater influence because the exam carries half of the total weight.

You can also use whole-number weights. With the same scores in A2:A4 and weights 2, 3, and 5 in B2:B4, the same formula returns 78: the weighted sum is 780 and the weights sum to 10. This is often convenient when a grading policy is stated as relative points rather than percentages.

Check whether weights are set up correctly

Before relying on a result, confirm what the weights mean and whether they cover all the relevant observations. If weights are intended to represent shares of a whole, they commonly sum to 100%. A quick check is to select the weight cells and inspect the total in Excel’s status bar, or calculate a separate total in a spare cell.

With weights in B2:B4, enter =SUM(B2:B4) in a separate cell to check their total. For percentage weights, the expected result is 100% (or 1 when displayed as a decimal); for relative weights such as 2, 3, and 5, the total is 10. The weighted-average formula still computes a result if the weights do not sum to 100%, but that may reveal a missing category or a data-entry error if the policy requires a complete distribution.

Weights should usually be zero or positive for an ordinary weighted average. A zero weight means the corresponding value has no effect on the result. Negative weights can be used in some specialized mathematical models, but they do not usually represent a normal share, quantity, or importance and can make the result unintuitive. Check the meaning of the data before accepting negative values.

Another example: average selling price

A common mistake is to average prices without accounting for how many units sold at each price. Imagine selling 12 units at $4 each and 8 units at $5 each. The simple average of the two prices is $4.50, but the average revenue per unit is lower because more units sold at the lower price.

With unit prices in A2:A3 and quantities sold in B2:B3, enter =SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3). The weighted revenue is (4 × 12) + (5 × 8), or $88, and the total quantity is 20, so the weighted average selling price is $4.40 per unit. This illustrates the general rule: use the number of units as the weight when the desired result is an average per unit across items sold.

The same logic applies to rates and durations, but first define the quantity being averaged. For instance, averaging the hourly rates of employees equally answers a different question from calculating the average rate across all hours worked. Choosing the appropriate weight is a matter of interpreting the data, not merely selecting an Excel function.

Missing values, errors, and other common problems

A blank value paired with a nonzero weight deserves attention. SUMPRODUCT treats a blank numeric contribution like zero, while SUM still includes a numeric weight in the denominator. As a result, the blank row can act like a value of zero and pull the average down. If the blank means the observation is unavailable rather than truly zero, do not blindly use the basic formula across that row. Complete the missing value, exclude the entire observation from both ranges, or build a clearly documented approach that includes only valid pairs.

Likewise, a weight entered as 20 instead of 20% is not inherently wrong if all weights use a consistent proportional scale. But mixing 20 with 0.3 and 0.5 is almost certainly an error. Check whether Excel shows the intended percentage and whether entries are consistently stored as percentages, decimals, or whole-number weights.

If the formula returns #DIV/0!, the sum of the weights is zero or the denominator range contains no usable numeric weights. Check that the weights are present and that their total is greater than zero. A #VALUE! error can point to incompatible data, such as text where numeric values are expected, or to ranges that do not pair cleanly. Make sure the values and weights are numeric and that the two ranges are the same size.

Excel formulas normally use commas between function arguments, as in the examples above. Depending on regional settings, an installation may require semicolons instead. If Excel rejects a formula that otherwise looks correct, check the list separator settings for that installation. The formula logic is the same.

Formatting and making the result easier to use

The weighted average inherits the numeric character of its inputs, but the result cell may need an appropriate display format. Select the result cell and use Home > Number to choose a number, percentage, or currency format. Formatting changes how a value is displayed, not the underlying calculation. For example, a score of 78 can be displayed with zero or one decimal place without changing the weighted average.

Keep labels next to the data: identify the values, the weights, and the units. When a workbook is shared, a heading such as Weight (%) is more informative than Weight, especially if someone might enter 20 rather than 20%. For recurring work, use an Excel Table so added rows are easier to manage, and verify that the formula includes every intended observation after the table grows.

The SUMPRODUCT and SUM approach is available in current Excel for Microsoft 365 on Windows. The same functions and formula logic are available in Excel for Mac and Excel for the web; menu layout can differ, but the calculation does not require a Windows-only feature.

Weighted average versus a simple average

Use AVERAGE when every observation should count equally. Use a weighted average when each observation represents a different quantity or has a different stated importance. Neither method is universally better: they answer different questions. If every weight is identical, the weighted average equals the arithmetic average, assuming the same observations are included.

Do not confuse the weighted average of subgroup averages with the average of all individual records. If one department reports an average based on 10 people and another reports an average based on 100 people, averaging the two department averages equally gives each department the same influence. Weighting those averages by their group sizes instead reflects the average across all 110 people, provided the group data are comparable.

Frequently Asked Questions

What is the formula for a weighted average in Excel?
With values in A2:A10 and matching weights in B2:B10, use =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10). Make sure each value is paired with the weight on the same row.

Do weights have to add up to 100%?
No. They can be relative numbers such as 2, 3, and 5 because the formula divides by their sum. If weights are supposed to represent a complete percentage breakdown, however, a total other than 100% may indicate an error or an omitted category.

Can I use whole-number weights instead of percentages?
Yes. Whole-number weights work as long as their relative proportions are right and the values are numeric. Weights of 2, 3, and 5 have the same effect as 20%, 30%, and 50%.

Why is my weighted average lower than expected?
Check for blank score cells with nonzero weights, a high weight attached to a low value, mismatched rows, and inconsistent percentage entry. Also confirm that you intended to weight the values at all; the weighted result can legitimately differ from a simple average.

How do I calculate a weighted average when some values are missing?
Decide whether each blank means zero or unavailable data. If it is unavailable, exclude that row’s value and weight together, or use a method that explicitly includes only complete numeric pairs. Do not leave the weight in the denominator while silently treating a missing score as zero.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.