Forum Discussion
Double Thunking Works Wonders!
Whilst the original post was challenging [it created a Lambda helper function that simultaneously accumulated results both across a range and down it, whilst allowing individual results to be arrays], it was somewhat esoteric and lacked obvious use cases other than generating combinations as outlined.
As a consequence, I have reformulated that particular use case which allowed considerable simplification, since each list of combinations only references the list above and the list to the left. Ultimately the number of combinations returned by the formula is limited by the number of rows on the worksheet. It is quite usable returning 200,000 combinations (for example).
Something that the new solution retains from the original helper function is that it uses thunks to hold any single list of combinations within a 2D array. To make REDUCE work, a complete row of thunks is itself turned into a single thunk. The amazing thing is that it works, and works efficiently at that!
This is one of those concepts where I spend some time on it each day then put it aside to re-visit at a later time. Needless to say, when a post like this is made it's easy to fill up quickly! These are what my notes look like in stepping through LISTCOMBINλ:
It's going to take some time to sink in before I understand it better. I really hope your work is going a long way to forcing the Excel team's hand to open up the calculation engine and support nested arrays. In the very least - maybe LIST to store nested arrays in memory without #CALC! errors and a function to flatten the list.
You appear to be doing well. I get so far, and then realise I have lost the thread and need to wait for fresh inspiration! I find myself spending a lot of time debugging, but that's inevitable when you make as many mistakes as I do 🤔.
One debugging pattern is to return a LET variable of interest instead of the intended final result. If it is #CALC! then before doing anything else, it enclose it in TYPE (within the LET block). Surprisingly often the single term comes back type=64. Then the next step is remove the TYPE and replace it with INDEX(..., 1, 1)( ) or, if one is feeling a little more adventurous (@...)( ). If an array of values is returned, end of story. Just a question of establishing whether they are right or wrong. Now though, it might be an array of #CALC! errors (thunks) one gets back. Then it is back to INDEX once more, to pick out a term that should be interesting and, at the same time, recognisable. Having isolated a single thunk from the flock, the final step is to evaluate the thunk by offering it a null parameter string, and it just may reward you with an array that you recognise. I do wonder whether @Diarmuid Early would take so long!
If I understand the thinking, if one is using thunks it might make sense to thunk again to reduce the output to a scalar for calculation efficiency (It's easier to toss around a scalar than a large matrix, for example). This makes sense to me on a basic level but goes against my natural Excel instincts which are wired to adhere to the limitations of the current calculation engine.
The more I play around with thunks, the more my head spins with ideas of how I can improve functions I've built. I have a magic square Lambda that contains 3 algorithms depending on the integer. 2 of the algorithms could possibly be improved by the idea of double thunking because of the heavy use of MAP. I may get around to it after I digest this meal!
