Monday, February 13, 2012

How to Calculate a Weighted Average in Excel 2007


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.

Blogger news