Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 21, 2024
Solved

Return of the Corkscrew with Spilled Arrays

  I'm starting a new topic but do want to refer to PeterBartholomew1 post  on accumulators here https://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Oct 23, 2024

    James_Buist Just for good measure, MMULT can also get the job done with minimal effort.

     

    Closing Balance (cell G22):

    =LET(
    data, HSTACK(F22:F24,G12:M14+G17:M19),
    cId, SEQUENCE(,COLUMNS(data)),
    arr, MMULT(--data,--(DROP(cId,,1)>=TOCOL(cId))),
    VSTACK(arr,BYCOL(arr,SUM))
)

     

    Cheers!

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 22, 2024

James_Buist 

The basic strategy is to start of by calculating closing balances using SCAN.  The catch here is that you are accumulating arrays rather than scalars, so SCAN needs to be fooled by passing it a thunk vector.  The problem of evaluating the resulting thunk array is not trivial.  Opening balances follow by offsetting the closing balances by one period.  Once you have the opening balances, the rest of the model follows as one simple array calculation.

 

In some situations, like a revolver, you may know how that you are only accumulating two interacting rows of data and it may be better to use two passes of MAP to evaluate debt and cash rows individually.

 

 

Resources