In Excel, calculate a weighted average by multiplying each value by its weight, adding those products, and dividing by the sum of the weights. With scores in B2:B4 and their weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4); for scores of 85, 92, and 78 weighted at 20%, 30%, and 50%, the result is 83.6.
A Worked Excel Example
Suppose a course grade combines three assessments, and each assessment contributes a different share of the final grade. Put the assessment names in column A, scores in column B, and weights in column C:
- Row 2: Quiz, score 85, weight 20%.
- Row 3: Project, score 92, weight 30%.
- Row 4: Final exam, score 78, weight 50%.
With scores in B2:B4 and weights in C2:C4, enter this formula in another cell, such as D2: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Excel multiplies 85 by 20%, 92 by 30%, and 78 by 50%, then adds those results: 17 + 27.6 + 39 = 83.6. The weights total 100%, so the denominator is 1, and the weighted average is 83.6 points.
This differs from a basic average. The ordinary average of the three scores is 85, because it treats every assessment as equally important. The weighted result is lower because the 78 on the final exam has the largest weight. The calculation reflects the grading policy rather than giving each row equal influence.
How the Weighted Average Formula Works
A weighted average is the sum of value-by-weight products divided by the total weight. In symbols, it is (value 1 × weight 1 + value 2 × weight 2 + …) ÷ (weight 1 + weight 2 + …). This makes each value’s contribution proportional to its assigned weight.
In Excel, SUMPRODUCT multiplies corresponding cells in two same-sized ranges and adds the products. SUM adds the weights in the denominator. The two ranges must line up: the value in B2 is paired with the weight in C2, B3 with C3, and so on. If they have different dimensions or the wrong rows are paired, the result may be an error or a plausible but incorrect number.
Weights do not have to be typed as percentages. For example, weights of 20, 30, and 50 produce the same average as 20%, 30%, and 50%, because dividing by the total weight scales the calculation. Percentage entries are often easier to interpret, while raw points or counts may suit other datasets. Use one consistent interpretation within a calculation.
Set Up the Data and Format the Result
For a reliable worksheet, keep one observation per row and place the measure and its weight in separate columns. Add clear headings such as Score and Weight, and keep totals or notes outside the ranges used in the formula. If the data are formatted as an Excel table, structured references can make a formula easier to read, but ordinary cell ranges are sufficient for a small example.
Enter percentage weights as actual percentages when that is what they represent: typing 20% stores the decimal value 0.2. The sample formula works whether those percentages add to 100% or to another positive total, because it divides by the sum of weights. If the weights are meant to be shares of a whole, check that they total 100%; that is a useful validation, even though the formula itself normalizes the values.
To display weights as percentages, select their cells and use Home > Number group > Percentage Style. Format the result as a number with an appropriate number of decimal places, or as a percentage if the values being averaged are percentages. Formatting changes how a value appears, not the underlying calculation. For example, displaying 83.6 with zero decimal places shows 84, but the stored result remains 83.6.
When a Weighted Average Is Appropriate
Use a weighted average when observations represent different amounts, importance, or exposure. Common examples include course grades with different assessment shares, average purchase prices across quantities bought, average unit costs across batches, and average survey results where groups have different population sizes. The choice of weight should correspond to the question being answered.
For example, averaging two product prices equally answers a different question from calculating the average price paid across all units. If 2 units cost $10 each and 8 units cost $15 each, the unit-weighted average is $14 per unit: (2 × 10 + 8 × 15) ÷ 10. An equal average of the two listed prices would be $12.50, but it would give the small purchase the same influence as the larger one. These figures are illustrative arithmetic, not financial advice.
Weights can represent frequency or size, as well as importance. If each row is a group average and the group sizes differ, group size may be the right weight for an overall average. However, if the goal is to compare group performance without letting large groups dominate, equal weights may be intentional. The calculation cannot decide which interpretation is appropriate; that depends on the measurement and the intended comparison.
Common Errors and Checks
Weighted-average mistakes often come from inconsistent data rather than a faulty function. Before relying on the output, verify the inputs and the meaning of the weights:
- Confirm that each value is matched to its own weight on the same row.
- Check that the value and weight ranges contain the same number of cells.
- Look for weights entered as whole numbers in some rows and percentages in others.
- Check that weights are numeric and that the total weight is not zero.
- Decide how to handle missing values before calculating the result.
If every weight is zero, the denominator is zero and Excel returns a division error. A negative weight is mathematically possible in some specialized calculations, but it is not normally a meaningful share of importance or quantity; investigate it rather than silently accepting it. If a score is blank but its weight remains in the range, the blank can effectively contribute a zero to the numerator while its weight still contributes to the denominator. That may pull down the result. Decide whether to complete the missing score, exclude that row from both ranges, or apply a documented rule.
It can also help to calculate the weight total in a separate cell and compare it with the expected total. With weights in C2:C4, enter =SUM(C2:C4) to check their sum; for the example, the result is 100% (or 1 when the cells store percentage values). This check does not prove that each weight is assigned correctly, but it catches many omissions and accidental entries.
Weighted Average Versus Other Excel Calculations
AVERAGE gives each numeric observation equal weight. It is appropriate when each observation should count equally, such as a set of individual test scores under an equal-weight policy. A weighted average is appropriate when one observation should count more than another. Choosing between them is a modeling decision, not just a formula preference.
A common misconception is that weights must add to exactly 100 for a weighted average to work. They do not have to if the formula divides by their actual sum. If weights sum to 100%, the denominator is 1; if they sum to 10, the numerator is divided by 10. However, if the weights are defined as percentages of a total, a sum other than 100% may reveal an input error and should be checked.
Another important distinction is between an average of averages and an average of the underlying observations. Averaging two department averages equally is only equivalent to pooling all employees if both departments have the same number of employees, or if equal department influence is what the analysis intends. When department sizes differ and the goal is an overall employee average, use the department sizes as weights.
Using the Formula for Larger or Changing Data
For a longer list, extend both ranges to cover the same rows. If the scores run from B2 through B100 and the weights run from C2 through C100, use those matching ranges in the same SUMPRODUCT-over-SUM pattern. Avoid including unrelated totals in either range, as that would treat the total as another observation.
If rows are added frequently, an Excel table can help the calculation grow with the data. Select the dataset and choose Insert > Table, making sure the header option matches the layout. You can then use table column references in formulas; verify the generated names because they depend on the actual table and column headings. For a one-time calculation, fixed cell ranges are easier to inspect and are less likely to confuse a reader.
When auditing a result, calculate a few products separately or inspect the formula ranges in the formula bar. In the worked example, the three contributions should be 17, 27.6, and 39. If the output is unexpectedly different, confirm that the cells contain the intended numeric values and that no row has been omitted or included twice.
Frequently Asked Questions
What is the weighted average formula in Excel?
Multiply each value by its corresponding weight, add those products, and divide by the sum of the weights. For values in B2:B4 and weights in C2:C4, use =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4).
Do weights need to add up to 100% in Excel?
No. The formula divides by the total weight, so it can use weights that add to another positive number. If the weights are intended to be shares of a whole, though, a total other than 100% may signal a data-entry or policy issue.
Can I use SUMPRODUCT for a weighted average?
Yes. SUMPRODUCT multiplies each value by the weight in the corresponding position and adds the products. Divide that result by the sum of the weights to get the weighted average.
Why is my weighted average showing a division error?
The denominator may be zero, often because all weights are zero or the weight range is empty. Check that the weight cells contain numeric values and that their sum is greater than zero.
What is the difference between AVERAGE and a weighted average?
AVERAGE gives each value equal influence. A weighted average gives values more or less influence according to their weights, so it is useful when observations represent different quantities or importance.
Related
- Email Merge from Excel: Steps, Setup, and Safer Sending
- Emergency Light Checklist Template in Excel: What to Include
- Electronic Inventory Tracking in Excel: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IF Formula Examples for Everyday Spreadsheet TasksExcel’s IF formula checks whether a condition is true and returns one result if it is, and another…
- Excel Formula for Years of Service From Hire DateTo calculate completed years of service from a hire date in Excel, use =DATEDIF(A2,TODAY(),"Y"), assuming the hire date…
- Excel IFS Greater Than and Less Than: ExamplesYes. In Excel, use greater-than and less-than operators directly inside an IFS logical test, such as A2>10 or…
- How to Show Formulas in Excel for MacTo show formulas in Excel for Mac, open the worksheet and choose Formulas > Show Formulas. You can…
- Due Date Calculator in Excel: Formulas and ExamplesFor a basic due date calculator in Excel, enter a start date in A2 and the number of…
- Excel XLOOKUP Examples: Formulas and TipsMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another; for example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.