Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a weighted average in Excel and Google Sheets
Topic:RANK & statistical aggregatesExcel & Google Sheets
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Item | Value | Weight |
| A | 10 | 2 |
| B | 20 | 3 |
| C | 30 | 5 |
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)→23
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your data into two columns: one for the values and another for their corresponding weights.
- 2Select an empty cell where you want the weighted average to appear.
- 3In Excel or Google Sheets, use the formula =SUMPRODUCT(values_range, weights_range)/SUM(weights_range).
- 4Replace 'values_range' and 'weights_range' with the actual cell ranges containing your data.
- 5Press Enter to calculate the weighted average.
Tips
- Ensure that the ranges for values and weights are of the same size to avoid errors.
- Check for any zero or negative weights as they can skew your results.
- Locale settings might affect the formula syntax; ensure consistency with decimal and comma usage.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What happens if my weights sum to zero?
If the sum of weights is zero, the formula will return a #DIV/0! error. Ensure that your weights add up to a non-zero value.
Can I use this method for more than two criteria?
Yes, you can extend the SUMPRODUCT and SUM functions to include additional criteria by adding more ranges to the formula.
Is there a difference between Excel and Google Sheets for this formula?
No, the SUMPRODUCT and SUM functions work identically in both Excel and Google Sheets for calculating weighted averages.
More on RANK & statistical aggregates
See all →Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
How-toHow to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
ReferenceRANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
How-toHow to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
ReferencePERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-toHow to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.