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.
PeterBartholomew1re: "Of all the MAPS/FOLDS/SCANS that might be technically possible, I wonder which form the 'core irreducible set' that must be present to meet the major demands of user problems. Whichever make the transition to built-in functions will need careful presentation if it is to fit into standard practice." -- Based on my experimentation, and the varying implementations I've tried, I suspect we'll end up with element-wise, and row-wise/column-wise versions, and we'll need a proper conception of an empty array to work with. I think much of my trouble with my previous fold examples was trying to work around not having an empty array primitive as a "natural zero".
Taking myself at my word and reworking my fold/reduce/scans for a 3rd time, and after lori_m's COLMAP example, I've done implementations of vMap and hMap doing row-wise and column-wise mapping respectively. I may have been trying to squeeze too much out of fold and many of my failing examples have yielded to an application of one of these maps. I'm pretty happy that I got bubble-sort figured out finally though.
If I rework things again, it may be to take what I think may be the more functional approach and make everything unary so that I can get better acquainted with currying/partial application. I've gotten some inspiration recently from whoever is posting https://rosettacode.org/wiki/Category:Excel and https://rosettacode.org/wiki/Fibonacci_sequence#LAMBDA! Could be fun to pick through some of the things https://rosettacode.org/wiki/Reports:Tasks_not_implemented_in_Excel and give them a swing.
As for the LAMBDA editor, thus far I'm really enjoying it! Whereas previously I tried not to clutter things up too much, I've not minded the explosion of functions that do "one" thing that really are part of a swiss-army knife approach that makes more intractable problems easier.
And as SergeiBaklan mentions, usability will be the ultimate arbiter; I'm having fun with it now, but nothing in my work life relies on it, so we'll see what the team can make for us. I know that Peter's tree traverse has essentially removed the stack depth issues, but it does slow thing down considerably when getting into larger tree structures. Nothing to do but play around until the next thing is brought down from the mountain-top.
Thanks for the links and sample workbook - all sounds like great progress given what is available currently.
I now also have access to Lambda Editor but, as I'm restricted to current channel on corporate licence, it's not much use without LAMBDA! I presume one can make names containing lambdas appear as functions in tooltips and also create function categories for them by opening name manager from a macro sheet (ctrl+f11) or by using the VBE Names properties.
And interesting what you say about empty arrays being a stumbling block. An alternative might be to use an array containing a single blank value which can be returned as an intermediate result eg if blank=SORT(,) then ISBLANK(blank) returns {TRUE}. The INDEX function can preserve blank values like these in results but many other functions can't which makes them tricky to manipulate however.
I believe we shall have something more civilized to check missing/optional parameters. Excel team has lot of feedback from pre-lambda stage, hope many of wishes will be implemented in v.1.0 (or 2.0 if exclude production from versioning).
