Hi,

Want to know whether I can use formulas to calculate profit/loss on sales of stocks based on FIFO - First In First Out - or does it need VBA.

I have my stock 'Transactions' sheet set up as below:

Date Stock Action Qty Price Brokerage Trade Value

'Stock' would be short name for the stock involved.
'Action' would be either Buy or Sell.
'Trade Value' would be calculated as Qty*Price+Brokerage if Buy, Qty*Price-Brokerage if Sell.

Ideally, I would like a column after the above columns indicating the gain or loss if Action is Sell. Is it possible using Array/other formulas?

Thanks,
Vivek