I have the following Google Sheet's formula (Column I) - =if(B4<>"Deposit", iferror(if(row()<>2,INDEX(arrayformula(filter($J$2:$J3,$C$2:$C3<>"",row($C$2:$C3)=max(if($C$2:$C3=C4,row($C$2:$C3),0)))) ,1),0),0), "")
- I want to take it and put it in to Excel but Excel doesn't like the FILTER() funtion
- Can you help me translate this? I will explain more below.
2020-03-31 12_25_02-Robinhood Portfolio - Google Sheets.png
Basically what Column I does is track how many shares of a Stock I buy/sell. So in this example, if you look at CGC on 2/12/2019 - I buy 125 shares. This is the first time I bought CGC so the Previous Units (Column I) should be 0 and the Cumulative Units (Column J) should be 125. When I Sell is on 4/18/2019 it Column I should be 125 and Column J should be 0.
Another scenario is buying more of a stock I already own. Look at the Boeing example in the Excel spreadsheet highlighted in Yellow. If I buy BA again before I sell it, Column I should update from Column J of the previous buy date and Column J for the new buy should update with the new shares bought (column D) added to the previous shares Column I.
Please let me know if this makes sense. I've attached the excel spreadsheet as well.
Bookmarks