Jun 25 2022 12:37 PM
For this exercise I stripped down a calendar template and put in some sample data. The goal is to obtain gross pay for July for 3 employees.
The data arrangement:
I believe there are several solutions and I welcome them all. My first take was using REDUCE but I thought I could be more efficient with another approach. My solution is included in the workbook. I may revisit the REDUCE solution after I've put some more thought into it.
Formulas only please (No fill handle). No VBA.
I'll mention a few who people might be interested in this exercise. All are welcome.
@PeterBartholomew1 @mtarler @Lorenzo @OliverScheurich
Jun 25 2022 03:20 PM
As variant
weekdayMultiplier=
LAMBDA( weekday, CHOOSE(weekday, 1.5, 1, 1, 1, 1, 1, 1.5) );
weekdayHours=
LAMBDA( vector, weekday,
SCAN(0, vector,
LAMBDA(a,v,
IF( v= 4, 16, // with simplified holidays
IF( v = "", 0,
IF( ISTEXT(v), a, 8*weekdayMultiplier(weekday)) ) ) ) )
);
weekdayPersonHours=
LAMBDA( vector, weekday, name,
SUM( weekdayHours(vector, weekday)*(vector=name) ));
monthPersonHours=
LAMBDA( month, name,
REDUCE(0, SEQUENCE(7), LAMBDA( a,v,
a + weekdayPersonHours( CHOOSECOLS( month,v), v, name) ))
);
GrossPay=
LAMBDA( month, names, rates,
SCAN(0, names, LAMBDA(a,v, monthPersonHours(month, v) ) )*
rates
);
Jun 25 2022 04:11 PM
@Patrick2788 Maybe I'm missing some of the challenge but I didn't use Lambda:
=(1.5*COUNTIFS(july,staff)-0.5*COUNTIFS(WkDays,staff)+COUNTIFS(holiday,staff))*8*rate
I added 2 Names: WkDays = the range of the month M-F and holiday = July 4
Jun 26 2022 02:39 AM
I was undecided whether to delicately unwrap the calendar format into meaningful arrays or to simply hit it with a blunt instrument, COUNTIFS in the present case. I came to the conclusion that the latter was sufficient, provided I was allowed to mark the holiday as a named range.
Worksheet formula
= rate * MAP(staff, Hoursλ);
Hoursλ = LAMBDA(s,
8 * SUM(COUNTIFS(holiday, s), dayRate * BYCOL(july, LAMBDA(day, COUNTIFS(day, s))))
);
dayRate = {1.5, 1, 1, 1, 1, 1, 1.5};
One thing that I would observe about Patrick's original solution, is that 'hours()' is a Lambda function and therefore will accept parameters directly from the helper functions without the use of an additional Lambda to pass parameters. I only realised that once I had reduced my own formula by naming its Lambda function to be "Hoursλ".
Jun 26 2022 02:18 PM - edited Jun 27 2022 10:14 AM
I think I made this exercise too easy. @mtarler struck it with a blunt instrument (COUNTIFS) and got the job done. @PeterBartholomew1 - I thought I made this one a pain with the holiday pay but you seem to have resolved this one easily working COUNTIFS into your LAMBDA.
I believe @SergeiBaklan has a solution that can be scaled to include 3D referencing.
I had to do it. I've updated the workbook, so it now includes 3 months. I've made some design changes: the number for the day of the week is now a true date and a list of holidays is included in the summary sheet.
Jun 27 2022 05:51 AM
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.
Jun 27 2022 09:26 AM - edited Jun 27 2022 11:51 AM
Thanks for catching that one.
I feel silly:
(I've replaced the workbook previously uploaded)
Jun 27 2022 08:05 PM
@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
Jun 28 2022 06:14 AM - edited Jun 28 2022 09:44 AM
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.
Jun 28 2022 09:31 AM
@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)
Jun 28 2022 09:48 AM - edited Jun 28 2022 09:51 AM
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) )
)
);
Jun 29 2022 09:21 AM
I have used your challenge as a further exercise. I note that the formula that Sergei introduced to restructure the 3D Range, that contains the calendar, is recalculated in its entirety once for each staff member. Provided the calculation is fast that is not a problem, but out of interest I set out to limit the calculation step to a single occurrence. That meant I could not use a defined Name or a Thunk to pass the restructured array 'stack'.
I could create a monolithic block of code and place the calculation of 'stack' before the MAP that determines the eligible hours for each staff member. To move away from such monolithic code, I wanted to use a further nested Lambda function. This could either be defined within the outer Lambda function by using LET, in which case it would be within scope of the formula-local name, or 'stack' itself could be passed as a parameter. I implemented each, but chose the second option.
GrossPayλ
= LAMBDA(calendar, names, rate,
LET(
stack, SORT(WRAPROWS(TOCOL(calendar, , 1), 3)),
rate * MAP(staff, EquivalentHoursλ(stack))
)
);
EquivalentHoursλ
= LAMBDA(stack,
LAMBDA(name,
LET(
dates, TAKE(stack, , 1),
holiday?, COUNTIFS(holidays, dates),
weekend?, WEEKDAY(dates, 2) > 5,
time, 1 + 0.5 * weekend? + holiday?,
assigned, TAKE(stack, , -2),
onshift?, BYROW(assigned, LAMBDA(s, OR(s = name))),
SUM(IF(onshift?, 8 * time))
)
)
);
In order to pass the additional parameter into the MAP construct I needed to define the function 'EquivalentHoursλ' in a Curried form, passing one parameter at a time by using multiple nested LAMBDAs.
I am sorry if this reads like complete gobbledegook; I have tried my best to express the ideas clearly, but I am not convinced I have succeeded!
Jun 29 2022 11:59 AM
Jun 30 2022 09:25 AM
Hi Patrick
I am not so sure. If anything, there is less of a need, given the new array shaping functions, for formulas relating indices in order to generate array transformations. I followed your link and posted a formula solution. It required 200ms to generate a 50,000 x 12 array of results.
The question of locating an element of an array is a little unusual to my mind. More often it is a case of mapping an entire array to a new layout, though one could conceivably play 'find the "X"'. One thing I have done to get a handle on some of the new array shaping functions is to apply them to a data set but, in parallel, to apply the same formula to an equivalent index array.
Then again, maybe this isn't what you are trying to achieve!!
Jun 30 2022 10:03 AM
Jun 30 2022 10:34 AM
Jun 30 2022 03:56 PM
I am not sure I fully understand the scenario and, it may well be that I am simply agreeing with Matt. That said, I see that helper functions like MAP and SCAN may perform calculations element by element without there being an obvious means of addressing adjacent elements of the array. In such a situation, my suggestion would be to scan an index array rather than directly scanning the target array. Elements of the target array could then be returned by use of the INDEX function, e.g.
= MAP(indexArray,
LAMBDA(k,
AVERAGE(
INDEX( targetArray, k+{0,1,2} )
)
)
)
Am I on the right track?
Jul 01 2022 01:33 PM
Yes, I think you and @mtarler have it correct. I have a way (SCAN with XLOOKUP using SEQUENCE for lookup array and return array) to reset sequential numbering (e.g 1,2,3,4,5,6,1,2,3, etc) but I don't believe it can be scaled for large data sets.
I still need to study your workbook from the destacking thread. When I'm at my desk at work I don't have access to an Insider build so I'm missing out during typical working hours!
Jul 02 2022 04:44 AM