To calculate a weighted average in Excel, multiply each value by its corresponding weight, add those products, then divide by the total weight. If your values are in B2:B5 and their weights are in C2:C5, enter =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). With the example data below, the result is 87.95. This method works in Microsoft Excel for Microsoft 365 on Windows and in current Mac and web versions.
A regular average gives every item equal importance. A weighted average accounts for differences in importance, such as assignment weights in a course, unit prices for purchases, or survey responses with different sample sizes. Before starting, make sure every value is paired with the correct weight and decide whether your weights are recorded as whole numbers or percentages.
What You’ll Need
- An Excel workbook and a set of numeric values to average.
- A weight for each value, recorded in a separate column. Weights may be numbers such as 20 and 30, or percentages such as 20% and 30%.
- A blank cell for the result. The weights do not have to add up to 100, but their total must not be zero.
Step-by-Step
-
Enter the values and their weights in matching rows. Put the values in one column and the corresponding weights in the next. For example, enter assignment scores in B2:B5 and their course weights in C2:C5:
Row Score (column B) Weight (column C) 2 88 20 3 92 30 4 76 25 5 95 25 Keep each score and its weight on the same row. The weight in C2 applies to the value in B2, the weight in C3 applies to B3, and so on. Labels in row 1 are useful, but do not include them in the calculation ranges.
-
Check the weight scale and the data. In this example, the weights total 100, so they can be read as percentages: 20%, 30%, 25%, and 25%. You could instead enter 20%, 30%, 25%, and 25% as percentage-formatted cells. The weighted average is unchanged as long as all weights use a consistent scale. Look for blank cells, text accidentally entered as a number, and weights assigned to the wrong rows. Weights commonly represent a share, quantity, frequency, or level of importance.
-
Choose a result cell and enter the calculation. With scores in B2:B5 and their matching weights in C2:C5, select an empty cell such as E2 and enter
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). Press Enter. SUMPRODUCT multiplies each score by the weight in the same row and adds the products. SUM adds the weights, and the division scales the total back to an average. The example produces 87.95 because the weighted products total 8,795 and the weights total 100.If the weights are stored as percentages rather than whole numbers, the same formula works: Excel treats 20% as 0.2, and both the numerator and denominator are scaled by the same factor. Do not divide the weights by 100 again in the formula.
-
Format the answer to suit the values. For scores recorded on a 0-to-100 scale, a result of 87.95 is a score, not automatically 87.95%. Use the Home tab’s Number group to choose a number format and set the decimal places you want. If the values themselves are percentages, use Percent Style so the result displays as a percentage. Formatting changes how a result appears, not the calculation stored in the cell.
-
Verify the result using the weighted contributions. In the example, the scores contribute 88 × 20 = 1,760; 92 × 30 = 2,760; 76 × 25 = 1,900; and 95 × 25 = 2,375. Those contributions add to 8,795, and 8,795 divided by 100 is 87.95. The answer falls between the lowest and highest scores, as a weighted average with positive weights should. This quick check can reveal a mismatched range or an incorrect weight.
-
Extend the calculation when you add rows. If you add more observations, include the new value and weight cells in both ranges. Make sure the ranges remain the same length and stay aligned. For a list that will grow regularly, you can select the data and choose Insert > Table, confirming that the table includes headers if you have them. A table makes it easier to add and review rows; after adding data, check that the formula ranges cover the new rows. The Insert > Table command is available in Excel for Microsoft 365 on Windows, Mac, and the web, although the ribbon layout may vary slightly.
Common Mistakes
-
Using AVERAGE instead of weighting the values. A regular average treats every score or item equally, even when the weights differ. Use the weighted calculation when the inputs have different importance or frequency.
-
Mixing up row pairings. If a score is on one row but its weight is on another, Excel will calculate a result, but it will not represent the intended data. Sort the value and weight columns together, not separately, and check a few pairs before trusting the result.
-
Including labels or unrelated cells. Select only the numeric data rows. Including a header in one range, or using different starting or ending rows for values and weights, can cause an error or a misleading result.
-
Assuming weights must add to 100. They do not. The formula divides by their actual total, so weights of 2, 3, and 5 work just like 20%, 30%, and 50%. However, a zero total makes division impossible, and a negative or unintended weight may not make sense for an ordinary grade or share calculation.
-
Rounding the inputs too early. Keep the original precision in the cells and adjust the displayed decimal places instead. Rounding every value or weight before calculating can change the final result.
Tips
-
Use clear headers such as Score and Weight, and document what a weight means. A weight might be a percentage of a final grade, a number of units, or a count of responses; readers need that context to interpret the result.
-
For percentage weights, check that their total is the intended amount. A total of 100% is common when the weights describe shares of a whole, but weights based on counts or quantities may total something else.
-
Keep the calculation in a separate result cell rather than typing over a source value. This preserves the original data and makes it easier to audit or update.
-
When checking an unusually high or low result, review the largest weights first. A heavily weighted value can strongly influence the outcome, even if other values are very different.
-
If you copy the formula to another area, verify that the ranges point to the intended data. For a single summary calculation, keeping the formula beside the data is often clearer than copying it elsewhere.
Troubleshooting
-
The formula returns #DIV/0!. The total weight is zero, or Excel has no numeric weights in the selected range. Check that the weight cells contain numbers and that the range includes the intended rows. If positive and negative weights cancel each other out, reconsider whether those weights are appropriate for this type of average.
-
The formula returns #VALUE!. Look for text or error values in the ranges. A number stored as text can prevent the calculation from working as expected. Select the affected cells and use Excel’s warning option to convert them to numbers, or re-enter the values as numeric data. Also check that there are no errors in either source column.
-
The answer is not what you expected. Confirm the value and weight ranges start and end on the same rows. Recheck the pairings, the weight total, and the worked contribution for one or two rows. Make sure you have not used a weight in one column that actually represents a different measure, such as a score rather than a percentage.
-
The result displays as a date, a percentage, or too many decimals. The stored calculation may be correct while the cell format is not. On the Home tab, choose a suitable option in the Number group, then adjust decimal places. For a score of 87.95, use a number format; use a percentage format only when the underlying values represent percentages.
-
New rows are not included. Check the ranges in the formula after adding data. If the new entries are outside the selected ranges, expand both ranges to include them. If the data is in an Excel table, make sure the new row was added to the table rather than placed below it with a blank row in between.
Frequently Asked Questions
Do the weights have to add up to 100?
No. The calculation divides by the sum of the weights, so any consistent nonzero total works. A total of 100 is convenient when weights represent percentages, but it is not required for the formula to calculate a weighted average.
Can I use percentage weights in Excel?
Yes. Enter weights as percentages using Excel’s percentage format, or enter them as whole numbers such as 20 and 30. Use one scale consistently. The formula divides by the total weight, so you should not also divide percentage weights by 100 in a separate calculation.
What is the difference between a weighted average and an average?
An ordinary average gives every value equal influence. A weighted average gives more influence to values with larger weights. For example, a final course grade can give a major exam more influence than a short quiz, rather than treating both scores as equally important.
Can a weighted average include blank cells?
It is better to resolve blanks before calculating. A blank value paired with a nonblank weight may be treated differently from an intentional zero, and leaving out a value can change the result. Decide whether the observation is missing or actually zero, then update both the value and weight data consistently.
Can weights be negative?
Excel can calculate with negative numbers, but negative weights are usually inappropriate for common uses such as grades, product quantities, or response counts. They can make the result difficult to interpret or cause the total weight to be zero. Use negative weights only when the underlying method specifically calls for them.
Related
- How to Share Work in Excel and Collaborate in Real Time
- How to Build a Forecast Model in Excel
- How to Build a Debt Schedule in Excel Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- WORKDAY Formula in Excel: How It Calculates Business DatesExcel’s WORKDAY function returns a date a specified number of working days before or after a starting date,…
- How to Filter in Excel Using Keyboard Shortcuts and MenusTo filter a list in Excel for Microsoft 365 on Windows, select a cell in the data, press…
- How to Find the Lowest Value in Excel with the MIN FunctionIf your numbers are in B2:B7, enter =MIN(B2:B7) in an empty cell; Excel returns the smallest number in…
- DAX Formula in Excel to Remove Duplicates: What WorksThere is no DAX formula in an Excel worksheet that deletes duplicate rows. For a live list of…
- How to Filter Highlighted Rows in Excel by Cell ColorTo filter highlighted rows in Excel, turn on AutoFilter, open the dropdown for a column whose cells have…
- Find Duplicate Names in Excel: Formulas and ToolsTo find duplicate names in Excel, select the name cells and choose Home > Conditional Formatting > Highlight…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.