Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 01, 2021

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 proble...
excel
Formulas and Functions
office 365
  • tboulden's avatar
    tboulden
    Iron Contributor
    Sep 01, 2021

    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_)))
)

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Sep 18, 2021

      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.

      = 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, ""))
      • tboulden's avatar
        tboulden
        Iron Contributor
        Sep 18, 2021
        That's a nice clean construction that encapsulates our journey from your original post!!
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Sep 03, 2021

      tboulden 

      I got a surprise playing with SCAN in response to a question on this forum.  Basically the OP wanted to decrease an amount by 10% per day, thus giving rise to a geometric sequence.  Besides calculating the sequence directly, I also used SCAN to give the result array in a manner that could accommodate a table of variable rates.

      Then I turned the percentage into an array {10%,12%} to see what happens, fully expecting SCAN to moan about nested arrays.  What happened was that the direct calculation of geometric sequences gave two sequences, one for 10% and the other 12%, but scan did something entirely different.

      What it did was to apply an alternating pattern of 10% and 12% as a single calculation!  The challenge might be to think up a meaningful use case for such a calculation.

      • JoeMcDaid's avatar
        JoeMcDaid
        Icon for Microsoft rankMicrosoft
        Sep 07, 2021

        SCAN iterates through the provided array argument in row-major order and returns its output in the same shape as the array argument. So this behavior is expected, array-of-array are not encountered as each iteration of SCAN only returns a scalar. 

Resources