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

Silver Contributor

I remember @JoeMcDaid stating that 2D arrays are the only data types held in Excel; rows columns and scalars are merely special cases with the last being a 1x1 array.

image.png

So why is it such a problem bringing related rows together the form a 2D array?  There are a number of tricks and workarounds, but should that be necessary?  By way of example I show an old data file with the individual results from a cross-country championships.

image.png

(first 20 of 300)

The exercise is to rank the teams by adding the positions of the first 4 of their 8 runners to get a point count (lowest wins).  What I require is an output with the teams in result order showing the names of the 4 scoring members of the team.

image.png

The formula looks to be a monster at first sight

 

 

= LET(
  \0, "Extract source data",
      pos,  --LEFT(rawdata,7),
      name, (MID(rawdata,8,22)),
      team, TRIM(MID(rawdata,31,26)),
      time,--("00:"&RIGHT(rawdata,5)),
      teamList, UNIQUE(team),
  \1, "Build Lambda functions",
      SCOREλ, LAMBDA(t,
         LET(
            pts, FILTER(pos, team=t),
            SUM(INDEX(pts,{1,2,3,4}))
         )),
      MEMBERSλ, LAMBDA(t,
         LET(
            m, FILTER(name, team=t),
            CONCAT(m)
         )),
  \2, "Identify scoring members of teams in order",
      teamScore, MAP(teamList, SCOREλ),
      teamOrder, SORTBY(teamList, teamScore),
      scoring, MAP(teamOrder, MEMBERSλ),
      MID(scoring,{1,23,45,67},22))

 

 

but the section following name '\0' and the text comment is simply extracting individual fields from the source data and could easily be separated off within a helper range.  Similarly the lines following '\1' defines named Lambda functions to calculate the team score given the name of a single team and to generate the array of scoring competitors.

The meat of the calculation is at the end: the teams are scored and ranked by score.  The penultimate line assemble the list of scoring members for each team in order.  My complaint is that each team cannot be treated as an array without triggering an error message.  Instead I have had to concatenate the names to product a column of results.  Only then can I split the column back to give the array I need.   

There are other approaches such as that shown by @tboulden in 

Array of arrays using Lambda helper functions - Microsoft Tech Community

which used REDUCE and a sequence of VSTACK operations.

 

Does the restriction on arrays of arrays serve any purpose given that the spreadsheet is all about the manipulation of 2D arrays?  What do you think? 

@Sergei Baklan 

@lori_m 

@Chris Gross 

69 Replies

@tboulden 

GLOBAL is an idea for a native Excel formula (along with a potential partner in crime, LOCAL, which would upload a name definition to the Sheet names collection).  As yet, it has not even passed the first hurdle towards being a feature request.  Does the idea even make sense; would it help close the divide between the experience of formula development on the worksheet and that of Name Manager?

 

I did think about a demonstrator and it would most likely draw upon the functionality of the Macro you referred to from my former work.  That required the user to lay out the name, the comment and a working formula implementing an anonymous Lambda function in a predefined pattern on the grid and manually run a macro.

 

To implement the GLOBAL function would either require it to be a Lambda function or a UDF.  The catch is that the first parameter defining the name would produce a #NAME! error first time round, that would prevent a UDF from working, though I suppose I could required it to be provided in the form of a string.

 

The next challenge is that UDFs are not allowed to change defined names, so the real action would need to be triggered by a worksheet change event.   Another idea for a demonstrator implementation was to allow GLOBAL itself to be a named Lambda function which would reduce to a LET function.  I have in the past done things like this to allow a function √(2) to be synonymous with SQRT(2) - pointless but kind of fun.

 

In the present case, it was getting late and it was too complicated, so there is no concept demonstrator.  Another option might be to suggest it to @Charles Williams to see whether he can be tempted to implement the idea as a C addin.  Though I think GLOBAL might be a good idea, I am not at a point where I could even mount an argument that this is the killer function that would integrate the user experience for spreadsheet development.  At present, I simply welcome discussion which might add to my education!

@Peter Bartholomew 

I believe one could create a VBA prototype e.g. GLOBAL_ by using an indirect call such as Evaluate("AddName()") where AddName() contains the Names.Add command.

 

This has potential to alter range dependencies during calculation though, my preference would be for LAMBDAs to be called from cells as well as names - if there were an option.

@lori_mI had tried experimenting with SET.NAME and DEFINE.NAME early on when I was playing with LAMBDA(x,EVALUATE(x)) for helping debug my constructions, but couldn't get it to work. I was able to get a handful of XLMs working via Name Manager, but only ones that returned info, nothing that altered the workbook. In my XLM reference I don't see a NAMES.ADD, can you reference the syntax??

@tboulden 

In the above Evaluate and Names.Add refer to VBA functions - presumably equivalent to the legacy EVALUATE and DEFINE.NAME macro functions though XLM has more resrictions within udfs.

@Peter Bartholomew @lori_m  Decided to mock-up my "array of LAMBDAs" suggestion since EVALUATE was revived in my memory. Perhaps I can configure this idea to work with MAKEARRAY similar to the proto-CORKSCREW previously shown, will give that a try later.

 

Array_of_LAMBDAs.jpg

@tboulden 

Not quite as devious as your thinking but I got as far as defining a GLOBALλ to be the Lambda function

= LAMBDA(name,λfn,λfn)

that takes a name and an anonymous Lambda function and returns the function.  So

= GLOBALλ(√, LAMBDA(x,SQRT(x)))(25)

would be a valid formula, despite the fact that the math symbol '√' will evaluate to a #NAME! or #CALC! error.  The next step is to read the formula using the VBA change event.  The formula would be parsed and the global name set up (as I do now) but reading from the formula text rather than a pattern of cells.

 

@Peter Bartholomew 

It's an interesting idea. Given the formula

 

=GLOBAL_(√, LAMBDA(x,SQRT(x)))

 

The following code could be inserted into a new VBA module:

 

Dim namedef(1) As String

Function GLOBAL_(ParamArray args()) As Boolean

    Application.Volatile False
    
    Formula = Trim(Mid(Application.ThisCell.FormulaLocal, 2))
    Namestr = Mid(Formula, InStr(Formula, ",") + 1)
    
    namedef(1) = "=" & Left(Namestr, InStrRev(Namestr, ")") - 1)
    namedef(0) = Mid(Split(Formula, ",")(0), 9)
    
    Evaluate "addname()"
    GLOBAL_ = True

End Function

Function addname()
    Names.Add namedef(0), namedef(1)
End Function

 

This appears to do the job on my setup (tested without access to LAMBDA). Clearly there is room for improvement in the string parsing to handle more general cases.

 

The reservation I would have with worksheet functions like GLOBAL is introducing the ability to alter workbook state which is not strictly consistent with the functional programming paradigm. Possible side effects may include losing undo and changing range dependencies within the workbook during a calculation cycle.

 

A button or hyperlink upload tool would be a safer approach at the expense of additional user interaction.

P.S. The attachment contains the following modification to the GLOBALλ definition:

= LAMBDA(name,λfn,IF(GLOBAL_(),λfn))

This might allow Peter's original formulation to work:

=GLOBALλ(√, LAMBDA(x,SQRT(x)))(25)

 

@tboulden 

That's cool that works. In a recent blog post, it sounded like XLM is going to be turned off by default for security reasons. I wonder if one can use CHOOSE({1;2;3},...) with multiple LAMBDAs as well?

 

@Peter Bartholomew 

I played around some more and discovered one can also add names via data validation! This removes the need for creating functions with side effects and also works with undo. I've attached another example which I think could be workable.

@Peter Bartholomew @lori_m  Success! I was able to avoid EVALUATE by using CHOOSE to create my array of LAMBDAs! Then by creating a COMPOSE lambda, I'm able to reduce an array of LAMBDA using COMPOSE. I think Peter will be able to run with this more quickly than I will, but I think its very promising!Screenshot 2021-09-16 173040.png

I think lightning struck us at the same time regarding CHOOSE from looking at the timestamps. Just had to put a workbook together!

@Peter Bartholomew  I've applied this REDUCE/COMPOSE construction to a modified version of your original problem. It's not as readable as the LET construction, but I think it will have applications for manipulations that frequently happen together.

Screenshot 2021-09-17 064911.png

 

One thing that was on my wishlist that this re-emphasizes: if some legacy functions were converted to be LAMBDAs, we could have =REDUCE(UNIQUE,...)(team) instead of =REDUCE(LAMBDA(x,UNIQUE(x)),...)(team).

@tboulden 

It is one thing to know that Lambda functions are first class citizens of the calculation and can be passed as arguments of other functions, it is another to achieve anything useful with the functionality.  Congratulations.  Presumably it is the MAKEARRAY that allows the formula to return the 2D array of results but following the formula is far from straightforward.  

 

In connection with your other post, it does sometime seem odd having to create a Lambda function that does no more than the built-in function it implements [for me it might be SUMλ which is simply a restricted version of SUM].  It has yet to cause me grief though and, at present, I find the presence of the λ a useful reminder.

 

Meanwhile, I have been focussing on simpler problems on this forum.  For example, to unpivot a 2D array

"UNPIVOTλ"
= LAMBDA(grid,
    LET(
      fullList, REDUCE( , grid, APPENDλ),
      SORT(UNIQUE(fullList))
    ))

where APPENDλ is defined by

"APPENDλ"
= LAMBDA(list,value,
    IF(value="", list,
    LET(
      n, COUNTA(list),
      k, SEQUENCE(n+1),
      IF(k<=n, list, value))
  ))

I have actually used REDUCE to run over a 2D array in row-major order to deliver value!

This is a little less convoluted in terms of application, I think. Using the appropriate functions with SCAN, I get the minimal set of values that encapsulate the changes, then break those changes out into their own columns in the final step.

 

=LET(
    bal,100000,
    nper,120,
    rate,5%/12,
    pmt_,PMT(rate,nper,-bal),
    array_1,
        MAKEARRAY(nper,3,LAMBDA(i,j,j)   ),
    fxns,
        CHOOSE(array_1,
            LAMBDA(x,x),
            LAMBDA(x,x*(1+rate)),
            LAMBDA(x,x-pmt_)
        ),
    amort,SCAN(bal,fxns,LAMBDA(acc,fn,fn(acc))),
    CHOOSE(SEQUENCE(,4),INDEX(amort,,1),INDEX(amort,,2)-INDEX(amort,,1),INDEX(amort,,1)-INDEX(amort,,3),INDEX(amort,,3))
)

 

 

@tboulden 

Looks impressive! I'll need to see if there's a Python equivalent formulation to get my head around it. Regarding the last line:

=CHOOSE(SEQUENCE(,4),
     INDEX(amort,,1),
     INDEX(amort,,2)-INDEX(amort,,1),
     INDEX(amort,,1)-INDEX(amort,,3),
     INDEX(amort,,3))

This looks fairly readable when placed on separate lines but might sometimes be worth creating a LAMBDA function inline in such cases so as to replace INDEX(amort,,i) as col(i), say.

 

Another option, given people who use higher order functions would generally be familiar with matrices, might be:

=MMULT(amort,TRANSPOSE({1,0,0;-1,1,0;1,0,-1;0,0,1}))

 

@tboulden 

That is quite an achievement; an entire amortisation schedule in a cell.  Rather like the numerical analogue of the ship in a bottle!

 

image.png

It might take me a while to assimilate the implications of this and related initiatives.

@tboulden 

I got some advice concerning the original 'array of arrays' problem.  Essentially the advice was 'rather than trying to create arrays of arrays, reduce the array to 'thunks', which can be handled as scalars and evaluated at the end using MAKEARRAY.  In the event, I didn't even need to do that because I was already working with Lambda functions (team name as the parameter) which evaluated correctly.

image.png

= LET(
    teamList, UNIQUE(team),
    teamScore, MAP(teamList, SCOREλ),
    teamOrder, SORTBY(teamList, teamScore),
    result, MAKEARRAY(COUNTA(teamList), 4,
      LAMBDA(t,p,INDEX(MEMBERSλ(INDEX(teamOrder,t)), p))
    ),
  IFERROR(result, ""))
That's a nice clean construction that encapsulates our journey from your original post!!

@Peter BartholomewRevisited your particle dynamics and reworked so that the values are passed in LAMBDA function rather than as COMPLEX, taking advantage of what we've learned about arrays of LAMBDAs.

 

=LET(
    pairs,
        SCAN(LAMBDA(x,CHOOSE(x,0,2)), F#,
            LAMBDA(coord,F₀,
                LET(
                    x₀,coord(1),
                    v₀, coord(2),
                    x₁, x₀ + Δt*v₀,
                    v₁, v₀ + Δt*F₀/Mass - Δt*Coef*v₀-κ*x₀,
                    LAMBDA(x,CHOOSE(x,x₁,v₁))
                )
            )
        ),
    x,MAP(pairs,F#,LAMBDA(fn,ignored,fn(1))),
    v,MAP(pairs,F#,LAMBDA(fn,ignored,fn(2))),
    CHOOSE({1,2},x,v)
)