Forum Discussion
Halloween 2026 Scheduling Challenge 🎃
Struggling with this website... what a joke.
Trying again...
= LET(
sch, SCAN(
VALS3(HSTACK(available, treat)),
SEQUENCE(SUM(ROUNDUP(available / items_per_turn, 0))),
LAMBDA(acc,_,
LET(
arr, SORTBY(acc(1), TAKE(acc(1), , 1) = 0),
bal, SINGLE(arr),
amt, MIN(bal, items_per_turn),
VALS3(SORTBY(IFNA(EXPAND(bal - amt, 2, 2), arr), rank = 1), amt, INDEX(arr, 1, 2))
)
)
),
end, SCAN( , MAP(sch, LAMBDA(val, val(2))), SUM),
VSTACK({"Treat","Start","Stop"}, HSTACK(MAP(sch, LAMBDA(val, val(3))), DROP(VSTACK(0, end), -1), end))
)Where:
VALS3 = LAMBDA([value1],[value2],[value3], LAMBDA(x, CHOOSE(x, value1, value2, value3)))Note: SINGLE(arr) should automatically convert to "at"arr when the formula is committed to a cell. I used SINGLE because implicit intersection operators contained within a code block don't seem to be rendering properly in Microsoft Edge after posting.
This solution probably wouldn't perform all that great with a huge table/array of treats, due to the manipulation and indexing of the entire array at each iteration, but it works well for the dataset provided.
A step-through is available in the attached workbook to review the accumulated items for any given iteration (assuming the workbook is not stripped from my post again!).
Happy Halloween! 👻
(first solution)
This is the slipping a thunk-through-a-keyhole method! For me it's not readily apparent what's going on until I sit down and really step through the calculations that I can appreciate the mental model being used. It takes a lot of skill to create 'sch' and not have it realized until you iterate over it with MAP and then you're pulled out what you need and assembling the result.
This is an interesting approach I've used in moving aggregation:
∂, M3:M10 - SEQUENCE(, ROUNDUP(MAX(M3:M10) / 2, 0), 2, 2),The only downside is creating and then discarding extra values when a treat is out of stock. Given the limitations of the current build (Nested arrays not yet in general release), it's a valid approach and the calculation times are generally good.
My favorite part is how you setup SCAN with an array of 2s and 1s. Very nice!
run, SCAN(0, 1 + (qty >= 0), SUM),Thanks Patrick2788​ for you feedback.
Nested arrays not yet in general release
Indeed, not being able to use nested arrays, which is possible in GS, limits certain solutions; this should be overcome by the new capabilities introduced in the ßeta version.
With 3 at 2nd parameter in TOCOL I dropped the excess ones, thinking it was the best compromise.
Byev1 serves as a demonstration of the VALSn concept (a variation of thunking), whose main purpose is to store multiple variables in a single element and recall them as needed, and is typically used in conjunction with SCAN-MAP as a viable alternative to REDUCE-VSTACK. Indeed, it's very much an abstract method, requiring the ability to mentally visualize the results at each step of whatever problem you're solving for. The SORTBY-IFNA-EXPAND method I chose was just a simple trick to modify the first element of the array, then rotate the first row to the bottom of array and make way for the next record. DROP-VSTACK-HSTACK would have been just as good in this scenario for rearranging the records (and probably cleaner too). A moot point, though, considering v2 (or some other variation of broadcasting) is the way to go. I suspect the impending release of Compatibility Version 3 may offer alternative approaches with nested arrays and FLATTEN, but I'm not on the Beta Channel to try anything out at this time.
BTW, thanks for the challenge! Working through these types of problems are always fun!
One small revision to minimize redundancy...
The second accumulated value at each iteration of sch can be changed from amt to amt + acc(2), so that end can simply be evaluated as MAP(sch, LAMBDA(val, val(2))).
I'm not about to edit my previous post, as it would most likely result in the workbook being stripped away.
