hello gentlemen!
I've a sticky problem at hand...
I need to calculate the average cost per share in a spreadsheet.
The average price works wonders when I only Buy stocks but the moment I sell stocks it changes my average cost of shares.
Basically I need to take the shares I sell out of the calculation...
My current formula is this one:
=SUMPRODUCT($G$10:$G$65000,$L$10:$L$65000,(SUBTOTAL(3,OFFSET($G$10,ROW($G$10:$G$65000)-MIN(ROW($G$10:$G$65000)),,))))/SUBTOTAL(9,$G$10:$G$65000)
I didnt write it myself but ti works when I Buy shares. I tweaked it (I dont understand what the subtotal() does exactly. it uses a counta() method)
For info:
Column G = Quantity (number of shares)
Column L = Price
now I need to be able to customize this formula so that it takes into account only the stocks that are Bought (Column E stores "B" or "S")
Can someone tell me how to tweak the formula so my average cost of share doesnt take into account the quantity and price in the calculation when Column E = "S" please?
Thanks in advance!
I've a sticky problem at hand...
I need to calculate the average cost per share in a spreadsheet.
The average price works wonders when I only Buy stocks but the moment I sell stocks it changes my average cost of shares.
Basically I need to take the shares I sell out of the calculation...
My current formula is this one:
=SUMPRODUCT($G$10:$G$65000,$L$10:$L$65000,(SUBTOTAL(3,OFFSET($G$10,ROW($G$10:$G$65000)-MIN(ROW($G$10:$G$65000)),,))))/SUBTOTAL(9,$G$10:$G$65000)
I didnt write it myself but ti works when I Buy shares. I tweaked it (I dont understand what the subtotal() does exactly. it uses a counta() method)
For info:
Column G = Quantity (number of shares)
Column L = Price
now I need to be able to customize this formula so that it takes into account only the stocks that are Bought (Column E stores "B" or "S")
Can someone tell me how to tweak the formula so my average cost of share doesnt take into account the quantity and price in the calculation when Column E = "S" please?
Thanks in advance!