Converting google sheets formula to excel

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?

If F is easy I left it for you
For E2 I can suggest array formula

Formula:

=IFERROR(INDEX(F$1:F1,LARGE(IF(C$1:C1=C2,ROW(C$1:C1),""),1)),0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

it's basically the same array formula.
in sheet2 B3:

Formula:

=IFERROR(INDEX(Sheet1!F$1:F$10,LARGE(IF(Sheet1!C$1:C$10=A3,ROW(C$1:C$10),""),1)),0)

and copy down to B4
just a reminder - array formula.


BTW. My proposition for F2 in sheet1 would be regular formula (not array):

Formula:

=IF(B2="Buy",1,-1)*D2+SUMIFS(D$1:D1,C$1:C1,C2,B$1:B1,"Buy")-SUMIFS(D$1:D1,C$1:C1,C2,B$1:B1,"Sell")

and what is yours, as you not disclose it in the attachment, while may be somebody using http://www.excelforum.com/search.php will find this thread and may be for her/him it will not be "It is easy to write the formula for column F"?

Yes it's intentional
and nope, result wouldn't be blank.
C$1:C$10 can be empty, does not matter
but
ROW(C$1:C$10)
will return an array of row numbers 1,2,3...10 (despite of cells contents)
you could use
ROW(A$1:A$10)
or
ROW($1:$10)
as well.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.