Forum Discussion
Spilled Array based on different Time Periods and applied periods - Help Needed
I see what you mean now. Nice use of the MOD function to handle out-of-scope references. There's a lot of ways one could spin this. My first thought was to simply focus on a single cell and return the sequence of dates used for that calculation, so if a particular period was not flagged as expected, you could just peek under the hood and see what went wrong. I played around with it some more to get all of the flagged items in the selected row highlighted (different color), but the "focus" is still on the target item. I also added a checkbox to toggle the picklists between all row/column numbers and only those containing flagged items.
Also, just for fun, here's another variant using lori_m's bi-row method to convert the array of thunks into an array of arrays, then MMULT to return the final output:
=LET(
periodStart, G2:R2,
periodEnd, G3:R3,
interval, B5:B12,
startDate, C5:C12,
endDate, D5:D12,
rowId, SEQUENCE(ROWS(interval)),
duration, ROUNDUP(((YEAR(endDate)-YEAR(startDate))*12+MONTH(endDate)-MONTH(startDate)+1)/interval,0),
seqλ, MAP(startDate,duration,interval,rowId,LAMBDA(sd,d,i,n,
LAMBDA(LET(a,IFERROR(EDATE(sd,SEQUENCE(d,,0,i)),-1),HSTACK(IF({1},n,a),a))))),
biλ, LAMBDA(x,λ,IF(ROWS(λ)=1,(@λ)(),VSTACK(x(x,TAKE(λ,ROWS(λ)/2)),x(x,DROP(λ,ROWS(λ)/2))))),
arr, biλ(biλ,seqλ),
rId, TAKE(arr,,1),
seq, DROP(arr,,1),
IF(MMULT(--(TOROW(rId)=rowId),(seq>=periodStart)*(seq<=periodEnd)),1,0)
)
I think this method would be more efficient overall because it generates a single array containing all sequences, then MMULT (which is highly efficient) handles the rest in one go. My previous formula using MAP with broadcasting had to iterate through every row/column and process each calculation one at a time. I didn't actually conduct a speed test but have my suspicions.
Note: REDUCE-VSTACK could have also been used, but the bi-row method appears to be much faster and more robust (tested and confirmed in various other scenarios).
See attached...
It is interesting to see the differences in style as well as substantive differences of approach. It appears that you wish to keep the entire calculation, including the definition of the seqλ lambda function visible on the worksheet formula, whereas it tend to delegate areas of functionality to named functions. I avoid using LET to name input ranges and instead use defined names to conceal the raw range references. I stopped using direct cell referencing in 2015 (a move that most find incomprehensible) because I had no interest in where the referenced data was located, I just wanted the data linked to real world objects it represents. In the present context, the LET variable creates an additional copy of the data in memory, which may have both pros and cons.
I like the manner in which you broadcast the final resolution of the array of thunks; it is really elegant, even if the way you pass λ as an array parameter comes as something of a shock! So much nicer than INDEX/MAKEARRAY. I remember Lori Miller showing the approach to be one of the fastest until such time as Microsoft pull their finger out and address the array of array problem.
What I tend to do is use modularisation to hide complexity. For example, in the version of the solution I intend to attach to this post, I have introduced a function EVALTHUNKARRλ that attempts to evaluate arrays of thunks without any reference to how they were created. The individual element thunks are not constrained to give scalars when evaluated; in one case they evaluated to give actual arrays with 100 000s of elements.
The problem with my approach is that, although it allows the complex bits to be used as black boxes, debugging can be a nightmare when you are 3 deep in a calling stack!
/* FUNCTION NAME: "EVALTHUNKARRλ"
DESCRIPTION: "Called by modified helper functions to stack the contents of any array of thunks" */
/* REVISIONS: "Date Developer Description
14 Oct 2024 Peter Bartholomew Extracted for MAPλ as a separate module"
*/
EVALTHUNKARRλ = LAMBDA(thunkArrayϑ,
LET(
m, ROWS(thunkArrayϑ),
n, COLUMNS(thunkArrayϑ),
h, SEQUENCE(CEILING.MATH(LOG(n,2),1)),
recombinedRowsϑ, IF(
n > 1,
BYROW(thunkArrayϑ, LAMBDA(thunkRowϑ, @REDUCE(thunkRowϑ, h, JOINPAIRSλ(1)))),
thunkArrayϑ
),
k, SEQUENCE(CEILING.MATH(LOG(m,2),1)),
recombinedϑ, IF(
m > 1,
REDUCE(recombinedRowsϑ, k, JOINPAIRSλ(0)),
recombinedRowsϑ
),
result, IFNA((@recombinedϑ)(), ""),
result
)
);
/* FUNCTION NAME: "JOINPAIRSλ"
DESCRIPTION: "Called by EVALTHUNKARRλ to stack the contents of thunks pairwise" */
/* REVISIONS: "Date Developer Description
09 May 2024 Peter Bartholomew Original Development
16 May 2024 Peter Bartholomew Test for unpaired thunk in binary tree
30 Aug 2024 Peter Bartholomew Modify to stack horizontally or vertically"
*/
JOINPAIRSλ = LAMBDA([by_col], LAMBDA(thunkArray, [k],
LET(
alternate, WRAPROWS(thunkArray, 2, thunk("\")),
firstpart, TAKE(alternate, , 1),
finalpart, TAKE(alternate, , -1),
MAP(
firstpart,
finalpart,
LAMBDA(ϑ₁, ϑ₂,
LET(
x₁, (@ϑ₁)(),
x₂, (@ϑ₂)(),
v, IF(@x₂ = "\",
x₁,
IF(by_col, HSTACK(x₁, x₂), VSTACK(x₁, x₂))
),
THUNK(v)
)
)
)
)
));
THUNK
= LAMBDA(x,LAMBDA(x));