Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 28, 2022

Patrick2788 

This is not an independent solution since I started of by examining SergeiBaklan's solution and only then started to play.  The initial manipulation of the 3D Range is truly amazing, I am only beginning to get to grips with some of the new array shaping functions.

 

I liked Sergei's use of nested Lambda functions; I am moving towards using such functions wherever possible rather than wherever necessary to improve the structure and self-documenting character of solutions, but I have not got there yet!  I did think the use of SCAN to multiply the hourly rates was something of overkill though.

 

My formulas were

 

 

Worksheet formula
= rate * MAP(staff, BillableHoursλ);

BillableHoursλ=
LAMBDA(name,
    LET(
        stack,      SORT(WRAPROWS(TOCOL(calendar, , 1), 3)),
        dates,      TAKE(stack,,1),
        holiday?,   COUNTIFS(holidays,dates),
        weekend?,   WEEKDAY(dates,2)>5,
        time,       1 + 0.5*weekend? + holiday?,
        staff,      TAKE(stack,,-2),
        onshift?,   BYROW(staff,LAMBDA(s,OR(s=name))),
        SUM( IF(onshift?, 8*time) )
    )
);

 

 

Resources