calculate averages of prices

[NikBMO]

hello,

I have a spreadsheet which contains share prices that go years back and also gets updated with new ones. I would like to get a function that calculates the average share price per month whenever I update my spreadsheet. I was thinking of an average function whose arguments are cell addresses but I cannot make it work.

The spreadsheet has columnds like this and the every time a new row appears with an updated share price

14/10/2014 $60
....
5/3/2010 $50
...
1/1/2004 $55

Can anyone advise?

[Bob Phillips]

You need to give a manually calculated example, your info is far too little.

[NikBMO]

Apologies for not making it clear. Column B gives the date of the stock price and column C the closing price. There are multiple stocks that go a few years back so this is an example for one stock. Every time I update the spreadsheet (Using a Bloomberg built in code) the older prices move to the rows below and the new ones appear on top. For this reason, I will need to use some sort of lookup function in order to calculate the average price every month.

Column B Column C
10/10/2014 254.02
09/10/2014 251.66
08/10/2014 255.09
07/10/2014 258.23
06/10/2014 259.63
03/10/2014 261.88
02/10/2014 264.55
01/10/2014 265.81
30/09/2014 265.17

So in order to calculate say the June 2014 average price, I thought of doing: =Average(CELL("address",vlookup(02/06/2014,$B$1:$c$10000,2,false)):CELL("address",vlookup(30/06/2014,$B$1:$c$10000,2,false)) but it does not work. My reasoning was, using the look up function, to find the cells of beg and end days of each month and then put then in the average formula to find the average price. Hope this is clear, can anyone advise?