Forum Discussion
Formula worked in google sheets but now doesn't work in Microsoft 365 - gSheets Arrayformula
I want to replicate in Excel the same workflow this Google Sheets has. There are 2 conflictive columns due to the gSheets function (arrayformula), which isn't in Excel.
The "previous units" column gives us the previous value that was added for the token found in column B.
This is the formula that can be found on the second row.
=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))
The other conflictive column is the column K, since the formula also uses the "arrayformula" function:
=if(C2="","",iferror(if(row()<>2,INDEX(arrayformula(filter($N1:$N$2,$C1:$C$2<>"",row($C1:$C$2)=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ,1),0),0))
Column H snapshot
Cell H4 formula
=if($C4="","",iferror(if(row()<>2,INDEX(arrayformula(filter($I$2:$I3,$C$2:$C3<>"",row($C$2:$C3)=max(if($C$2:$C3=C4,row($C$2:$C3),0)))) ,1),0),0))
Column K snapshot
Cell K4 formula
=if(C4="","",iferror(if(row()<>2,INDEX(arrayformula(filter($N$2:$N3,$C$2:$C3<>"",row($C$2:$C3)=max(if($C$2:$C3=C4,row($C$2:$C3),0)))) ,1),0),0))
Does anyone knows a turnaround for this?
wapc14 so in Excel 365 you don't need or use ARRAYFORMULA, it is just 'built in'. As for those formulas I think you should be able to just use TAKE(FILTER(),-1) like this:
=if($C2="","",iferror(TAKE(filter($I1:$I$2,$C1:$C$2<>"",0),-1),0)in addition to no ARRAYFORMULA please note that FILTER also has a different format (i.e. in sheets it takes an array of conditions while in excel all the conditions must be combined into 1 term and then followed by the if_none_found optional parameter) The TAKE function will specifically take the designated row(s) and in this case choosing -1 is the 1 row starting from the end.
I didn't test it but hope it works or at least gives you understanding how to make it work
