Forum Discussion
If you thought Spills killed CSE, read this post
I build financial models. All financial models I have ever seen do the same thing - they show one reporting date per column. Every next reporting period evolves from the previous financial close. That is, the calculation order in such models is strictly A1 to An, B1 to Bn, ... N1 to Nn.
There may at times arise the need to perform complex calculations that produce more than one result. Today, we can (program those in Lambdas and) spill the outputs across multiple rows (within the same column).
Alas, Excel's performance resizing Spills is absolutely atrocious. Deploy such a formula across the columns of your model and you can literally watch the pixels set. One column at a time. Things settle after the initial run. There is then no need to resize ever again.
Just so long as the calculation works out alright.
Unfortunately, stuff happenz. And when that calculation errors out, you are back to watching pixels set as Excel replaces your multiple result rows with one row of #VALUE!. Once you fix that error, well, then you are back in for another treat of umpteen resize passes...
Such a spreadsheet is frankly unusable.
Now, I noticed with some astonishment that there is a simple fix for this nonsense: Just enter your spill formula with Control-Shift-Enter to morph it into an array formula. Sure, we all hate those. But all the hassle around editing is a price worth paying for Excel suddenly attaining Warp speed...
Post Scriptum:
There is another "solution" to this problem. Instead of performing the calculation once to spill 5 results, perform the same calculation 5 times over and return 1 result each time. If you think this is awful, you are of course correct. But ... to my amazement, doing this easily beats the Spill (no surprise - even using an Abacus probably will) and there was no telling a difference to CSE. I was baffled. Then I realized what Excel got up to. Excel understands that these 5 calculations are independent of one another and will happily parallelize them across multiple CPU cores. So, while this insane solution hammers the CPU, the nature of a financial model is such that it mostly cannot exercise more than one core anyway (it cannot be multi-threaded), such that on the occasion that we are being stupid, there are plenty of cores idling around that can bail us out... :)
Post Post Scriptum:
If for some reason you do not wish to use CSE, make absolutely sure that when stuff happenz your Spill returns 5 bad results. That avoids Excel having to resize that range when an error occurs.
4 Replies
I'm interested in seeing an anonymized sample workbook with this calculation crunch. There's not a lot of official documentation concerning dynamic arrays and Lambda in regards to performance and optimization.
I'm attaching a workbook that was referenced in an older discussion that may have been overlooked by many. It's worth checking out because the workbook contains a reproduceable anomaly in the way the screen updates to fill out the spill. I'm curious if your example is anything like this.
Puzzling performance issues with LAMBDA array formulas | Microsoft Community Hub
I will take the warning but, to date, I haven't encountered such problems. Maybe the errors have caused some corruption of the Excel file. I have tried formulas such as
= LET( date, EOMONTH(startDate, SEQUENCE(N, , 0)), date ) = LET( flow, SEQUENCE(N, , initial), flow ) = LET( balance, SCAN(0, cashFlow#, SUM), balance ) = SUM(cashFlow#) = MAX(balance#)with N = 80,000 (quite a lot of months) with no apparent problem.
Yeah, just tried with a blank workbook and some VSTACKs, not noticeable. Meaning, your sheet needs a certain complexity. You'll know when it hits you. Just interesting to observe that CSE still has some use :)
I am a bit concerned about this. What level of complexity are we talking about? I have used SCAN to accumulate cash and debt as a two cell thunk over 30 years of monthly periods and then used the opening balances to generate a further 10 arrays using as a single array formula. No problems so far.
Are you working at a far bigger scale or is there something wrong with your setup that Excel is struggling to find the necessary resources.
BTW Before 2019 I used to use CSE to commit all formulas. It worked OK (though with some performance penalty) but authoring was not a good experience.
