Average share (opening) price

[JackDanIce]

Hi,

I have the following data, where a negative number represents a sell of shares and a positive number a purchase and column C shows at what price the transaction happened.

	A	B	C	D	E	F
Ticker	Quantity	Price		Ticker	Average Price
TSCO LN Equity		VOD LN Equity	?
TSCO LN Equity		TSCO LN Equity	?
TSCO LN Equity
TSCO LN Equity
TSCO LN Equity
TSCO LN Equity
VOD LN Equity
TSCO LN Equity
TSCO LN Equity
VOD LN Equity
VOD LN Equity
VOD LN Equity
VOD LN Equity
VOD LN Equity

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 104px"><COL style="WIDTH: 98px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]50 [/TD]
[TD="align: right"]323[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]-30 [/TD]
[TD="align: right"]330[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]60 [/TD]
[TD="align: right"]300[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]300 [/TD]
[TD="align: right"]325[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]-5 [/TD]
[TD="align: right"]360[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]200 [/TD]
[TD="align: right"]310[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]-50 [/TD]
[TD="align: right"]190[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]10 [/TD]
[TD="align: right"]350[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]-40 [/TD]
[TD="align: right"]320[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]25 [/TD]
[TD="align: right"]150[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]25 [/TD]
[TD="align: right"]155[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]-150 [/TD]
[TD="align: right"]180[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]-200 [/TD]
[TD="align: right"]170[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]40 [/TD]
[TD="align: right"]130[/TD]

</TBODY>

And I've been asked to calculate the average price per ticker. For now it doesn't matter whether it's based on a first in first out principle or first in last out principle.

Has anyone got a formula to calculate this?

Thanks in advance,
Jack

 

[JackDanIce]

Thank you drsarao, that seems to work, although now I'm being asked to calculate something slightly different (though related to this), so need to ask a new question. Appreciate your time and replies

 

[drsarao]

You are welcome!
Thanks for the feedback.