Forum Discussion
Working with Arrays of Ranges
Wow, I would bet that those are a couple of formulas that no one has tried to write before! What do you mean by 'not working' ? Does the formula error or does it crash Excel? I have noticed a number of occasions where the beta channel code hesitates, crashes, reopens, and crashes a second time. The recovered file contains areas of #N/A in place of formulas. Out of curiosity what is
LAMBDA(a,DROP(INDEX(T,,,a),{0}))intended to do? My guess it that it was intended as the means to extract an individual range reference. I agree that, since the concept of multi-area ranges is a native part of Excel it makes sense to evaluate their applicability.
In my exploration of 'array of ranges', though, I wasn't setting out to avoid thunks. More the opposite, I am increasingly comfortable with the concept, and look for opportunity to deploy them. I see parallels with Named Formulas which can be defined in Name Manager, but are not evaluated until such time as they are used within a formula. They may never be evaluated but, each time they are, it will be a fresh calculation. The alternative is to evaluate the Name in a Range and then reference the Range. The price of the calculation is paid at that point but its multiple reuse is cheap.
Similarly, I believe placing a formula within LAMBDA turns it into a named function that may never need to be evaluated. The catch is that if the function is used within MAKEARRAY to extract its elements term by term, the price of the calculation will be paid at every step. What I attempt, is to use LET to force the calculation and only then to create a thunk to reference the contents of the variable. I am then able to pass the thunk as a reference to an area of memory, through a chain of nested function calls as a reference rather than an array.
I was driven to such strategies by necessity (explaining to a lay audience why an array of thunks might be the best thing since sliced bread is not something to be undertaken lightly), but it could even be considered as a way of improving calculation efficiency. If the array is used at the final step of a recursive formula, do you need a copy of the array at each step or would it be better to store it as a reference at each step and only evaluate it at the final step. Now I really am speculating beyond my competence!
Meanwhile, I am quite happy thunking along on the crest of a wave 😜.
DROP(range,{0}) appears to have the effect of 'boxing' the range within a 1x1 array, i.e. range -> {range}, thus preventing it from being dereferenced in some situations. Conversely the @ operator has the effect of 'unboxing' the range, i.e. {range} -> range. I'd need to track down some other examples where lambda helper functions dereference range inputs and this trick was required.
Not that I have anything against thunks about which you will certainly be more informed than me. To be honest my use of spreadsheets hasn't progressed beyond copy/paste exercises of late so I thought it would be an interesting challenge to see if any of the old techniques could be leveraged here. One thought was that it may be possible to improve efficiency through manipulation of references. I haven't looked to see what the limit on areas is but I'd think there are scenarios where forming a union of range references can be more efficient than forming a union of range arrays particularly given hstack/vstack support multi-area ranges as parameters.
PS. Clarification to earlier formula: this is designed to return an array of sums for a multi-area range but currently throws #CALC! on beta version.