Forum Discussion
Double Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n (not just a count of options =COMBIN(n, m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task.
Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array.
In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right.
So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row.
By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done!
p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960s
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!
