I would welcome your review of the following ideas, to identify errors, spot improvements or advise if I am following ideas that are already standard fare for others. My objective is to develop a series of workbooks that are comprised entirely of dynamic arrays; no more 'and fill down'. The problems I identified when DA first appeared were the problem of using dynamic arrays for accumulations without allowing circular references and problems handling 'arrays of arrays' (sum each column for example). Recursion and lambda functions appear to offer a way forward, but I do not find it intuitive.
"The purpose of the proposed approach is to accumulate a sequence of values as a dynamic array. Examples include the flow variables in a running balance or series returned from a difference equation. The key feature of the calculation is that each term depends upon the preceding value of the same array; this makes Excel report the calculation as a circular reference.
The calculation developed here uses recursion to work around this problem by exploiting the fact that the recursion introduces a new set of operands at every level of the stack. In order to avoid replicating the flow variables at every level of the recursion stack, they are passed as a lambda function rather than an array. A similar consideration also applies to the results. Rather than accumulating the results as they are calculated, assembly is left to the tail end of the calculation as control is returned back though the stack.
Unfortunately the obvious linear sequence of calls from term to term can easily fail because of the limit on the size of the operand stack. In order to circumvent this problem, I have traversed the data as a binary tree, as shown in the figure. Although twice as many nodes are visited in such a scheme, the maximum levels of recursion for N values is log₂(N).
= LAMBDA(λfunction,startValue,r,p,n,
IF(n>1,
LET(
ℓ, n - QUOTIENT(n,2),
b₁, XACCλ(λfunction,startValue,r,p,ℓ),
b₂, XACCλ(λfunction,MAX(b₁),r,p+ℓ,n-ℓ),
balance, COMBINEλ(b₁, b₂),
balance),
λfunction(p)+(1+r)*startValue)
)
I have tested the Lambda function for up to 16,000 terms (pretty much the width of an Excel sheet) and it has worked, but is slow. The approach took 15sec, more than 1000 times slower than Charles Williams's FastExcel paid add-in! That said, for more reasonably sized timelines the calculation worked well.
Note: Other approaches to the accumulation of flow variables (sometimes known as corkscrews from the way a hand-calculation may be laid out) include the use of MMULT and SUMIFS. In each case, the idea is to calculate the balances directly from the flow variables without any recursive calls to previously calculated values. In the case of MMULT, the calculation requires an upper triangular matrix of 1's to be assembled. Although there is no formal limit of array size in Excel, 16000 x 16000 struck me as uncomfortably large."
