A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.

PeterBartholomew1 

A substantial amount of trial and error was required as we're relying on undocumented functionality to achieve this objective. Things like adding names via validation and calling a Sub from a name I only learned through this exercise, however there were many other commands that didn't work as expected when called this way.

As the basis of a 'DEFINE' function, I did have some success adding the validation automatically using =IF(COUNTA(AddValidation()),Result) where 'Result' would refer to a LAMBDA expression similar to your earlier suggestion. I hope to pick this up again later when lambda reaches the current channel if someone else doesn't pick the baton up before then.

Since we've veered a little off topic here, it might be worth raising a new thread for name management in general to continue discussion of this and other ideas to improve user interaction with names. I'll look at putting some notes together when time permits.

lori_m 

I have been somewhat quiet of late!  I was struggling a bit sorting out a 'recommended approach' for using thunks from the tboulden experimentation.  I need an idiot's guide to follow!

As for your coding, it is so full of unfamiliar techniques that it looks like magic!  I think it was Arthur C Clark who had something to say about any sufficiently advanced technology being indistinguishable from magic.  It took me a while to recognise the UDFs, tucked away in LET since, without any arguments, they didn't look like functions; finding the subroutine call was even more of a challenge.  I had no idea that was possible to call subs like that and it seems to bypass all the constraints that would be placed upon a UDF.  I have a vague recollection of seeing hyperlinks being used to achieve a similar objective.

My original thought was to use the Worksheet Change event to run the VBA code but your approach seems to offer advantages.  I also like the way you have parsed the cell formula in the 'args' function, I had mentally overcomplicated the task by thinking in terms of Regular Expressions.

Right now, I have some conference papers to review, and hopefully approve, but I will try to put the pieces together, either following my idea of GLOBAL/LOCAL or perhaps using your 'scope' parameter to combine them as DEFINE (say).  I am hopeful that this will serve to make some of our very long formulas more compact by separating out the definition of key elements of the formula to defined names yet, at the same time, provide documentation and traceability on the worksheet rather than tucked away in Name Manager which appears to be widely despised.

tboulden 

It took a bit of time to figure out the formula parsing, it's not 100% foolproof yet, but as a proof-of-concept it works better than expected.

I think the GLOBAL/LOCAL functions that were suggested earlier have potential to further improve the UI by adding the validation automatically - which looks possible using Evaluate - I'lll have to wait for LAMBDA for that though. Anyway full credit to PeterBartholomew1  for speccing it out!

lori_mI forgot briefly that this was an xlsm and wondered what voodoo was happening! This is pretty slick!

lori_m 

Too easy to forget attachments!

tboulden 

I agree unions of ranges have limited application; arrays of ranges on the other hand can be more useful as more functions allow them, eg attachment uses formula below for referencing 4D data:

=INDEX(INDEX(SubArrays,i,j),k,l)

I guess the equivalent for thunks could be:

=INDEX(INDEX(Thunks,i,j)(),k,l)

Also included is a sample implementation of Peter's idea from earlier in the thread of adding names based on a LET function. I think this is quite an intuitive way of managing names in general not just LAMBDAs, I might see about distributing the code as an addin so any workbook code can be removed.

lori_mSorry, the prior post ate my image for the MAP constructions.

lori_m  Ahh, thanks for the clarification, I had never noticed the areas param in INDEX; I've confirmed both constructions, MAP across an area index and MAP across the EVALUATEd array of ranges.

I couldn't think of a real-world specific use-case for the REDUCE/union construction, but set up a table and unioned all rows and all columns separately, then intersected them. Was surprised to see that its non-commutative in terms of order, but also makes sense in retrospect.

tboulden 

My thought was that one might be able to select specific sub areas with

=INDEX((B6:D7,C7:E8,D7:F8),,,i)

and then apply MAP with i={1,2,3} maybe within a name? Analogously one can define a name 'subarrays' as

=EVALUATE({"B6:D7","C7:E8","D7:F8"})

and then use SUBTOTAL(9,subarrays) as in the previous OFFSET formula.

In Python instead of applying map to 2D arrays one can use array broadcasting in 3D, e.g.

arange(3)+arange(2)[:,None]+arange(3)[:,None,None]

returns:

[[[0, 1, 2],
[1, 2, 3]],

[[1, 2, 3],
[2, 3, 4]],

[[2, 3, 4],
[3, 4, 5]]]

