Forum Discussion
Accumulating arrays
PeterBartholomew1Howdy Peter, season's greetings! I did something similar with thunks and MAKEARRAY, see https://www.mrexcel.com/board/threads/scanbyrow.1183272/ 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.
PeterBartholomew1Much 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 ) ) ) ) )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)
Interesting discussion about thunking, have to learn more.
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 ) ) ) );
