Conditional weighted average

Hi all,

I'm trying to calculate the average weighted entry price of a portfolio of stocks.

However, imagine I have a list of chronological transactions and that eventually I could exit a position and then start again.
(i.e. I could sell my stocks on company A and then buy again later on)

The calculations of my average entry price should only include the transactions after I started the new position.
If simply "do the math" Excel will consider all transaction and calculate the weighted average for ALL transactions instead for the relevant ones. How can I include a condition for only averaging the appropriate transactions?

I've created a sample file that shows the problem. Could anyone please take a look?

Thanks in advance!
Bruno

Hi Dave, thanks a lot for the help.

It needs to be a weighted average to calculate the average price of the amount held - your solutions works fine for the case with the stock A but it doesnt for stock C, which should be a weighted average of the trades - 3000 @ 150 + 3000 @ 170 = 6000 stocks @ weighted average price of 160.

Any thoughts?

Thanks again.