Forum Discussion
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
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
69 Replies
Thank you each for contributing to my continuing education!
At first I was distracted by the MAKEARRAYs but then realised the answer lay in the use of MAP. I guess the key to generating a 2D array is that the array parameters must be of the same shape.
= LET( teamList, UNIQUE(team), teamScore, MAP(teamList,SCOREλ), teamOrder, SORTBY(teamList,teamScore), team, IF({1,1,1,1},teamOrder), place, IF(ISTEXT(teamOrder),{1,2,3,4}), MAP(team, place, LAMBDA(t,p,INDEX(MEMBERSλ(t), p)) ) )I have shortened the worksheet formula by moving the formulae that extract arrays from the original rawdata to global defined names along with the definition of the Lambda functions.
- JoeMcDaid
Microsoft
1x1 and scalars are distinct in Excel. =TYPE(1) returns 1. =TYPE({1}) returns 64.
The Arrays of Arrays limitation is a long standing limitation, it doesn't serve a purpose per se - its just something that was never supported even in the CSE days. Now that arrays are more prominent, the limitation is more apparent. Changing this behavior has backcompat implications, so anything we do here in future needs careful engineering and consideration. It also needs to be weighed and prioritized against other customer asks.Thanks for the correction, it was just a bit of sloppy reporting from
in which I remembered you saying "Arrays in Excel are 2D; Excel is the only programming language in the world that doesn't have the concept of a 1D array".
I have been trying to refactor old workbooks to help me understand the potential of the new Lambda functions for changing the nature of Excel programming. To my mind, something important that they achieve is to make recursion something more than a 'geeks corner' technique. Then I examined the idea that they may allow me to dispense with the concept of relative referencing in its entirety. Both the notation and the practice of 'copy down' could be consigned to the trashcan. MAP would allow one to dispense with one of the central concepts of traditional spreadsheet development.
It was surprising how many times I hit 'arrays of arrays' (and, to a lesser extent, arrays of ranges) issues. In some instances the gains of simplifying Excel formula were then lost by needing recursive VSTACK to consolidate the result array. What you are doing is amazing but I imagine it is also highly speculative in terms of user acceptance, so I can see why you might not wish to rush the process.
I was raising the issue early because I presumed that replacing a #CALC! error with a logical result might not cause compatibility issues whereas expanding a truncated calculation might have undesirable consequences.
Lost the file!
PeterBartholomew1 I did think of a MAP solution just now to test out, and its really just a glorified INDEX solution, please try the below. I've changes MEMBERS lambda to TRANSPOSE rather than CONCAT, then MAP across 2 arrays, the row indices and column indices.
= 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), TRANSPOSE(m) )), \2, "Identify scoring members of teams in order", teamScore, MAP(teamList, SCOREλ), teamOrder, SORTBY(teamList, teamScore), row_,MAKEARRAY(ROWS(teamOrder),4,LAMBDA(i,j,i)), col_,MAKEARRAY(ROWS(teamOrder),4,LAMBDA(i,j,j)), scoring, MAP(teamOrder, MEMBERSλ), MAP(row_,col_,LAMBDA(r_,c_,INDEX(MEMBERSλ(INDEX(teamOrder,r_)),c_))) )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, ""))
