Weighted Average in Excel: Formula, Steps and Examples

Formulas & functions · Updated November 20, 2026

With values in B2:B4 and their corresponding weights in C2:C4, calculate a weighted average in Excel with =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). SUMPRODUCT multiplies each value by its weight and adds the products; dividing by the total weight scales the result to the same units as the values.

What a weighted average means

A weighted average gives some observations more influence than others. In a regular average, each observation contributes equally. In a weighted average, the contribution depends on an assigned weight, such as the share of a total, the number of units sold, or the portion of a course grade determined by an assignment.

This is useful whenever the data points represent unequal quantities or importance. For example, averaging the prices of products without considering how many of each were sold treats one sale of an expensive item as equally influential as hundreds of sales of a cheaper item. A weighted average price instead reflects the overall mix of units.

Weights usually need to be nonnegative and have a meaningful relationship to the values. They do not have to add to 100 or to 1: the formula divides by their sum. The result is therefore unchanged if every weight is multiplied by the same positive number. A set of weights of 2, 3, and 5 has the same relative influence as 20, 30, and 50.

The Excel formula and how it works

Assume B2:B4 contains the values and C2:C4 contains the matching weights, with one value and one weight on each row. The standard formula is =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The ranges must cover the same number of rows and stay aligned: the weight in C2 applies to the value in B2, C3 applies to B3, and so on.

SUMPRODUCT multiplies corresponding cells and sums the products. With three rows, its calculation is equivalent to value one times weight one, plus value two times weight two, plus value three times weight three. SUM adds the weights. Dividing the first total by the second produces the weighted mean. This method works in current Microsoft 365 Excel for Windows and does not require an add-in or a special weighted-average function.

Weights can be entered as whole numbers or as percentages. If the weights are 20%, 30%, and 50%, Excel stores them as 0.2, 0.3, and 0.5. Both the numerator and denominator use that same scale, so the quotient is the same as when the weights are entered as 20, 30, and 50. Do not divide by 100 again.

Worked example: course grades

Suppose B2:B4 contains scores of 80, 90, and 70, while C2:C4 contains weights of 20, 30, and 50. The weighted products are 1,600, 2,700, and 3,500. Their sum is 7,800, and the weights sum to 100, so the formula returns 78. The result is on the same scale as the scores.

The ordinary average of those scores is 80. That is different because it gives each of the three scores one-third of the influence. In the weighted calculation, the score of 70 matters most because it has the largest weight, 50. That is not an error: it is the intended outcome if those weights accurately represent how much each score counts.

For a course grade, confirm what the weights mean before using them. If exams are 50% of the final grade and two assignments are 25% each, those category weights add to 100%. If the listed weights cover only completed work and the grade is meant to reflect completed work so far, dividing by the weights currently present can be appropriate. If missing work should count as zero, treat that as an explicit grading rule rather than silently removing the missing item.

Enter and check the calculation

  1. Put each numeric value in one column and its matching weight in the next. Add clear headers, such as Score and Weight, and keep the two ranges the same height.
  2. Choose an empty cell for the result. If the example data is in B2:B4 and C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4).
  3. Press Enter. Check that the answer is within the range of the values when all weights are nonnegative and the total weight is greater than zero. In the example, 78 falls between 70 and 90.
  4. Format the result to match the data. A grade may use a number format, while a weighted rate may use Percentage. Number formatting changes how the result appears, not the underlying calculation.

It can help to verify the calculation in separate cells during setup. Calculate the sum of the weighted products and the sum of the weights independently, then divide the first by the second with a calculator or another cell. Remove those helper calculations later if they make the sheet harder to read, or keep them as an audit trail.

How to choose appropriate weights

The spreadsheet can calculate a weighted average only after the weights have been chosen. Use weights that express the question you actually want to answer, not weights that merely make the result look plausible. Common choices include:

  • Counts or quantities: Use units sold to calculate an average sale price across different products, or employee hours to combine hourly rates.
  • Shares of a total: Use percentage contributions for grade categories, departmental shares, or components of a composite score.
  • Exposure or duration: Use the relevant time or exposure amount when averaging a measurement across unequal periods, provided that weighting matches the measurement and question.
  • Importance ratings: Use these for a deliberately designed scorecard, but document the scale and rationale because importance weights involve judgment rather than a purely mechanical fact.

Weights should generally be comparable and measured consistently. If one row uses a count and another uses a percentage without a clear conversion, the formula may produce a number but not a meaningful average. Also decide whether the weights represent all relevant observations or only a subset. The denominator is the sum of the weights included in the formula, so changing the included rows changes the question being answered.

Blanks, zeros, and missing data

