Suming with criteria in cronilogical order

Please help.

I'm trying to work this out and can't figure it out. I would like a formula that i can drag down the "Current Holdings and Gain/loss" Columns that will give the following results....

Date Name Type Quantity Price Current Holdings Gain/Loss
1/1/2001 Stock a Initial 1000 1 1000 0
1/2/2001 Stock a Sell 100 2 900 100
1/3/2001 Stock a Buy 100 2.5 1000
1/4/2001 Stock a Sell 200 2 800 50loss+ 100Gain
1/5/2001 Stock a Sell 800 2 600 800
1/1/2001 Stock b Initial 1000 1000
1/2/2001 Stock b Buy 200 1200
1/3/2001 Stock b Sell 150 1050
1/4/2001 Stock b Buy 150 1200



Thanks!

Hi exclusiveicon

I was going to say that you cannot get a formula to do this, but then I thought that was a very arrogant thought, so let me say that I do not know how to write such a formula.

I'm not sure whether you realise, but you are asking for a LIFO system (Last in First Out) and this is (for me) the incredibly complicated bit. I could write some VBA to handle it, but not a formula.

If you are prepared to accept an average system (whereby the selling price is compared to the average price of the stock), I have written some formulas that will do the job. (Note that it is important to have a blank row between each type of stock)

Let me know how you get on

Regards
Alastair