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 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,
- True to my word, I've gone through with the unary reworking, and I think the only reason I have the patience for it is because of the editor. I think there are some issues and bugs, but I've not made a concerted effort to catalog them methodically. And I've had my Windows crash this week, seemingly beyond repair, so I'm out of commission for a few more days.
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!
