Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
MVP
Jun 27, 2022

Patrick2788 

Yes, there are minor adjustments in previous solution to work with few months. The only, I have a bit different result with you. Calculated weekend hours for Bev

Didn't dig what and where exactly is wrong.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 28, 2022

    Patrick2788  without those new beta functions it seems harder (at least to me) so I added the sheet names and made a 2 level BYROWS() call:

    =BYROW(staff,LAMBDA(e,
   SUM(BYROW(SheetNames, LAMBDA(mm, 
      LET(sm, INDIRECT(mm&"!a3:g20"), 
         swd, INDIRECT(mm&"!b3:f20"),
         smb, INDIRECT(mm&"!a4:g21"),
         smc,INDIRECT(mm&"!a5:g22"),
         1.5*COUNTIF(sm,e)-0.5*COUNTIF(swd,e)
            +SUM(COUNTIFS(sm,holidays,smb,e)+COUNTIFS(sm,holidays,smc,e)) 
         )
      )))
   ))*8*rate
    • 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)
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jul 02, 2022
      Thank you for sharing. The way you approach this task is fascinating. The way I was taught to write code in VBA is avoid lengthy modules and split subroutines in smaller tasks and simply call them in another macro. Your LAMBDAs are like a quality Dutch coffee maker. If one part needs to be removed or replaced, it's easy to do so. Additionally, I do like the parameter names like 'vector' instead of single 'x' or 'y', for example.
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jul 02, 2022

        Patrick2788 

        Thank you, I only try to follow general principals

        1) engineering - avoid to combine few functions in one module, that decreases reliability and maintainability

        2) coding - function shall be self-explainable to avoid comments as much as possible

Resources