Forum Discussion
Double Thunking Works Wonders!
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!
I seem to have struggled with thunks for quite a time now. The point I have reached, it to think of them as a code fragment, held as text, that can be evaluated at some later point. Rather like a defined Name, the formula may be little more than a reference or it may be a major calculation. A thunk that references a LET variable minimises the burden of recalculation.
The key point in this context, is that the code invoked by a thunk may itself contain further functions, including thunks. Hence I can pass arrays of thunks to a function, like SCAN, that expects a simple array of values.
Since one is passing code representing deferred calculations around, it is difficult to know whether it is correct or not. That is where the tricks I described above are involved. By isolating a single thunk and evaluating it, one can check that one in on-track to return the desired result. It is all pretty mind stretching though. What I have been aiming at, is to embed the complexity into purpose-written helper functions that may be used subsequently without requiring understanding of 'how' they work. That is very different from the traditional spreadsheet paradigm where the 'how' is blatantly obvious, being no more than grade school arithmetic, but the 'why' is obscured by the volume of cascading cell formulas.
BTW, I have just read that the Double Diamond brand of beer is being reintroduced, so soon I may be able to determine whether Double Diamond (hopefully not the indifferent keg-ale with carbon dioxide pressurisation) does still 'work wonders'.
I've been working on a function that utilizes thunks. The rough draft of the function utilizes a single thunk to re-shape a matrix by specifying the depth and width.
The general idea:
Start with an input matrix that can be re-shaped into 1 of 2 possibilities. My working solution breaks the matrix into smaller matrices with the help of thunks. I hope to have something to share soon. At present the function is still undergoing rigorous testing and I need to determine what to do in situations where the number of columns and/or rows is not even.
Your post drew my attention to the fact that, although I have given a lot of attention to unravelling arrays of thunks that arise 'naturally' as workarounds for the 'array of array', 'nested arrays' and 'array or ranges' limitations of Excel, I have paid little attention to the modelling of nested multi-dimensional arrays.
This is my attempt to rectify that situation. By all means ignore the post, at least until you have completed your testing. I would hate to come between a man and his rigorous testing regime or even spoil the fun of the chase! I think it is a worthwhile problem because Excel data quite often seems to be prepared as an array of forms, each containing array data. That, despite the wishes of the more database-oriented of our friends, who would prefer to see all data in normalised tables.
In fact, you do show the process of normalising the data as one of your outputs, but my past experience is that the steps that allow the data to be normalised as a starting point for standard solution processes, often allow the desired output to be derived directly from the cross-tabulated input.
I will show a picture of my worksheet because that will allow you to see what I have done without necessarily getting tied up with the 'how'.