How to Calculate a Weighted Average in Excel 2007

Microsoft Excel has various mathematical formulas and equations that can be used on rows and columns of values in a spreadsheet. By using a combination of two such functions, you can calculate the weighted average of a group of numbers on your spreadsheet.

Instructions

1. Enter your two sets of values that you wish to find the weighed average for into your spreadsheet, separating them into two columns. For the sake of this example, assume that the values in the first column stretch from A1 to A5 and the values in column B stretch from B1 to B5.

2. Type the following formula into a blank cell, cell C1, without quotes: "=SUM(B1:B5)" This formula will calculate the sum of the second column of numbers.

3. Type the following formula into another blank cell, cell C2, without quotes: "=SUMPRODUCT(A1:A5,B1:B5)" This formula calculates the total amount paid into the first and second columns.

4. Type the following formula into a final blank cell, cell C3, without quotes: "=C2/C1" This calculates the total weighted sum for the first column when compared to the second column.

References

• Microsoft: Calculate a weighted average

Resources

• Read this Article in Spanish