CODEWITHSUNDEEP
excelexcel-formuladynamic-arrays
Michael Suchy
Michael Suchy

Reputation: 13

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.

Upvotes: 1

Views: 1993

Answers (2)

DjC
DjC

Reputation: 1312

DISCLAIMER: Due to the limitations of lambda recursion in Excel, the methods outlined in the solution below should only be used on small datasets.

UPDATE: Bisection can be used to overcome recursion limits (see below).

With any of the built-in dynamic array functions like BYROW(), if the calculation performed on each iteration of the function produces an array of results, it will return the #CALC! error because "Nested Arrays" are not supported.

The canned solution you'll typically find as a workaround is to use some variation of REDUCE() with VSTACK() and/or HSTACK(). However, this method can be difficult to comprehend, and even harder to adapt to complex scenarios.

An alternative approach would be to create a custom LAMBDA() function in Name Manager that behaves exactly like BYROW(), but with the added benefit of being able to "stack" the results of nested arrays. To do this, open Name Manager (Ctrl + F3) and define a new name called STACKBYROW with the following formula:

=LAMBDA(array,function,[initial_value],[start],[pad_with],
   LET(
   n, IF(ISOMITTED(start), 1, start),
   f, function(INDEX(array, n, )),
   v, IF(ISOMITTED(initial_value), f, IFNA(VSTACK(initial_value, f), pad_with)),
   IF(n<ROWS(array), STACKBYROW(array, function, v, n+1, pad_with), v)))

The STACKBYROW() function can now be used in your workbook exactly as you intended to use BYROW():

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

If the results produce a "jagged array", the empty values will be filled with zeros (0) by default. Use the optional [pad_with] parameter to fill them with a value of your choosing, such as an empty string (""), if desired:

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

As for the additional example presented in the comment section above, you could use:

=STACKBYROW(A1:A2, LAMBDA(r, --TEXTSPLIT(r, ", ")),,, "")

stackbyrow.png

BONUS Example (with increased complexity):

To demonstrate the flexibility of STACKBYROW(), we can use it to solve the "Ticket List Challenge" presented by Wyn Hopkins of Access Analytic back in November 2022:

ticket_list_challenge.png

Solution:

=STACKBYROW(tblTicket, LAMBDA(r,
LET(
   n, INDEX(r, 1),
   v, --TEXTSPLIT(INDEX(r, 3),"-"),
   m, MIN(v),
   IFERROR(HSTACK(n, SEQUENCE(MAX(v)-m+1,, m)), n))),
{"Name","Ticket Number"})

Results:

ticket_list_solution.png

UPDATE:

To bypass the recursion limits imposed on custom LAMBDA functions in Excel, STACKBYROW could also be defined as follows:

=LAMBDA(array,function,
    IF(
        ROWS(array) = 1,
        function(array),
        VSTACK(
            STACKBYROW(TAKE(array, ROWS(array) / 2), function),
            STACKBYROW(DROP(array, ROWS(array) / 2), function)
        )
    )
)

Source: https://techcommunity.microsoft.com/t5/excel/recursive-lambda-implementation-of-excel-s-reduce-function/m-p/3949754#M207809

Upvotes: 6

user22566114
user22566114

Reputation: 391

=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

Upvotes: 0

Related Questions