Sep 27 2020 05:47 AM - edited Sep 28 2020 05:46 AM
The attached files are based upon a workbook that emulates the proposed function using a VBA macro. There would appear to be many potential applications in situations where the @operator needs to be used to restrict the operation of an array parameter.
Sep 28 2020 06:09 AM
I have attached an updated file to the previous post which allows for formulae that return 1D arrays, giving an array of arrays held within a 2D spilt range. Comments would be welcome!
Oct 01 2020 06:18 AM
Still working. the present sheet was written as a demonstration of LET relevant to a question on Chandoo. The last entry for each field of a table was returned using XLOOKUP. It is not possible to search multiple rows within a single array formula, so the column is picked up as a relative reference.
Wrapping the formula within FOR.EACH removes the need for relative referencing and returns the results as a dynamic array.
Whereas the previous workbooks were created to demonstrate and idea and put it forward for discussion, this time I simply built the formula around an existing solution. The implementation of the macro is still weak but the idea seems to work well.
Dec 02 2020 03:04 AM
@Peter Bartholomewwhat are you working on
Dec 02 2020 04:32 AM - edited Dec 02 2020 04:34 AM
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!
Dec 05 2020 01:05 PM
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!
Dec 05 2020 01:16 PM
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.
Dec 05 2020 02:34 PM
Thank you Sergei. I value your opinion.
Dec 07 2020 02:48 PM
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?
Dec 08 2020 12:05 PM
I tried to play with, result is in the row below
and function
=LAMBDA(s,n,
if(n<=argument,
acc(
LET(
sm, SUM(INDEX(inflow,1,1):INDEX(inflow,1,n)),
k, SEQUENCE(,n),
IF( k<n, s, sm )
),
n+1
),
s+BFwd
)
)