How to calculate the weighted average in Excel

In this article from .com we want to teach you how to do the weighted average with Excel or OpenOffice . Microsoft Excel has several formulas that you can use over rows and columns of values ​​in a spreadsheet. Using a combination of two of these functions, you can calculate the weighted average of a group of numbers in your spreadsheet.

one

As you can see in the image you must enter the numbers you want to calculate in two rows. In this example, the values ​​in the first column go from cell B2 to B6, and the values ​​in column C go from cell C2 to cell C6.

two

Continue calculating the weighted average by writing in an empty cell, for example in C10. Write without the quotes: "= SUM (C2: C6)". This formula will calculate the sum of the numbers in the second column.

3

Write the following formula in another empty cell, cell C11, without the quotes: "= SUMAPRODUCT (B2: B6, C2: C6)". This formula calculates the total of the first and second columns.

4

Write the following formula in the empty end cell, cell C12, without the quotes: "= C11 / C10". This calculates the total of the weighted sum of the first column when compared to the second column. As you can see in the image in our example, the result of the weighted sum is 4.58.

5

If you want to calculate the weighted sum with openOffice you can also do the only thing that the formula changes and instead of SUM you should put SUM in all the formulas.