A zero value is not the same as a blank. A zero is a real numeric observation: if its weight is included, it pulls the weighted average downward. A blank may mean not collected, not applicable, or simply missing. Those interpretations lead to different decisions about whether its weight belongs in the denominator.

If a score cell is blank but its weight is present in the range, do not assume Excel will automatically remove that row from the calculation. The weight is still included by SUM in the denominator, while the blank contributes no numeric score to the product total. That effectively treats the missing score like zero for the weighted calculation. If instead the row should be excluded until a score exists, make the input ranges cover only eligible rows or otherwise explicitly filter out those records.

For example, when calculating a current average from completed assessments, you might include only assessments that have a score. When calculating a final grade where an unsubmitted assessment counts as zero, include its weight and enter a numeric zero. State which policy applies so another person reviewing the workbook can interpret the result correctly.

Common errors and ways to troubleshoot

  • SUMPRODUCT returns an error: Check that the value and weight ranges have matching dimensions. B2:B4 and C2:C4 are compatible; B2:B4 and C2:C5 are not.
  • The formula returns #DIV/0!: The sum of the weights is zero or the selected weight range contains no numeric weights. Check for an empty range, accidental zeros, or an incorrect reference.
  • The result seems too high or low: Confirm each weight is beside the correct value, and check whether a weight was typed as 25 rather than 25% when the intended scale is unclear. Whole-number and percentage weights are both valid when used consistently.
  • The result changes after adding a row: Fixed references such as B2:B4 do not automatically include data entered in row 5. Expand both ranges or use an Excel Table so the data region can grow more reliably.
  • There are unexpected results or errors: Check for error values in either range, text mistakenly entered in numeric cells, and weights that should not be included. Keep headers outside the ranges.

Negative weights are mathematically possible in some specialized calculations, but they are not ordinary averaging weights. They can make the result fall outside the minimum and maximum values, so avoid them unless the model specifically requires them and the interpretation is understood. For a conventional weighted average, use nonnegative weights and verify that their sum is positive.

Weighted average versus other Excel methods

AVERAGE is appropriate when every observation should count equally. It does not consider a separate weight column. In the example, AVERAGE of the three scores gives 80, whereas the weighted calculation gives 78. Neither result is universally better; each answers a different question.

SUMPRODUCT is a compact choice when values and weights are in parallel ranges. You could also calculate each value-times-weight product in a helper column, sum that column, and divide by the weight total. A helper column takes more space but can make the arithmetic easier to audit, especially when a workbook is used by people who need to see each contribution.

A simple average of subgroup averages can also be misleading. If one store has 10 transactions and another has 1,000, averaging the two store averages equally gives each store the same influence. To find the average across all transactions, weight each store average by its transaction count. This is a common reason that a weighted average differs from an average of averages.

Using an Excel Table for growing data

For a list that will gain rows, convert the data range to an Excel Table using Insert > Table, and confirm that the table has headers. Tables make it easier to label fields and extend a dataset as new records are added. If the table is named Scores and its columns are Score and Weight, the equivalent structured-reference formula is =SUMPRODUCT(Scores[Score],Scores[Weight])/SUM(Scores[Weight]). This assumes those exact table and column names; Excel adjusts structured references if you rename them.

Keep each table row as one observation, and avoid blank or summary rows within the data. If the table contains incomplete records, decide how those rows should affect the denominator before relying on an all-rows formula. For recurring reports, documenting the meaning of the weight in the column header or a nearby note can prevent someone from treating a count, percentage, or priority score as interchangeable.

Frequently Asked Questions

Does Excel have a weighted average function?

Excel does not have a dedicated built-in function named Weighted Average. Use SUMPRODUCT to multiply values by weights, then divide by the sum of the weights. For aligned numeric ranges, this is a concise and widely applicable approach.

Do weighted-average weights have to add up to 100%?

No. They can add to 100%, 1, 10, or another positive total. The division by the sum of the weights normalizes the result. Percentages can make the weighting easier to interpret, but they are not required.

Can I calculate a weighted average if some values are blank?

Yes, but first decide whether a blank row should be excluded or treated as a zero. If the blank value has a weight inside the selected range, the denominator can still include that weight. Exclude incomplete rows when they should not count; use zero when zero is the intended value.

Why is my weighted average the same as the regular average?

That can happen when all weights are equal, because every value then has the same influence. It can also happen by coincidence when unequal weights balance out. Check the weights, their alignment with the values, and whether the data genuinely calls for weighting.

Can weights be entered as percentages?

Yes. Enter values such as 20%, 30%, and 50% as percentages, or enter 20, 30, and 50 as whole-number weights. Since the formula divides by the total weight, either consistent scale gives the same result.

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.