Loop through table column with spill formula

Hi, I get a nested array calculation error if I try to loop through the rows and for each row prepare a list of working days. See attached file. Anyone a suggestion how I can work my way around ...

Book1.xlsx15 KB

excel

Formulas and Functions

Patrick2788

Sep 08, 2024

MichielS340

You're receiving the #CALC! error because BYROW is designed to return a scalar for each row - 7 rows, 7 scalars.

One approach is to use thunks. For example,

=LET(
    i, SEQUENCE(ROWS(Table2)),
    thunk, LAMBDA(x, LAMBDA(x)),
    thunks, BYROW(
        Table2[Begin],
        LAMBDA(x,
            LET(
                StartDag, x,
                EindDag, OFFSET(x, , 1),
                AantalUur, OFFSET(x, , 2),
                NettoWerkdagen, NETWORKDAYS(StartDag, EindDag),
                LijstWerkdagen, WORKDAY(StartDag - 1, SEQUENCE(NettoWerkdagen)),
                UurPerDag, AantalUur / NettoWerkdagen,
                thunk(
                    HSTACK(
                        LijstWerkdagen,
                        EXPAND(UurPerDag, NettoWerkdagen, , UurPerDag)
                    )
                )
            )
        )
    ),
    DROP(REDUCE("", i, LAMBDA(acc, v, VSTACK(acc, INDEX(thunks, v, 1)()))), 1)
)

There are several discussions in this forum that cover thunks thoroughly. This site is a good primer for getting started:

What is a thunk in an Excel lambda function? - FLEX YOUR DATA

=LET( start, MIN( INT(Table2[Begin]) ), end, MAX( INT(Table2[Eind]) ), days, SEQUENCE(, end-start+1, start ), check, IF( (days >= INT(Table2[Begin]))*(days <= INT(Table2[Eind])), TRUE ), VSTACK( days, check ) )

Forum Discussion

Loop through table column with spill formula

Resources