Forum Discussion
Ways of performing Accumulation with Dynamic Arrays
This has been a great thread! Thanks to all the contributors. We just announced a new wave of lambda functions that I think will greatly help these scenarios. In particular, REDUCE, SCAN, BYROW and BYCOL.
I agree that it would be very nice to be able to step through much like in VBA; even having the Evaluate formula step-through would be a welcome improvement.
As for performance, I would think it is probably due to the feature being strapped on rather than properly integrated. I also wonder if the Calc.ts work paves the way for this optimization, but may rely on being Javascript/web-based, and its not super compatible with legacy Excel recalc paradigm? Uneducated musings on my part.
And I'd love to try out FastExcel once I get a working machine again! How do I sign up?
Putting the ingredients together to create a slightly different dish!
The Lambda editor and Calc.ts should be a pretty good fit. The editor checks syntax but if that is OK why not a form to accept a set of test parameters and display results on a small grid?
As for FastExcel it is possible to download a time limited test copy. What I was suggesting was slightly different. I would use my license to download a runtime version of the add-in and use zip to package it along with the Excel file for distribution. Apparently the idea is that the add-in works on the target machine, provided it is held in the same folder and the macro is run to load it on open.
- Ahh, that makes sense then, would love to see ACCUMULATE in action!
I've got a new Windows install running in a VM, and I've still got access to LAMBDA and the LAMBDA editor, so more to come!
From the lack of any further communication, I assume my experiment with the FastExcel runtime add-in didn't work. Sorry about that; it was my first attempt at using it.- Hi Peter, I apologize for my rudeness, it was successful! I've found my Windows install in a VM lacking however and that discouraged me from doing much; a replacement laptop with Windows finally shipped this week and I promise I'll do better once I have a working machine and can really examine how ACCUMULATE functions.
[2006.14706] Will Dynamic Arrays finally change the way Models are built? (arxiv.org)
In the referenced EuSpRIG paper, I drew attention to problems of accumulation and column sums as being the major bottlenecks impeding progress towards fully-dynamic (financial or engineering) models. Charles Williams solves each with elegance but it is a shame to have to rely upon add-ins to perform tasks that should be native functionality.
The zip file should contain the original accumulation file posted on this thread but with an 'on open' macro that loads one of the two add-ins. There is also a Word file containing instructions/explanation and a text copy of the macro.
If you wish to try other functions then let me know and I will add them to a fresh copy of the workbook; assuming you get it going in the first place, that is.