Forum Discussion
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
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ϑ) ) )(array,initial,ADDTHUNKλ)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)) ) ) "ADDTHUNKλ" = LAMBDA(accϑ,vϑ, THUNKλ(accϑ()+vϑ())) "GROWTHTHUNKλ" = LAMBDA(accϑ,vϑ, THUNKλ(accϑ()*(1+vϑ()))) "FIBTHUNKλ" = LAMBDA(accϑ,vϑ, THUNKλ(MMULT(M, accϑ())))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
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!
PeterBartholomew1 , thank you. I guess tboulden used the same idea.
The workbook
PeterBartholomew1Howdy 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.
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.
