Google Sheet function to Excel 2016 function

[TonieeDang]

Hi everyone,

I am struggling with Goolgesheet function in cell H2 sheet Transaction OSV from this link


"=if($C2="","",iferror(if(row()<>2,INDEX(arrayformula(filter($I1:$I$2,$C1:$C$2<>"",row($C1:$C$2)=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ,1),0),0))"
converting to Excel 2016 function.

Really appreciate if you guys can have a look. Thank you.

[AliGW]

Welcome to the forum.

You are going to need to explain what the formula is required to do (in WORDS) and give us some expected results (manually calculated). Please don't expect members to work this out by reverse enginnering a formula. Thanks.

[TonieeDang]

My apology for not clearly explaining my issue.

In sheet Transaction_OSV, column H "cumulative units", the column will be used to filter stock by ticker in column C and make a sum calculation cumulatively the quantity of that stock has been acquired so far from the very beginning to the time another transactions have occured.
For example, a stock name Yahoo was bought first on 11 Jul 2016, it will show the results on another transaction of that stock on 14 Jul 2016 (row 13) which is 219.

The idea is that the H column will return all the cumulative amount of that stock has been acquired upto the time of later transaction.

I has tried to convert the Googlesheet formula to Excel 2016 using Index Aggregate function combining array but the result not correct.
"=IF($C2="", "", INDEX($I$1:$I$2, AGGREGATE(14, 6, ROW($I$1:$I$2)/(($C$1:$C$2=$C2)*($C$1:$C$2<>"")), 1)))"

Thank you for your advice and comment for the first thread Mrs. AliGW.

[AliGW]

Once again, please provide some manually-cacluclated expected results in the sample workbook and annotate the workbook for clarity.

[Phuocam]

"=if($C2="","",iferror(if(row()<>2,
INDEX(arrayformula(filter($I1:$I$2,$C1:$C$2<>"",row($C1:$C$2)
=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ,1),0),0))"
converting to Excel 2016 function.

Excel 2016 can try this:

=IF($C2="", "", IF(COUNTIF($C$2:C2,C2)=1,0,LOOKUP(2,1/($C$1:C1=C2),$I$1:I1)))

[TonieeDang]

Thank you very much. Appreciate for your help Mr. Phuocam.
Once again, thank you.