Forum Discussion
Would a FOR.EACH function be useful in Excel 365?
PeterBartholomew1what are you working on
What I am trying to develop is an approach to building financial or engineering models that is based on the use of dynamic arrays in its entirety. Any input data would be entered into Tables in order to provide structured references that are inherently dynamic. From there, the entire solution should respond to change without any user intervention or any need to create 'oversized' formula ranges.
Other goals are to get rid of the concept direct cell referencing totally (the location on the sheet should be of no more relevance than the position of text on a page, - order matters but not the location). Another advantage is that the concept of relative referencing can also be consigned to the trash can of history; it never was a meaningful concept though it did fit an amateurish way of working that was built on the idea of replicating formulas, rather than recognising arrays and lists as objects.
Whether this is a good place to express the idea that traditional spreadsheet concepts are junk is another matter!
I think these requirements may be met by the new LAMBDA function and recursion.
First though, it needs to make an appearance on my system!
Most probably yes. If not right now, when some later, lambda is at very beginning.
As for the 50% lottery with new functionality - I have 3 beta installations, on my home PC, on laptop and on virtual machine in office. Quite often I'm in first 50% at least on one of them.
I think the attached workbook demonstrates that it should be possible to use recursion to perform accumulation. In the absence of the LAMBDA function, I have used rows on the worksheet to emulate a recursion stack.
As things stand, it would appear to require N² memory locations but that might depend upon the details of memory management as results are passed back from the stack.
Do you have any thoughts on the matter?
