Forum Discussion
Ways of performing Accumulation with Dynamic Arrays
This has been a great thread! Thanks to all the contributors. We just announced a new wave of lambda functions that I think will greatly help these scenarios. In particular, REDUCE, SCAN, BYROW and BYCOL.
It took me some time to understand the points you raise here. I think I get it now, translating into functional programming terminology one might want as an accumulator,
LAMBDA(x,y,x+z*y)
with z = 1.1, say, for compound interest. One could also extend to array inputs using,
LAMBDA(x,y,x+MMULT(z,y))
Setting z = {1,1;1,0} together with an input list consisting of pairs {1;0}, {0;0}, {0;0}, {0;0}, ...
would give rise to a sequence of Fibonacci pairs {1;1}, {2;1}, {3;2}, {5;3}, ...
Since arrays cannot be nested they would need to be passed as individual arguments though, one possible approach for that was mentioned here.
I realise since the arrays are only one dimensional they could be stacked which I guess was the intention for the ACCUMULATE function. So one could write a function that accumulates arrays in one dimension or other, e.g.
=VSCAN({0,1;0,0;0,0;0,0},LAMBDA(x,y,x+MMULT(y,{0,1;1,1})))would return {1,1;1,2;2,3;3,5}
Yes, though I apologise for causing you to devote time chasing that particular rabbit down its burrow! It was just a thought that the second-order difference equation could be reduced to first-order matrix form by introducing an additional variable.
I've been a little busy playing with melding Andy Gordon's fold implementation and Peter's tree traversal (_tt), and have put together versions of reduce/fold/scan (left and right) using both methodologies; well, almost, I've gone cross-eyed on doing the tree traversal for scan, so tabling it for now. Please see attached! I've even got reduce/fold on columns and rows!
Based on my studying, we basically only need one of the three to get the other two; I've done this with reduce_tt using fold_tt. I think we should be able to make scan from reduce and Peter's COMBINEλ, however its going slightly wrong. I'm sure its an oversight on my part somewhere, but I thought I might let others play with these while I uncross my eyes.
I have downloaded your file and plan to work through it. Some education regarding the higher-order functions will not go amiss!
As things stand though, it is my belief that Excel still needs a high performance ACCUMULATE function as a built-in function in order to achieve the goal of fully-dynamic modelling with Excel. Corkscrews represent a very common construct in financial modelling and suggesting the solution lies in recursive programming is not a way forward.