PeterBartholomew1I went down a rabbit hole trying to play with MAKEARRAY since you mentioned it can't have a 0 value. I learned that you can omit both of the first two parameters, and that led me even further trying to see what possibly could go in for those parameters, even providing SEQUENCE formulae, etc. I was hoping to reverse-engineer MAKEARRAY based off the similarity to MAP of 2 parameters, but what I developed with MAP had sensical behavior, but I couldn't quite figure out what was going on with MAKEARRAY; exhibited on the MAKEARRAY tab of attached. I'm not sure I follow how to make it work with the 0 parameter. If the thunks aren't your cup of tea, check out the array_storage tab where I've put together a storage scheme to get dimensions of arrays and flatten them, and can revive the array from the storage array.

lori_m  I was able to use the union (,) operator, but could only get it working with thunks, see the union tab. I've not messed with union/intersection much, so not fully following your breadcrumbs, and my google searches didn't help much. Any references you can point me to, or suggest a specific INDEX construction? I wasn't sure what to do with OFFSET either.

PeterBartholomew1Re: Can a thunk refer to a previously calculated array, just waiting to be called, or should it be thought of as a recipe for calculating the array should it ever be required?

I think I recall hearing Andy/Simon/someone along the way mention lazy evaluation?? And I think I recall that meaning that it stores the LAMBDA, but only evaluates it when needed. It's also possible that my brain made it up sometime during my research on lambda calculus implementations.

PeterBartholomew1 
Thanks for hosting these lively discussions. I have learned a number of useful general techniques from the examples and suggestions in these posts. These threads also provide a useful way to feedback to the community now uservoice is not available. And yes, it would be useful to have access to lambda functionality for testing, with any luck it won't be too much longer to wait until they go current!

tboulden
Nice solutions, It will take some time to fully grasp these techniques! Interesting exercise to generate subarrays, one option:

r,c,n=2,3,5
A = np.arange(n*n).reshape(n,n)+1
p = np.mgrid[0:n-r+1,0:n-c+1].reshape(2,-1)
list(map(lambda i,j:A[i:i+r,j:j+c],p[0],p[1]))

I believe a more elegant solution exists using 3D array manipulations without lambda/map but it eludes me at present.

In Excel I wonder if unions of ranges could be constructed of the form ref1,ref2,...,refn via REDUCE so that individual areas could be selected with INDEX. OFFSET might also work though it is volatile,

=SUM(SUBTOTAL(9,OFFSET(A,{0,1,1},{0,1,2},2,3)))

As Peter says arrays of ranges are unsupported at present but out of interest, can MAP return an array of references using INDEX that can be manipulated this way?

One further thought.  It looks like we are not going to get native support for arrays of arrays or arrays of ranges within the foreseeable future (no doubt for good reason, including the potential loss of backward compatibility), but I wondered whether a single formula could be co-opted to the cause.  MAKEARRAY and derived Lambdas such as VSTACK are already capable of packing arrays into an array 'container' with padded spaces.

At the moment a '0' parameter is illegal within MAKEARRAY, but it would be reasonable to interpret it in much the same manner as a '0' in the INDEX function (i.e. 'I want it all').  If a number of row arrays were to be packed into a 2D array, the '0' would be interpreted as 'make the width just as long as it needs to be to accommodate the longest row'.

Any thoughts?

tboulden 

Wow!  If this were an art class, I am still working through Picasso's cubist period, whilst you guys are moving through Neoclassism and into the Surreal.   Despite that, I have made a little progress and come up with a Lambda function that appends a seasonally adjusted forecast to a table of actuals.  The prompt shows 

= FORECASTSλ(timeline, actuals, forecastPeriods, fill)

and the code reads

= LAMBDA(vec,arr,n,fill,
    MAKEARRAY(ROWS(arr), n+COLUMNS(arr),
      LAMBDA(row,col,
        LET(
          M,  COLUMNS(arr),
          k,  SEQUENCE(1,M),
          X,  INDEX(vec,col),
          X0, INDEX(vec,k),
          Y0, INDEX(arr,row,0),
          IF(col>M,
             FORECAST.ETS(X,Y0,X0),
             IF(fill, INDEX(Y0,col), "")))))
   )

At the moment, I find the creation of such formulae requires Herculean effort but no doubt it will come more naturally in time.

Looking through the discussion, I think we need lori_m to have an insider beta licence to bring his Python experience back to the fold of Excel.  Your frequent contributions have been missed both here and on Chandoo.  I have memories of a number of 'shortest formula' competitions with a quite remarkable degree of innovation.

I worked through the previous tboulden workbook with the reducing array thunks.  Can a thunk refer to a previously calculated array, just waiting to be called, or should it be thought of as a recipe for calculating the array should it ever be required?

Sorry, keep forgetting about the attachment.

lori_mPlease see attached, modified EXPAND slightly to handle 2d arrays of thunks. You'll note that for most of these, SCAN omits the init_value; this can be overcome if absolutely necessary, but is an added complication. I've also included a version of reshape that I tried basing off np.reshape, but it still needs some polishing.

