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 22, 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 25, 2024

James_Buist 

You mention 'keeping corkscrews in separate components'.  For me the corkscrew has no particular significance; it is simply a presentational device used to support hand calculation in much the same way that numbers used to be laid out for  'long division' in the days when that was a thing.

 

I the physical world of engineering quantities that I am used to such quantities the balances are simply the indefinite integral of the flows; conversely one differentiates the balances to return to the flows.  In discrete models the quantities become accumulations over time and, the reverse, differences per  period.  No artistic layout is required; the quantities simply are what they are defined to be.

James_Buist's avatar
James_Buist
Brass Contributor
Oct 25, 2024
I get your point here but used loads in financial models and people like them and expect them. Also they do form a very logical calc flow for all sorts of accounts such as VAT, and various reserve accounts and revolving credit where the balance is based on the difference between inflows and outflows and is then carried forward. I doubt they will disappear soon. Finance people like to see how the numbers are arrived at and follow them through!! They are not very accepting of a black box number.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 13, 2025

    Hi James

    I am not sure why this reappeared in my feed, especially since it is marked as a solved problem!

    However, this time I have attached a possible solution using a complicated form of the SCAN function that works with array of array problems.  My intention is that SCANHλ helper function should be reasonably easy to incorporate into a model.

    The core element of the financial models I have seen, is the Lambda function that addresses a single period within the model and, knowing the opening balances, calculates the closing balances.  Being a single period, model circular references are avoidable.  This function may be used within SCAN (or a generalisation of SCAN) to calculate the closing balances for the entire model.  Closing balances are brought forward to form opening balances, here using B∕Fwdλ.  Once they are known the entire model can be calculated and presented as a single array calculation.

    The catch with going so far into array manipulation is that the flow of the calculation is best read from the formulas rather than by examining cells on the worksheet.  That will ruffle many feathers.

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Dec 16, 2025

      Problems with posting workbooks!

      djclements​ 

      Thanks for the help!  

      The platform seams to have got a lot worse recently!  It is easy to miss prior posts because they are hidden and posting attachments is an utter mess!

      • djclements's avatar
        djclements
        Silver Contributor
        Dec 17, 2025

        You're welcome, Peter!

        You may also want to change your profile settings, if you haven't already done so, to disable the default "threaded" conversation view. Under My Settings > Preferences > Replies and Comments, change Display format to Linear and Default sort order to Newest to Oldest.

        Sergei was kind enough to point this out to me in another thread.

Resources