Forum Discussion
Ways of performing Accumulation with Dynamic Arrays
- Jul 26, 2021
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 haven't found as much time as I had hoped for to work with the Lambda editor. How about everyone else? I find the editor works well and both error checking and the context specific prompts also work well. That said, I find there is a curious disconnect between the add-in's side panel and the worksheet formulas one is trying to construct. I have yet to develop a work practice that allows me to progressively develop the formula, checking each step of the calculation as it is created. When I am in a VBA environment, the ability to step through the calculation using F8 is a lifesaver -- which probably demonstrates my shortcomings as a coder.
I do wonder whether the environment for creating Lambda functions on the worksheet could also be improved. After all, creating formula-local names using LET is now second nature. Would it be possible to allow workbook-scoped names to be defined in a similar manner?
Does anyone have insight as to the reason that the performance of the Lambda functions can be underwhelming? It could simply be the lack of 35 years of optimisation but I also wonder whether the memory management required to support recursion is the source of problems. Certainly Charles William's ACCUMULATE function was lightyears ahead in performance and, since converting flows to balances is so central to modelling, I would like to see it as an Excel built-in function.
Talking of FastExcel, Charles Williams has given me permission to upload a newly developed runtime version of the addin, if anyone is interested in trying it. It appears that the addin and the Excel workbook should be packaged as a zipfile and used within the same folder,
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?