=LAMBDA(array,[aRows],[aCols],
    LET(
        aRows_,IF(OR(ISOMITTED(aRows),aRows=""),1,INT(aRows)),
        aCols_,IF(OR(ISOMITTED(aCols),aCols=""),COUNTA(array),INT(aCols)),
        rows_,ROWS(array),
        cols_,COLUMNS(array),
        seq,SEQUENCE(aRows,aCols,0,1),
        r_,1+QUOTIENT(seq,cols_),
        c_,1+MOD(seq,cols_),
        reshaped,IFERROR(INDEX(array,r_,c_),""),
        result,FILTER(reshaped,BYROW(reshaped,LAMBDA(row_,NOT(AND(row_=""))))),
        result
    )
)

I'm enjoying these side-by-side comparisons, I've got another one for you, earlier this year I wrote a LAMBDA to extract subarrays of size [r x c] from a given array of size [m x n] in row-major order. Originally it was returning all subarrays, but since we hadn't figured out how to handle arrays-of-arrays, I was just using ARRAYTOTEXT, or subbing in an appropriate function to get back a single value.

Now that we are thunking the arrays, I can provide an index list, and operate on specific subarrays. What would this look like in Python?

I wonder if the 'thunk' method could also be made to work with SCAN?

For reference, this notebook code returns the same results as your sample file 

# array of arrays
peel = lambda x,_:x[1:-1,1:-1]
scanp=accumulate([0]*4,peel,initial=np.eye(9))
list(scanp)

# array of thunks
peelt = lambda x,_:lambda :x()[1:-1,1:-1]
scant=accumulate([0]*4,peelt, initial=lambda :np.eye(9))
list(scant)[3]()

 [np.eye = MUNIT]

tboulden 

Clever use of REDUCE with array parameters! That would never have crossed my mind.

It wasn't immediately clear to me exactly what the PEEL function was doing, maybe using SEQUENCE(n,n) for the screen clipping could assist others like me who do not yet have access to these new functions.

In any case, this looks very promising for working around the array truncation stumbling block that PeterBartholomew1 cites in the original post. 

Forgot to attach workbook.

If we define EXPAND like so:

=LAMBDA(array_of_arrays,indx,
    LET(
        target_thunk,INDEX(array_of_arrays,indx,1),
        target_thunk()
    )
)

Then we can use it to select and return a thunked array.

We can use this in REDUCE with no problem because REDUCE only returns the final result, unconstrained by the dimensions of the 2nd parameter, unlike MAP, BYROW/BYCOL, SCAN.

But if we try it in SCAN, we get an error because the results break the dimensions of 2nd parameter.

If we thunk the results instead, we can get all 4 back; they show as errors, but the arrays are inside the thunked LAMBDAs.

PeterBartholomew1  Let me know if this helps re: thunks; or anyone following along in the audience who may not know what we're talking about.

So as not to cloud the process with anything overly useful, I've defined PEEL to peel an array like an onion:

=LAMBDA(array,
    LET(
        rows_,ROWS(array),
        cols_,COLUMNS(array),
        INDEX(array,SEQUENCE(rows_-2,,2,1),SEQUENCE(,cols_-2,2,1))
    )
)

tboulden lori_m 

Thank you so much for taking this conversation forward.  I know some others have looked at the discussion but hesitated to get involved.  At times you are pushing me to my limits and beyond.  I plan to keep the discussion for future reference!

In recent days/weeks, I have tried to refactor a number of workbooks to exploit the new Lambda helper functions, that implement ideas that Lori first introduced me to.  The challenge of producing true 2D arrays rather than copying single rows seems to reoccur.  Whilst true 2D arrays are comparatively unusual, I would estimate that about 80% of solutions have array calculations conducted in parallel and would benefit from the ability to handle arrays of array (Joe did say "now that arrays are more prominent, the limitation is more apparent").  An example of this is the 12 month rolling forecast demonstrated by Danielle Stein-Fairhurst.

Building a Rolling Forecast in Excel - YouTube

I did manage to produce a fully-dynamic rolling forecast using MAKEARRAY and a Lambda function FORECASTλ that called the inbuilt FORECAST.ETS function by row and column indices (line item number and period counter) but it was far from straightforward and I really could do with a 'How to' guide if I am going to get beyond trial and error. 

I have yet to achieve the objective with 'thunks', so I am clearly falling short in my understanding.  My most successful approach has been to build a Lambda function that will return a single value from the desired array of results.  MAKEARRAY (or to a lesser extent, MAP) can then return the 2D array but it is far from fool-proof (unless that is just me bringing a finer grade of foolishness to the problem!)

I haven't attached Danielle's workbook without getting permission but have, instead, attached a simpler workbook which I used for development purposes.