Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 24, 2021

Accumulating arrays

Spreadsheets are the home of the 2D array.  On closer examination most of the 2D arrays that one sees are actually 'arrays of arrays', that is a 1D array nested within a list of similar arrays.  At p...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 25, 2021

PeterBartholomew1 

Here is with MAKEARRAY(), I didn't wrap by lambda

=init + MAKEARRAY( ROWS(data), COLUMNS(data), LAMBDA(r,c, INDEX( SCAN(, INDEX( data, r, 0), LAMBDA(a,v,a+v)), 1, c ) ) )

and

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 26, 2021

    SergeiBaklan 

    Thanks Sergei.  That is great.  I had followed up one idea for exploiting the way in which SCAN operates over 2D arrays, but felt that a less idiosyncratic approaches should be possible.  You have demonstrated that it is indeed possible and appears to be far more concise than my formula.  To allow comparison I also applied my rather verbose coding style to your formula.

    = LAMBDA(data′,[init′],
     LET(
→0, "Initialisation",
       n, ROWS(data′),
       m, COLUMNS(data′),
→1, "Define λ-function for use with SCAN",
       SUMλ, LAMBDA(a,v,a+v),
→2, "Set up cell by cell evaluation using MAKEARRAY 
     and use SCAN to perform accumulation",
       init′ + MAKEARRAY( n, m,
         LAMBDA(r,c,
           INDEX(
             SCAN(, INDEX(data′, r, 0), SUMλ),
             1, c)
         )
       )
     )
   )(data, init)

    Yours is still shorter, though!

Resources