Excel provides powerful functions to calculate weighted averages. The most common method uses a combination of SUMPRODUCT and SUM functions.
Using SUMPRODUCT and SUM
The SUMPRODUCT function multiplies corresponding elements in arrays and returns the sum of those products. Combined with SUM, it creates the perfect weighted average formula.
=SUMPRODUCT(values, weights) / SUM(weights)
Step-by-Step Guide
- 1. Enter your values in one column (e.g., A1:A5)
- 2. Enter corresponding weights in another column (e.g., B1:B5)
- 3. In a new cell, enter the formula: =SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5)
- 4. Press Enter to see the weighted average result
Practical Example
Suppose you have test scores in cells A1:A3 (80, 90, 70) and weights in B1:B3 (1, 2, 3). The formula would be:
=SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3) = 78.33
Tips for Excel Users
- Make sure both ranges have the same number of cells
- Use named ranges for cleaner formulas
- The formula also works in Google Sheets with the same syntax