Formula to calc cost based on previous

I see that you have changed your title.

You can use this array* formula in cell I2:

=IF(B2="Buy",0,INDEX(E$1:E1,MAX(IF((C$1:C1=C2)*(B$1:B1="Buy"),ROW($1:1))))*D2)

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Then you can copy down using your usual method(s).

It looks like you had the wrong value in cell I5, as you are selling 75 with a unit price of 2.86.

Hope this helps.

Pete

Am I doing something wrong...for I5 with the formula I get 214.5. which should be 286.00 since that was my previous. Also if I buy multiple times before selling it gets inputted as 0?

Is there a way to get rid of all of it besides the gain/loss and have that formula do all the work?

286.0 was the total cost of buying 100 units, i.e. unit cost of 2.86. If you sell only 75 of them, then the original cost of that number is 75 * 2.86. You subsequently sell 25 units, so the original cost of those would be 25 * 2.86. If you take the first sale as 286.0 and add on to it the cost of 25 sales, then the total cost will be higher as you are basically counting part of the batch of 100 twice.

I could only work with the data that you supplied, which should be representative of the real file. If it is substantially different, then you should submit another file. I think it will be difficult, though, to match up with many previous "buys", as they may be at different rates.

Hope this helps.

Pete