Weighted Average or (Weighted Mean):
October 16, 2012 Leave a comment
An Arithmetic Average that takes into account the importance of the items making up the average. In calculating the value of a share index, the share price are usually weighted in the some way, most often by the market capitalization of the company.
In Google Docs Spreadsheets Average function return the average of a range of data. if you want to calculate a weighted average there is no direct function to calculate this, so we have to manipulate this using the some the native function available in google native functions.
Suppose a trader buys commodity on four occasions -100 tonnes at 70 per tonne, 300 tonnes at 80 per tonne, 50 tonnes at 95 per tonne and 60 tonnes at 75 per tonne. the purchase total 510 tonnes. the Simple average price would be (70+80+50+60)/4=65. However, the weighted average, taking into account the amount purchased on each transaction is
[(100 X 70) + (300 X 80) + (50 X 95) + (60 X 75)]/510=78.92
In the Spreadsheet this can be done using Sumproduct function and Sum function
in cell D8 we have used the basic formula as show the above example.
to get the single cell formula we have used the Sumproduct and sum formula to get the weighted average as show in the Cell C11.
In Cell C10 we have average formula