Search code examples
excelexcel-formuladynamic-arrays

Using BYROW with dynamic arrays as formula inputs

I have a column in an excel table that contains the following formula: =ARRAYTOTEXT(HSTACK(MAKEARRAY(1,[@[Liquidation Lag]],LAMBDA(r,c,0)),TAKE(VALUE(TEXTSPLIT([@[Default Vector]],", ")),,[@[Months to Project]]-[@[Liquidation Lag]])))

On another worksheet, I would like to use BYROW to dynamically spill an array that extracts the embedded array within the ARRAYTOTEXT portion of the formula and spills it column-wise. This is the formula I'm trying to use:

=BYROW(lossModel_tbl[Liquidation Vector],LAMBDA(r,VALUE(TEXTSPLIT(r,", "))))

I've tried various iterations of this that have involved makearray, sequence, index, etc. and I always come up with either #calc! or #value! errros.
Any suggestions would be appreciated.

Solution

  • =NUMBERVALUE(IFNA(DROP(REDUCE("",SEQUENCE(ROWS(A1:A4)),LAMBDA(x,y,HSTACK(x,TOCOL(TEXTSPLIT(INDEX(A1:A4,y),","))))),,1),"0"))

    This formula returns the intended result if i correctly understand what you want to do. BYROW can't be applied since it can't spill horizontally and vertically at the same time.

    Using REDUCE with dynamic arrays as formula inputs