Loop through table column with spill formula

[MichielS340]

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 this?

many thanks,

[#2 · Accepted solution]

@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

[#3]

@MichielS340 

The problem arises on the line

    LET(
        ...
        LijstWerkdagen, WORKDAY(StartDag - 1, SEQUENCE(NettoWerkdagen)),

where SEQUENCE( ) generates an array of days which BYROW( ) then attempts to assemble into an array of arrays.  While I was working through the formula, I downloaded a Lambda (helper) function 'MAPλ' that I only finished writing last week (maybe some checking required).

That would at least allow you to look at numbers rather than error messages.

[#4]

@MichielS340 

@Patrick2788 approach with MAP:

=LET(
  thunk,  LAMBDA(x, LAMBDA(x)),
  thunks, LAMBDA(begin,eind,hours,
    LET(
      NettoWerkdagen, NETWORKDAYS(begin, eind),
      LijstWerkdagen, WORKDAY(begin - 1, SEQUENCE(NettoWerkdagen)),
      UurPerDag,      hours / NettoWerkdagen,
      thunk(
        HSTACK(
          LijstWerkdagen,
          EXPAND(UurPerDag, NettoWerkdagen, , UurPerDag)
        )
      )
    )
  ),
  Mapped,   MAP(Table2[Begin],Table2[Eind],Table2[Hours], thunks),
  StkThks,  LAMBDA(acc,rw, VSTACK(acc, INDEX(Mapped, rw, 1)()) ),
  DROP( REDUCE(0, SEQUENCE(ROWS(Table2)), StkThks), 1)
)

[#5]

Many thanks, I need some time to digest, but it works quite elegant!

[#6]

Thanks for your input!

[#7]

You're @MichielS340 

All credits due @Patrick2788. MAP avoids OFFSETing (cf. your initial formula), passing your 3 required arrays ([Begin], [Eind], [Hours]) to the function 

[#8]

@MichielS340 

As variant

=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 )
)

and Insert->Checkbox on the top (if you are on Current channel)

[#9]

@MichielS340 

I have tidied my previous solution up a bit by modifying my MAPλ Lambda (helper) function to accept the 3 input arrays in addition to the user-provided Lambda function that does the work.  My worksheet formula reduces to

= MAPλ(Table2[Begin], Table2[Eind], Table2[Hours], UurPerDagλ)

where the user Lambda function is given by

UurPerDagλ
= LAMBDA(StartDag, EindDag, UurPerDag,
    LET(
      NettoWerkdagen, NETWORKDAYS(StartDag, EindDag),
      LijstWerkdagen, WORKDAY(StartDag - 1, SEQUENCE(NettoWerkdagen)),
      UurPerDag,      AantalUur / NettoWerkdagen,
      return,         HSTACK(
                        LijstWerkdagen, 
                        EXPAND(UurPerDag, NettoWerkdagen, , UurPerDag)),
      return
    )
  )

(I have little idea whether this is the intended calculation).

Like the other solutions, this one uses thunks, so building an array of functions, rather than an array of arrays which causes Excel to error.  A key feature of the approach is that the thunks are formed without involving the user; the user provides a perfectly standard Lambda function that performs the calculation for a single cell from each of the input arrays. 

Something else that would be important if 1,000s of outputs were required, is that the stacking of calculated thunks is performed by assembling the result arrays as a binary tree rather than relying of REDUCE/VSTACK.  This can be 10 or 20 times faster for large dimension input arrays as well as concealing the complications from the end user.

The updated gist is to be found at

A version of Excel MAP helper function that will return an array of arrays (github.com)

You are welcome to look at it, but the intention is that it may be used without inspecting the content, since the expertise available within this discussion is far from 'normal'.

[#10]

@PeterBartholomew1 🙇‍:male_sign:unbelievable