To calculate a weighted average in Excel, multiply each value by its corresponding weight, add those products, and divide by the sum of the weights. With values in B2:B6 and matching weights in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6). This works whether the weights add up to 1, 100, or another positive total.
What a weighted average means
A weighted average gives some values more influence than others. In a regular average, every observation counts equally. In a weighted average, each value is multiplied by a weight that represents its relative importance, frequency, share, or contribution. The final result is normalized by dividing by the total weight.
For example, a course grade might combine homework worth 20% of the grade, a midterm worth 30%, and a final exam worth 50%. A score of 90 on the final matters more than a score of 90 on homework because the final has the larger weight. The weights describe importance; they are not the scores themselves.
A weighted average is useful when combining measurements that do not contribute equally. Common uses include course grades, average prices across purchases, portfolio or product metrics, survey results adjusted for representation, and performance scores built from several criteria. The calculation is meaningful only when the chosen weights match the question being asked.
Set up a simple calculator in Excel
Create one column for the values and a neighboring column for their corresponding weights. Keep each value and its weight on the same row so Excel can pair them correctly. Add headings to make the worksheet easier to understand, such as Score and Weight.
- Enter each value in B2:B6 and its weight in C2:C6.
- Put a label such as Weighted average in E2.
- Enter the calculation in E3, using the ranges described in the opening formula.
- Format E3 as a number with an appropriate number of decimal places. Use Percentage format only if the result itself represents a percentage.
In Microsoft 365 for Windows, you can select the result cell and use Home > Number to choose a number format or adjust decimal places. The formula works in Excel for Mac and Excel for the web too; the main steps and function names are the same. Depending on regional settings, Excel may expect semicolons instead of commas between function arguments. If Excel rejects a formula with commas, check the list separator used by your installation.
How the SUMPRODUCT formula works
SUMPRODUCT multiplies corresponding entries in two or more ranges and adds the products. For values 80 and 90 with weights 1 and 3, it first calculates 80 × 1 and 90 × 3, then adds those products. Dividing by the total weight makes the answer comparable to the original values rather than leaving it as a sum of weighted contributions.
With values in B2:B6 and corresponding weights in C2:C6, the weighted sum alone is =SUMPRODUCT(B2:B6,C2:C6). To calculate the weighted average rather than just the weighted sum, divide that result by the total of C2:C6, as shown in the opening formula. SUMPRODUCT and SUM are long-established Excel functions and are available in Microsoft 365 for Windows.
The ranges must line up: the first value in B2 is paired with the first weight in C2, B3 with C3, and so on. They also need the same number of cells. If you add another observation, extend both ranges together. A formula that includes a value but omits its weight, or vice versa, no longer describes the intended set of pairs.
Worked example: calculating a course grade
Suppose a student earns 82 on homework, 91 on a midterm, 76 on a project, and 88 on a final. The corresponding weights are 20%, 30%, 25%, and 25%. Put the scores in B2:B5 and the percentage weights in C2:C5. Because the weights total 100%, the calculation produces the weighted grade directly.
With scores in B2:B5 and decimal weights in C2:C5 (0.20, 0.30, 0.25, and 0.25), enter =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). The weighted products are 16.4, 27.3, 19, and 22; their sum is 84.7, and the weights sum to 1. The result is therefore 84.7.
You can also enter the weights as 20%, 30%, 25%, and 25% using Excel’s Percentage format. Percentage entries are stored as decimal values, so the formula still returns 84.7. If instead you type whole numbers 20, 30, 25, and 25, the denominator is 100 and the result is still 84.7. The denominator in the formula is what makes all three weight scales work.
Choose and enter weights carefully
Weights are not interchangeable with raw counts or percentages unless they represent the relative contribution you intend. A weight of 30 and a weight of 0.30 have the same relative influence when all weights use the same scale. But mixing scales, such as entering 20 for one item and 0.30 for another, can produce a misleading result.
- Percentage weights: Enter 20% or 0.20 for a 20% share. Confirm that the shares add to 100% when the model requires a complete allocation.
- Frequency weights: Use counts when each value stands for a group of observations. A value observed 10 times should have weight 10 if the goal is to average all underlying observations.
- Importance scores: Relative scores such as 1, 2, and 3 can be used when the scale expresses relative influence. Their total does not have to equal 100.
Do not assume that weights must always add to 100. The formula divides by their total, so weights of 2, 3, and 5 produce the same average as 20%, 30%, and 50%. Still, checking the total is useful: if the weights are intended to represent percentages, a total other than 100% may reveal a missing item or data-entry mistake.
Common errors and how to fix them
A #DIV/0! error usually means the sum of the weights is zero or the selected weight range contains no usable numeric total. Check that weights are present, numeric, and not all zero. If zero-weight rows are intentional, they contribute nothing, but there must still be a nonzero total weight for the average to exist.
A #VALUE! error can occur when the ranges have incompatible shapes or when a calculation encounters unsuitable data. Make sure the value and weight ranges contain matching numbers of rows and columns, and inspect cells for text accidentally entered where a number was expected. Blank cells or text can also lead to an incomplete calculation, even when Excel does not display an error, so clean the input rather than relying on how a function handles irregular entries.
Another frequent mistake is using the wrong weight column or shifting one range by a row. Labels, filters, and clear headers help prevent this. Also avoid including a totals row in both ranges: doing so counts the total as though it were another observation. When data grows, update both ranges or convert the input area to an Excel table and use structured references that expand with added rows.
Negative weights are mathematically possible, but they are unusual for ordinary grade, price, or share calculations. They can make the result difficult to interpret and can cause the total weight to be zero or nearly zero. Use them only when the model explicitly calls for signed contributions, and explain that choice in the worksheet.
Weighted average versus a regular average
Use AVERAGE when every listed value should count equally. Use a weighted average when values represent different numbers of observations or have different levels of importance. For example, the average of a class with 10 students scoring 80 and a class with 30 students scoring 90 should give the larger class more influence if the goal is the average score across all 40 students.
With class averages in B2:B3 and student counts in C2:C3, the combined average is =SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3). The result is 87.5: 80 × 10 plus 90 × 30 equals 3,500, and 3,500 divided by 40 equals 87.5. Simply averaging the two class averages gives 85, which answers a different question: the unweighted average of the two class-level results.
This distinction matters when combining averages. Averaging two department averages, monthly rates, or group percentages equally can be wrong if the groups differ in size or volume. If the underlying counts are available, use them as weights when the intended result is an overall average across those underlying observations.
Check and present the result
Before sharing a weighted average, verify that each weight belongs to the right value and that the denominator represents the full set you intend to include. A simple independent check is to calculate the weighted products in a temporary column, add them, and divide by the weight total. This makes it easier to spot a misaligned row or an unexpected contribution.
Keep the units consistent. If the values are percentages on a 0–100 scale, the result uses that scale; if they are decimals from 0 to 1, the result does too. Format the output to match the data and label it clearly, for example Weighted score (points) or Weighted rate. Formatting changes how a value appears, not the underlying calculation.
If the worksheet will be reused, add a note describing what the weights mean and where they came from. Consider using Excel’s Data Validation options to restrict weight entries to expected values, such as nonnegative numbers. For a percentage allocation that must total 100%, a visible check of the total is more reliable than assuming users will enter every share correctly.
Frequently Asked Questions
What is the Excel formula for a weighted average?
Use SUMPRODUCT to multiply each value by its matching weight, then divide by the sum of the weights. For values in B2:B6 and weights in C2:C6, use =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6).
Do weighted average percentages need to add up to 100?
No, not for the formula to calculate a result. Dividing by the sum of the weights normalizes them. However, if the weights are meant to be a complete set of percentages, checking that they total 100% can catch missing or incorrect entries.
Can I use whole-number weights instead of percentages?
Yes. Relative weights such as 2, 3, and 5 produce the same result as 20%, 30%, and 50%, because the formula divides by the total weight. Use one consistent scale throughout the weight range.
Why does my weighted average show #DIV/0?
The denominator, SUM of the weights, is zero. Check for blank, nonnumeric, or all-zero weights, and confirm that the formula points to the intended weight cells.
Can I calculate a weighted average when some values are missing?
Decide first whether a missing value should be excluded or treated as zero; those choices mean different things. For an exclusion, remove or filter out the missing value and its matching weight from both ranges, then calculate using the remaining valid pairs.
Related
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FV Formula: How to Calculate Future Value and GrowthIn Excel for Microsoft 365, use the FV function to calculate the future value of regular payments or…
- Excel Named Range Formula: Examples and SetupA named range formula example in Excel is =SUM(Sales), where the name Sales refers to a cell range…
- Excel Extract Date from DateTime: Formulas and FixesTo extract the date from an Excel date-time value in cell A2, enter =INT(A2). The formula removes the…
- Smartsheet SUMIF Examples for Everyday ReportsIn Smartsheet, SUMIF adds values in a sum range only when corresponding cells meet one condition. For example,…
- Excel SUM Formula That Changes With FiltersUse =SUBTOTAL(109,B2:B100) to sum only the currently visible values in B2:B100. When you filter the rows, Excel recalculates…
- Excel LET and LAMBDA: How They Work Together in FormulasExcel’s LET function assigns names to values inside a formula, while LAMBDA lets you create a reusable function…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.