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!

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 18, 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