Forum Discussion
A LAMBDA Exercise
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!
- Patrick2788Jun 29, 2022Silver ContributorThe more I study stacking (and unstacking), the more I believe there's a need for a function capable of determining the position of an element in an array (Not a range but an array). The other day someone had requested a formula to 'un-stack' a range. Worked on it bit (Explored pulling row and column with MAP from a mirrored range) but it kept coming back to finding the position within in the array. I saw L Z's tidy solution and moved on.
https://techcommunity.microsoft.com/t5/excel/how-to-build-a-destacked-table/m-p/3558426
ROW and COLUMN do not work on arrays and there's only so much XMATCH can do as arrays get larger and it won't calculate. I believe SCAN can sometimes get me the element position but it seems like a lot of heavy lifting.
Maybe the function would be called ELEMENT and would return position number within a vertical or horizontal array,- PeterBartholomew1Jun 30, 2022Silver Contributor
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!!
- Patrick2788Jun 30, 2022Silver ContributorI'd have to step you through my thinking in re: De-stacking.
Essentially, I was working on a solution where I had created an array and then needed to check each element in the array to determine if it would be a 1 or 0. I was using MAP with a dummy range of identical dimensions to obtain Row and Column. The problem was getting the array to know when to stop filling in 1s.
For example. Item 1 shows 3. By the time I get to the 4th position in the array, I need to tell it to stop filling in 1s even though it's still concerned with Item 1.
I hope that makes sense. I may revisit de-stacking with a clearer head.