# Accumulating arrays

Silver Contributor

# 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 present, though, it is not easy to create such data objects as the result of an array formula without triggering an error.

The problem I set out to solve was to create a Lambda function that would accumulate a number of rows horizontally.

Superficially, the solution looks simple enough.  An array of accumulations has been generated without triggering an error.  It is only when one looks at the Lambda function in its full anonymous glory that one might have reservations.

``````= LAMBDA(data′,[init′],
LET(
→0, "Initialisation",
n, ROWS(data′),
m, COLUMNS(data′),
k, SEQUENCE(n,m),
→1, "Define λ-function for use with SCAN",
ACC1λ,  LAMBDA(acc,k₁,
LET(
r,  1+QUOTIENT(k₁-1,m),
c,  1+MOD(k₁-1,m),
d,  INDEX(data′, r, c),
d₀, IF(ISOMITTED(init′), 0, INDEX(init′, r)),
d + IF(c>1, acc, d₀)
)
),
→2, "Use SCAN to perform accumulation",
SCAN(0, k, ACC1λ)
)
)(data, init)``````

The SCAN function runs across the array row by row and the formula tracks the row and column indices in order to determine the point at which a new sequence should be started.  I could have included a multiplicative factor as well but felt it was unnecessary at this point.

Do you have any ideas to improve the approach?

``````= BYROW(A,
LAMBDA(row,
SCAN(0,row,
LAMBDA(acc,a₁,acc+a₁)
)
)
)``````

would have been nice, but returns a #CALC! error.  Maybe MAKEARRAY could return a suitable solution?

10 Replies

The workbook

# Re: Accumulating arrays

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

# Re: Accumulating arrays

@Peter BartholomewHowdy Peter, season's greetings! I did something similar with thunks and MAKEARRAY, see here for more detail. It assumes init is a scalar rather than an array of values, but could work with a few minor tweaks.

# Re: Accumulating arrays

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!

# Re: Accumulating arrays

@Peter Bartholomew , thank you. I guess @tboulden used the same idea.

# Re: Accumulating arrays

@tboulden Hi, good to hear from you again and thanks for the link to your article on Bill Jelen's site.  It is very relevant.  The idea of using thunks was one I was going to try, if only because I have yet to put them to use beyond demonstrating that I could create one and read it back.  I worked through your approach but using the notation I had already adopted for the problem and got to

``````= LAMBDA(init′,data′,fnλ,
LET(
→0, "Initialisation",
n, ROWS(data′),
m, COLUMNS(data′),
→1, "Accumulate by row, returning
a column of thunks",
rowϑ,
BYROW(data′,
LAMBDA(row,
LAMBDA(SCAN(0,row,fnλ))
)
),
→2, "Expand thunks to form 2D array",
init′+MAKEARRAY(n, m,
LAMBDA(r,c,
INDEX(
INDEX(rowϑ,r,1)(),
1, c )
)
)
)
)(init,data,SUMλ)``````

which returned the correct result.  My treatment of the initial values is not ideal but the learning exercise has been rewarding.

For one reason or another I do not expect my posts to be swamped by responses but it appears that what may be lacking in quantity is made up for by quality.

One idea that I have, but have not been able to put into practice is, instead of hiding the array of arrays from BYROW, is to use thunks to hide from SCAN the fact that each value and accumulation is, in fact, a column vector that can be processed by the inner Lambda function, fnλ.  What I would want fnλ to do is, evaluate the thunks, multiply the resulting accumulation state by a matrix, and increment the result using the value array and return the newly accumulated values in the form of a thunk.  The requirement is similar to the Fibonacci calculation that you or Lori worked through in the days when recursion was all we had.

Please accept my best wishes for what is left of the holiday season and for the New Year.

# Re: Accumulating arrays

@Peter BartholomewMuch appreciated! I attempted to revamp my formula to go a similar direction if I'm reading you correctly, however it caused Excel to crash repeatedly. It was on a different machine, but it was something like the below; trying to add the error-handling for init_vals_arr being omitted, or promoting a single scalar to a rows_-length array are where I began experiencing glitchiness. Even tried operating from Excel for Web so Excel wouldn't keep crashing; I was able to import the LAMBDA editor manifest xml for the add-in, but it has no name manager available, so was at an impasse there as well.

``````SCANBYROW
=LAMBDA(init_vals_arr,array,fn,
LET(
rows_,ROWS(array),
cols_,COLUMNS(array),
thunk,LAMBDA(x,LAMBDA(x)),
row_thunks,BYROW(array,thunk),
scan_thunks,
MAP(init_vals_arr,row_thunks,
LAMBDA(init,row_thunk,
thunk(SCAN(init,row_thunk(),fn))),
MAKEARRAY(rows_,cols_,
LAMBDA(i,j,
INDEX(
INDEX(scan_thunks,i,1)(),
1,
j
)
)
)
)
)``````

# Re: Accumulating arrays

I think scan_thunks was missing a closing parenthesis

``````= LAMBDA(init_vals_arr,array,fn,
LET(
rows_, ROWS(array),
cols_, COLUMNS(array),
thunk, LAMBDA(x,LAMBDA(x)),
row_thunks, BYROW(array, thunk),
scan_thunks,
MAP(init_vals_arr, row_thunks,
LAMBDA(init,row_thunk,
thunk(SCAN(init,row_thunk(),fn)))),
MAKEARRAY(rows_,cols_,
LAMBDA(i,j,
INDEX( INDEX(scan_thunks,i,1)(), 1, j)
)
)
)
)(initial,array,Sumλ)``````

@tboulden I am going to have to work hard to catch up with you!! (grin)

# Re: Accumulating arrays

So far modified a bit my initial variant by adding inits to SCAN

``````scanByRows = LAMBDA(starts, data, fn,
MAKEARRAY(
ROWS(data),
COLUMNS(data),
LAMBDA(r, c,
// SCAN accumulates data for each row
// and outer INDEX returns result for
// each column of such row
INDEX(
SCAN(
INDEX(starts, r),
INDEX(data, r, 0),
LAMBDA(a, v, fn(a, v))
),
1,
c
)
)
)
);
``````

# Re: Accumulating arrays

Thank you so much. With your guidance and example I think I have achieved what I set out to do!

The formula I now have is

``````= LAMBDA(arr,init,Fnλ,
LET(
arrϑ,  BYCOL(arr, THUNKλ),
initϑ, THUNKλ(init),
accumulatedϑ, SCAN(initϑ, arrϑ, Fnλ),
EXPANDTHUNKλ(accumulatedϑ)
)

The main difference from our earlier work is that I converted columns into thunks so that I could feed a row array of thunks into SCAN.  That makes the Lambda parameter a little more complicated, but allows the rows to be processed together.

By changing the parameter string to

``````(growth,invested,GROWTHTHUNKλ)
&
(array,FibInit,FIBTHUNKλ)``````

respectively the same anonymous Lambda function performed a growth calculation and, as a party  piece, evaluated terms of the Fibonacci series.

The supporting Lambda functions were

``````"THUNKλ"
= LAMBDA(x,LAMBDA(x))

"EXPANDTHUNKλ"
= LAMBDA(aϑ,
MAKEARRAY(COUNTA(INDEX(aϑ,1)()),COUNTA(aϑ),
LAMBDA(r,c,INDEX(INDEX(aϑ,c)(),r))
)
)