OpenOffice Calc weighted average

openofficeopenoffice-calc

I need to find the result of

((C1 * 5) + (C2 * 4) + (C3 * 3) + (C4 * 2) + (C5 * 1))/(5*6/2).

I need to do it for 200 columns, not only 5. I currently use two sheets with sheet 2 containing a column of 200 down to 1.

The formula is =SUMPRODUCT(C1:C200;$Sheet2.$C$1:$Shee2.$C$200)/((200*201)/2). Sheet 1 keeps moving down to accept new data. Sheet 2 remains fixed.

Can this be done without resorting to a sheet 2?

Best Answer

Since this is the second result on my Google Search of "LibreOffice Weighted Average" I may aswell give it an answer:

The Weighted Average can be calculated by the use of the function:

=SUMPRODUCT(values, weights) / SUM(weights)

Weight and Value arrays need to be of the same length

: they can be on the same sheet just use a different column,

: you can multiply column by column, column by row, row by column, row by array,...

: The order of weights applied to each value need to be in order in the two arrays you specify

Note: if weights are already normalized then you don't need to divide by the sum of the weights (you will be dividing by 1)

Related Question