Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Jun 28, 2022

It's certainly a good solution with Beta functions not available. Use of INDIRECT is not ideal but the workbook is not large enough where there might be a calculation crunch.

mtarler's avatar
mtarler
Silver Contributor
Jun 28, 2022

Patrick2788  I agree using INDIRECT() is not ideal and I tried to avoid it but couldn't find anything that I could get to work on the 3d (i.e. multi-sheet) reference range.  I have used CONCAT/TEXTJOIN type of functions on 3d but for example using TEXTJOIN with "," delaminated created a string >1500 long so the 'traditional' trick of replacing with spaces and then using MID() to then break it up errors out due to the length.  That said I came up with a cute variant that seems to work:

=LAMBDA(ref,
    LET(
        mm, "," & TEXTJOIN(",", FALSE, ref) & ",",
        ll, LEN(mm),
        cc, ll - LEN(SUBSTITUTE(mm, ",", "")) - 1,
        s, SEQUENCE(cc),
        sp, REPT(" ", ll),
        dd, TRIM(
            MID(
                SUBSTITUTE(
                    LEFT(SUBSTITUTE(mm, ",", sp, s + 1), ll),
                    ",",
                    sp,
                    s
                ),
                ll,
                ll
            )
        ),
        dd
    )
)

 I then wrapped that with a calculation:

=LET(mm, ToCol(AllMonthsRef),
     holidayloc,XMATCH(holidays,--mm,0,1),
     BYROW(staff,LAMBDA(n,LET(
             ws,SEQUENCE(ROWS(mm)/7,1,1,7),
             alldays,SUM(--(mm=n)),
             wkends,0.5*SUM(--(INDEX(mm,ws)=n),--(INDEX(mm,ws+6)=n)),
             holly,SUM(--(INDEX(mm,holidayloc+7)=n),--(INDEX(mm,holidayloc+14)=n)),
             alldays+wkends+holly)))
     *8*rate)

Resources