To calculate a weighted average in Excel, multiply each value by its weight, add those products, and divide by the sum of the weights. With values in B2:B4 and their corresponding weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The result is a weighted average that gives more influence to values with larger weights.
Why use a weighted average?
A regular average treats every value as equally important. That is suitable when each observation represents the same amount, but it can be misleading when observations have different levels of importance or represent different quantities. A weighted average accounts for that difference by assigning a weight to each value.
For example, a course grade may combine homework, quizzes, and a final exam. If the final exam counts for half the grade, it should influence the overall result more than one homework assignment. Likewise, a business might calculate an average selling price from quantities sold at different prices; a price at which many units sold should contribute more than a price at which only one unit sold.
The idea is not limited to grades or prices. Weighted averages are useful for survey results with different group sizes, average unit costs, performance measures, and other calculations where each value represents a different share of the total. The key question is whether the weights accurately represent the importance or quantity you want to account for.
How the Excel calculation works
The calculation has two parts. First, multiply each value by its matching weight and add the products. Then divide that total by the sum of all weights. In mathematical terms, the weighted average is the sum of value times weight divided by the sum of weights.
In Excel, SUMPRODUCT multiplies corresponding entries in two ranges and adds the resulting products. SUM adds the weights in the second range. Dividing one result by the other normalizes the weighted total, so the answer stays on the same scale as the original values.
For instance, if values are 80 and 100, with weights of 1 and 3, the weighted result is 95: the 100 counts three times as much as the 80. A normal average would be 90 because it gives both values equal influence. This distinction is the main reason to use a weighted calculation rather than Excel’s ordinary average function.
The weights do not have to add up to 1 or 100. They can be percentages, fractions, counts, or other consistent quantities. Dividing by their sum is what makes the result an average rather than just a weighted total.
Enter the formula step by step
Set up the worksheet so each value appears on the same row as its corresponding weight. For example, put the items or categories in column A, the values in B, and the weights in C. Use a header row, then enter the first observation in row 2.
- Enter each value in B2:B4 and its matching weight in C2:C4. Keep the row pairings aligned: the weight in C2 must belong to the value in B2.
- Choose an empty cell for the answer, such as D2.
- Enter
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)and press Enter. - Format the result to suit the data. For a percentage grade, use Percentage or a suitable number of decimal places; for a price, use Currency if appropriate.
This formula works in Microsoft Excel for Microsoft 365 on Windows. The same function and formula work in current Excel for Mac and Excel for the web; no special array-entry keystroke is needed. If your regional settings use semicolons as argument separators, Excel may display or require semicolons instead of commas.
Worked example: weighted course grade
Suppose a student earns 88 on coursework worth 20% of the grade, 92 on a project worth 30%, and 76 on a final worth 50%. Put the scores in B2:B4 and the weights in C2:C4. The weights total 100%, and the formula returns 83.2.
The arithmetic is 88 × 0.20, plus 92 × 0.30, plus 76 × 0.50. Those weighted contributions are 17.6, 27.6, and 38, which sum to 83.2. The final exam has the greatest effect because it has the largest weight. The unweighted average of the three scores is about 85.3, a different answer because it treats all three components equally.
You can enter the weights as 20%, 30%, and 50% using Excel’s Percentage format, or as 0.2, 0.3, and 0.5. Both represent the same proportions, and the weighted-average formula returns 83.2 either way. You can also enter 20, 30, and 50: the formula still returns the same result because it divides by the sum of those weights. What matters is using a consistent scale, not whether the total is exactly 1 or 100.
Choosing and checking the weights
Before calculating, identify what each weight is meant to represent. It might express a proportion of a final result, the number of units sold, the number of people in a group, or a measure of relative importance. Different meanings can lead to different valid calculations, so a formula cannot correct weights that do not match the question.
- For percentages: Check that the percentages represent the intended shares. If they are supposed to cover the whole result, they should total 100% (allowing for small rounding differences).
- For quantities: Use the quantity associated with each value. For an average selling price, for example, unit counts can serve as weights.
- For importance ratings: Confirm that the scale is meaningful. A weight of 4 should have the intended relationship to a weight of 2; arbitrary labels are not automatically valid numerical weights.
- For missing observations: Decide whether a missing value should be excluded along with its weight or treated in another defined way. Do not let a blank or zero stand in for a real value without considering its meaning.
A useful check is to compare the answer with the smallest and largest values that have positive weights. The weighted average should fall between them. If it does not, check the references, signs, and data types. Another check is to confirm that each weight is paired with the correct value and that the weight total is nonzero.
Common errors and how to fix them
Misaligned ranges: SUMPRODUCT pairs entries by position. If a value range starts on row 2 and its weight range starts on row 3, the wrong observations are combined. Make both ranges the same size and start them on the same row.
Adding weights to the numerator but not the denominator: The sum of value-times-weight products alone is a weighted total, not an average. Divide by the sum of weights unless the weights are already normalized and you deliberately intend a different expression. Keeping the denominator in the formula is usually clearer and safer.
A zero total weight: If all weights are zero, the denominator is zero and Excel returns a division error. Review the weights; there is no meaningful weighted average when their total is zero. Negative weights may also be inappropriate for ordinary averages, depending on what the data represents.
Numbers stored as text: Values or weights imported from another system may look numeric but be stored as text. Check whether the cells are recognized as numbers, and convert them if needed. Text, error values, or inconsistent imported data can cause unexpected results or errors in a calculation.
Including totals or headers: Do not include a summary row in the ranges being averaged. Including an existing total as if it were another observation can distort the result. Use only the rows that represent individual values and their matching weights.
Rounding each contribution too early: Keep the source values and weights at their appropriate precision, then format the final answer for display. Formatting a cell to show one decimal place changes what is displayed, not the underlying value used in later calculations. Rounding every intermediate contribution can create avoidable differences.
Weighted average versus other Excel methods
Excel’s AVERAGE function calculates an ordinary mean, not a weighted one. Use it when every observation should count equally. If every weight in a weighted-average calculation is identical, the weighted result matches the ordinary average; otherwise, the two can differ.
You can also calculate a weighted result by writing out each multiplication and addition, then dividing by the total weight. That approach can be easy to inspect for only a few values, but it becomes lengthy and fragile as rows are added. SUMPRODUCT is usually more convenient because it handles matching ranges in one formula.
A PivotTable can summarize data by groups, but it does not automatically make every displayed average a weighted average. If you need an average weighted by a quantity field, make sure the calculation reflects the sum of value-times-quantity divided by the sum of quantity. Choosing a summary labeled Average alone may produce an unweighted result.
Frequently Asked Questions
Do the weights have to add up to 100%?
No. The formula divides by the total weight, so weights such as 2, 3, and 5 produce the same result as 20%, 30%, and 50%. They should, however, use a consistent scale and represent the intended proportions or quantities.
Can I calculate a weighted average when the weights are percentages?
Yes. Enter the percentages as Excel percentages or as decimal equivalents, then use the same weighted-average formula. A set of shares totaling 100% is common, but the formula still normalizes other consistently scaled weights.
Why is my weighted average different from the average shown by Excel?
Excel’s ordinary average gives each numeric value equal weight. A weighted average gives greater influence to values with larger weights. The results differ when the weights differ; if all weights are equal, the two results should match.
What happens if one weight is zero?
A zero weight contributes nothing to the numerator and does not affect the denominator. That observation therefore has no influence on the result. If every weight is zero, the formula divides by zero and cannot return a meaningful weighted average.
Can I use the formula with more than three rows?
Yes. Expand both ranges to cover every value and its corresponding weight, making sure they have the same number of cells. For a growing list, you can extend the ranges when adding rows or organize the data in an Excel table and adjust the calculation to use the table’s columns.
Related
- Attendance Point Tracking Excel Template?
- Attendance Point System Template Excel: Build and Use
- Attendance Excel Template: Free Options and Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Criteria Examples for ExcelIn Excel for Microsoft 365, COUNTIF counts cells in one range that meet one condition: enter the range…
- Excel Day Name Formula: Show the Weekday from a DateTo show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- COUNTIF Excel Formula for Dates: Examples and FixesTo count cells that contain a particular date, use Excel’s COUNTIF function with a real date value as…
- Excel XLOOKUP Examples for Common Spreadsheet TasksMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another. For example,…
- Excel FILTER and XLOOKUP: How to Use Them TogetherYes. In Excel for Microsoft 365, you can use XLOOKUP to find a value and FILTER to return…
- Excel SEQUENCE Shortcut: Fast Ways to Create a SeriesExcel has no dedicated keyboard shortcut for the SEQUENCE function. In Microsoft 365 or Excel 2021 and later,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.