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 love your final thought of
"my commitment to my recent progress is hindering my continued progress"!
I am now up and going with the Lambda editor. It was just a case of not noticing SHARED FOLDER under Get Add-ins. I now need time to run through the set-piece examples to bring it together with the results of my own experimentation.
At the moment, I find a strange contrast between basic spreadsheet development and the world of Lambda functions. The first assumes one is too idle to define ones terms and can handle only the most primitive computing concepts but, by way of contrast, has an elephantine memory for 'tips and tricks' that allow one to apply 'go faster stripes' to the solution process. The second appears so convoluted that I am finding it tricky to thread my Fortran IV trained brain through the twists and turns.
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.
MAP and FOLD could be combined to give SCAN though ideally scalable versions of all these functions would be available. For comparison Power Query and Python both have Accumulate available as a standard function capable of processing large lists via buffers / iterators. Many useful functions could be derived from these three.
As a typical example, to sum over columns one might use something like,
=COLMAP( SUM, TABLE )
where SUM or LAMBDA( x, SUM( x ) ) is the aggregation function and
COLMAP:=LAMBDA(f,T,MAP(LAMBDA(i,f(INDEX(T,,i)),SEQUENCE(,COLUMNS(T)))))
I only would like to say that everything depends on implementation. As for today, and from my point of view, lambdas in Excel is not the best solution from performance point of view if you have another alternatives. Perhaps it will be improved, but it takes months or years, who knows.
Thanks for the clarification that list aggregation in PQ is quite inefficient - that's good to be aware of. I do not really know PQ well and using List.Buffer for running totals is likely still not that much of an improvement.
Python - which I am more familiar with - uses iterators for list operations. The itertools accumulate function processes a million rows in a fraction of a second and I'm hoping we could see similar performance in Excel if the propose new functions are well optimised. For reference, the suggested formula above was prototyped in Python using:from numpy import array, shape, sum colmap=lambda f,T:map(lambda i:f(T[:,i]),range(T.shape[0])) >>>A=array([[1,2],[3,4]]) >>>list(colmap(sum,A)) [4,6]As a comment, Power Query is optimised to work with tables, not with lists. If something could be done by not elegant sequence of table transformations and with elegant list function, the performance will be dramatically decreased in the latest case.
Afraid we could be here in the same situation, elegant lamdas functional programming could kill performance compare to legacy formulas.
As for lambdas editor the question is not how good examples it provides but how suitable this editor will be for average Joe for editing, maintenance, etc.
