Would a FOR.EACH function be useful in Excel 365?

%3CLINGO-SUB%20id%3D%22lingo-sub-1717371%22%20slang%3D%22en-US%22%3EWould%20a%20FOR.EACH%20function%20be%20useful%20in%20Excel%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1717371%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20attached%20files%20are%20based%20upon%20a%20workbook%20that%20emulates%20the%20proposed%20function%20using%20a%20VBA%20macro.%26nbsp%3B%20There%20would%20appear%20to%20be%20many%20potential%20applications%20in%20situations%20where%20the%26nbsp%3B%40operator%20needs%20to%20be%20used%20to%20restrict%20the%20operation%20of%20an%20array%20parameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222427i70ECD5A6E1DDB598%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22Intro%20sheet%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EIntro%20sheet%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20586px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222428iEBAC00432E6ADE26%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1717371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1720170%22%20slang%3D%22en-US%22%3ERe%3A%20Would%20a%20FOR.EACH%20function%20be%20useful%20in%20Excel%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1720170%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20attached%20an%20updated%20file%20to%20the%20previous%20post%20which%20allows%20for%20formulae%20that%20return%201D%20arrays%2C%20giving%20an%20array%20of%20arrays%20held%20within%20a%202D%20spilt%20range.%26nbsp%3B%20Comments%20would%20be%20welcome!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20679px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222562i87CDF1AD9A594FDC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20668px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222566i5AA0B831944F883B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1734546%22%20slang%3D%22en-US%22%3ERe%3A%20Would%20a%20FOR.EACH%20function%20be%20useful%20in%20Excel%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1734546%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStill%20working.%26nbsp%3B%20the%20present%20sheet%20was%20written%20as%20a%20demonstration%20of%20LET%20relevant%20to%20a%20question%20on%20Chandoo.%26nbsp%3B%20The%20last%20entry%20for%20each%20field%20of%20a%20table%20was%20returned%20using%20XLOOKUP.%26nbsp%3B%20It%20is%20not%20possible%20to%20search%20multiple%20rows%20within%20a%20single%20array%20formula%2C%20so%20the%20column%20is%20picked%20up%20as%20a%20relative%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWrapping%20the%20formula%20within%20FOR.EACH%20removes%20the%20need%20for%20relative%20referencing%20and%20returns%20the%20results%20as%20a%20dynamic%20array.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20558px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223324i22E8EC5B140084E7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22FOREACH%20replaces%20relative%20referencing%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFOREACH%20replaces%20relative%20referencing%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhereas%20the%20previous%20workbooks%20were%20created%20to%20demonstrate%20and%20idea%20and%20put%20it%20forward%20for%20discussion%2C%20this%20time%20I%20simply%20built%20the%20formula%20around%20an%20existing%20solution.%26nbsp%3B%20The%20implementation%20of%20the%20macro%20is%20still%20weak%20but%20the%20idea%20seems%20to%20work%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948050%22%20slang%3D%22en-US%22%3ERe%3A%20Would%20a%20FOR.EACH%20function%20be%20useful%20in%20Excel%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3Ewhat%20are%20you%20working%20on%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948427%22%20slang%3D%22en-US%22%3ERe%3A%20Would%20a%20FOR.EACH%20function%20be%20useful%20in%20Excel%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888320%22%20target%3D%22_blank%22%3E%40Alexbayliss1545%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20develop%20is%20an%20approach%20to%20building%20financial%20or%20engineering%20models%20that%20is%20based%20on%20the%20use%20of%20dynamic%20arrays%20in%20its%20entirety.%26nbsp%3B%20Any%20input%20data%20would%20be%20entered%20into%20Tables%20in%20order%20to%20provide%20structured%20references%20that%20are%20inherently%20dynamic.%26nbsp%3B%20From%20there%2C%20the%20entire%20solution%20should%20respond%20to%20change%20without%20any%20user%20intervention%20or%20any%20need%20to%20create%20'oversized'%20formula%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20goals%20are%20to%20get%20rid%20of%20the%20concept%20direct%20cell%20referencing%20totally%20(the%20location%20on%20the%20sheet%20should%20be%20of%20no%20more%20relevance%20than%20the%20position%20of%20text%20on%20a%20page%2C%20-%20order%20matters%20but%20not%20the%20location).%26nbsp%3B%20Another%20advantage%20is%20that%20the%20concept%20of%20relative%20referencing%20can%20also%20be%20consigned%20to%20the%20trash%20can%20of%20history%3B%20it%20never%20was%20a%20meaningful%20concept%20though%20it%20did%20fit%20an%20amateurish%20way%20of%20working%20that%20was%20built%20on%20the%20idea%20of%20replicating%20formulas%2C%20rather%20than%20recognising%20arrays%20and%20lists%20as%20objects.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhether%20this%20is%20a%20good%20place%20to%20express%20the%20idea%20that%20traditional%20spreadsheet%20concepts%20are%20junk%20is%20another%20matter!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959490%22%20slang%3D%22en-US%22%3ERe%3A%20Would%20a%20FOR.EACH%20function%20be%20useful%20in%20Excel%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20think%20these%20requirements%20may%20be%20met%20by%20the%20new%20LAMBDA%20function%20and%20recursion.%3C%2FP%3E%3CP%3EFirst%20though%2C%20it%20needs%20to%20make%20an%20appearance%20on%20my%20system!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Valued Contributor

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.

 

Intro sheetIntro sheetimage.png

9 Replies

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!

 

image.pngimage.png

@Peter Bartholomew 

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.

FOREACH replaces relative referencingFOREACH replaces relative referencing

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.

@Alexbayliss1545 

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!

@Peter Bartholomew

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!

 

@Peter Bartholomew 

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.

@Sergei Baklan 

Thank you Sergei.  I value your opinion.

@Sergei Baklan 

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?

@Peter Bartholomew 

I tried to play with, result is in the row below

image.png

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
  )
)