Weighted Average or (Weighted Mean):

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: