I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim