Sep 01 2021 07:32 AM
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.
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.
(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.
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?
@Chris Gross
Sep 15 2021 02:20 AM
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!
Sep 15 2021 03:51 AM
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.
Sep 15 2021 04:57 AM - edited Sep 15 2021 05:03 AM
@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??
Sep 15 2021 05:59 AM
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.
Sep 15 2021 08:21 AM
@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.
Sep 15 2021 02:42 PM
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.
Sep 15 2021 11:56 PM
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.
Sep 16 2021 03:54 AM
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)
Sep 16 2021 01:57 PM - edited Sep 16 2021 02:11 PM
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?
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.
Sep 16 2021 02:33 PM
@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!
Sep 16 2021 02:37 PM
Sep 17 2021 03:53 AM
@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.
Sep 17 2021 04:03 AM
Sep 17 2021 01:44 PM
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!
Sep 17 2021 05:07 PM - edited Sep 17 2021 05:15 PM
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))
)
Sep 18 2021 12:28 AM
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}))
Sep 18 2021 01:38 AM
That is quite an achievement; an entire amortisation schedule in a cell. Rather like the numerical analogue of the ship in a bottle!
It might take me a while to assimilate the implications of this and related initiatives.
Sep 18 2021 02:24 PM
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.
= 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, ""))
Sep 18 2021 02:46 PM
Sep 18 2021 04:16 PM
@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)
)