I am trying to convert a formula from a google sheets template to excel. Unfortunately the author has not had the time nor the knowledge of how to achieve this hence I am seeking anyone's expertise here. The formula is :
=iferror(if(row()<>2,INDEX(arrayformula(filter($E$2:$E4,$C$2:$C4<>"",row($C$2:$C4)=max(if($C$2:$C4=C5,row($C$2:$C4),0)))) ,1),0),0)
Assume formula is on cell E5
B C D E F
Buy AAPL 1000 0 1000
Buy GOOG 300 0 300
Sell AAPL 200 1000 800
Buy GOOG 200 300 500
Sell GOOG 100 500 400
E Previous Units
F Cumulative Units
In effect what the formula seeks to achieve is calculate the sum of the holdings. ie result would show the number of APPL shares which is 800 shares and GOOG shares would be 400. It is easy to write the formula for column F however it is difficult to write the formula collating the sum of previous units in column E.
How would I approach this to achieve the same outcome in excel?
Bookmarks