A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.

%3CLINGO-SUB%20id%3D%22lingo-sub-2707723%22%20slang%3D%22en-US%22%3EA%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arrays.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2707723%22%20slang%3D%22en-US%22%3E%3CP%3EI%20remember%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3Bstating%20that%202D%20arrays%20are%20the%20only%20data%20types%20held%20in%20Excel%3B%20rows%20columns%20and%20scalars%20are%20merely%20special%20cases%20with%20the%20last%20being%20a%201x1%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%20303px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307299i3002A22034A12FC1%2Fimage-dimensions%2F303x59%3Fv%3Dv2%22%20width%3D%22303%22%20height%3D%2259%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%20why%20is%20it%20such%20a%20problem%20bringing%20related%20rows%20together%20the%20form%20a%202D%20array%3F%26nbsp%3B%20There%20are%20a%20number%20of%20tricks%20and%20workarounds%2C%20but%20should%20that%20be%20necessary%3F%26nbsp%3B%20By%20way%20of%20example%20I%20show%20an%20old%20data%20file%20with%20the%20individual%20results%20from%20a%20cross-country%20championships.%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%20563px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307302iDD75A8F852FEC807%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(first%2020%20of%20300)%3C%2FP%3E%3CP%3EThe%20exercise%20is%20to%20rank%20the%20teams%20by%20adding%20the%20positions%20of%20the%20first%204%20of%20their%208%20runners%20to%20get%20a%20point%20count%20(lowest%20wins).%26nbsp%3B%20What%20I%20require%20is%20an%20output%20with%20the%20teams%20in%20result%20order%20showing%20the%20names%20of%20the%204%20scoring%20members%20of%20the%20team.%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%2F307305iF39B31A683EEEFE3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20formula%20looks%20to%20be%20a%20monster%20at%20first%20sight%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%5C0%2C%20%22Extract%20source%20data%22%2C%0A%20%20%20%20%20%20pos%2C%20%20--LEFT(rawdata%2C7)%2C%0A%20%20%20%20%20%20name%2C%20(MID(rawdata%2C8%2C22))%2C%0A%20%20%20%20%20%20team%2C%20TRIM(MID(rawdata%2C31%2C26))%2C%0A%20%20%20%20%20%20time%2C--(%2200%3A%22%26amp%3BRIGHT(rawdata%2C5))%2C%0A%20%20%20%20%20%20teamList%2C%20UNIQUE(team)%2C%0A%20%20%5C1%2C%20%22Build%20Lambda%20functions%22%2C%0A%20%20%20%20%20%20SCORE%CE%BB%2C%20LAMBDA(t%2C%0A%20%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20pts%2C%20FILTER(pos%2C%20team%3Dt)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20SUM(INDEX(pts%2C%7B1%2C2%2C3%2C4%7D))%0A%20%20%20%20%20%20%20%20%20))%2C%0A%20%20%20%20%20%20MEMBERS%CE%BB%2C%20LAMBDA(t%2C%0A%20%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20m%2C%20FILTER(name%2C%20team%3Dt)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20CONCAT(m)%0A%20%20%20%20%20%20%20%20%20))%2C%0A%20%20%5C2%2C%20%22Identify%20scoring%20members%20of%20teams%20in%20order%22%2C%0A%20%20%20%20%20%20teamScore%2C%20MAP(teamList%2C%20SCORE%CE%BB)%2C%0A%20%20%20%20%20%20teamOrder%2C%20SORTBY(teamList%2C%20teamScore)%2C%0A%20%20%20%20%20%20scoring%2C%20MAP(teamOrder%2C%20MEMBERS%CE%BB)%2C%0A%20%20%20%20%20%20MID(scoring%2C%7B1%2C23%2C45%2C67%7D%2C22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20the%20section%20following%20name%20'%5C0'%20and%20the%20text%20comment%20is%20simply%20extracting%20individual%20fields%20from%20the%20source%20data%20and%20could%20easily%20be%20separated%20off%20within%20a%20helper%20range.%26nbsp%3B%20Similarly%20the%20lines%20following%20'%5C1'%20defines%20named%20Lambda%20functions%20to%20calculate%20the%20team%20score%20given%20the%20name%20of%20a%20single%20team%20and%20to%20generate%20the%20array%20of%20scoring%20competitors.%3C%2FP%3E%3CP%3EThe%20meat%20of%20the%20calculation%20is%20at%20the%20end%3A%20the%20teams%20are%20scored%20and%20ranked%20by%20score.%26nbsp%3B%20The%20penultimate%20line%20assemble%20the%20list%20of%20scoring%20members%20for%20each%20team%20in%20order.%26nbsp%3B%20My%20complaint%20is%20that%20each%20team%20cannot%20be%20treated%20as%20an%20array%20without%20triggering%20an%20error%20message.%26nbsp%3B%20Instead%20I%20have%20had%20to%20concatenate%20the%20names%20to%20product%20a%20column%20of%20results.%26nbsp%3B%20Only%20then%20can%20I%20split%20the%20column%20back%20to%20give%20the%20array%20I%20need.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20other%20approaches%20such%20as%20that%20shown%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3Bin%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Farray-of-arrays-using-lambda-helper-functions%2Fm-p%2F2634147%23M109962%22%20target%3D%22_blank%22%3EArray%20of%20arrays%20using%20Lambda%20helper%20functions%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%3CP%3Ewhich%20used%20REDUCE%20and%20a%20sequence%20of%20VSTACK%20operations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20the%20restriction%20on%20arrays%20of%20arrays%20serve%20any%20purpose%20given%20that%20the%20spreadsheet%20is%20all%20about%20the%20manipulation%20of%202D%20arrays%3F%26nbsp%3B%20What%20do%20you%20think%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F64907%22%20target%3D%22_blank%22%3E%40Chris%3C%2FA%3E%20Gross%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2707723%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2707740%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2707740%22%20slang%3D%22en-US%22%3E%3CP%3ELost%20the%20file!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2708704%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2708704%22%20slang%3D%22en-US%22%3E1x1%20and%20scalars%20are%20distinct%20in%20Excel.%20%3DTYPE(1)%20returns%201.%20%3DTYPE(%7B1%7D)%20returns%2064.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20Arrays%20of%20Arrays%20limitation%20is%20a%20long%20standing%20limitation%2C%20it%20doesn't%20serve%20a%20purpose%20per%20se%20-%20its%20just%20something%20that%20was%20never%20supported%20even%20in%20the%20CSE%20days.%20Now%20that%20arrays%20are%20more%20prominent%2C%20the%20limitation%20is%20more%20apparent.%20Changing%20this%20behavior%20has%20back%20compat%20implications%2C%20so%20anything%20we%20do%20here%20in%20future%20needs%20careful%20engineering%20and%20consideration.%20It%20also%20needs%20to%20be%20weighed%20and%20prioritized%20against%20other%20customer%20asks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2709194%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2709194%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%20I%20did%20think%20of%20a%20MAP%20solution%20just%20now%20to%20test%20out%2C%20and%20its%20really%20just%20a%20glorified%20INDEX%20solution%2C%20please%20try%20the%20below.%20I've%20changes%20MEMBERS%20lambda%20to%20TRANSPOSE%20rather%20than%20CONCAT%2C%20then%20MAP%20across%202%20arrays%2C%20the%20row%20indices%20and%20column%20indices.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%5C0%2C%20%22Extract%20source%20data%22%2C%0A%20%20%20%20%20%20pos%2C%20%20--LEFT(rawdata%2C7)%2C%0A%20%20%20%20%20%20name%2C%20(MID(rawdata%2C8%2C22))%2C%0A%20%20%20%20%20%20team%2C%20TRIM(MID(rawdata%2C31%2C26))%2C%0A%20%20%20%20%20%20time%2C--(%2200%3A%22%26amp%3BRIGHT(rawdata%2C5))%2C%0A%20%20%20%20%20%20teamList%2C%20UNIQUE(team)%2C%0A%20%20%20%5C1%2C%20%22Build%20Lambda%20functions%22%2C%0A%20%20%20%20%20%20SCORE%CE%BB%2C%20LAMBDA(t%2C%0A%20%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20pts%2C%20FILTER(pos%2C%20team%3Dt)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20SUM(INDEX(pts%2C%7B1%2C2%2C3%2C4%7D))%0A%20%20%20%20%20%20%20%20%20))%2C%0A%20%20%20%20%20%20MEMBERS%CE%BB%2C%20LAMBDA(t%2C%0A%20%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20m%2C%20FILTER(name%2C%20team%3Dt)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20TRANSPOSE(m)%0A%20%20%20%20%20%20%20%20%20))%2C%0A%20%20%20%5C2%2C%20%22Identify%20scoring%20members%20of%20teams%20in%20order%22%2C%0A%20%20%20%20%20%20teamScore%2C%20MAP(teamList%2C%20SCORE%CE%BB)%2C%0A%20%20%20%20%20%20teamOrder%2C%20SORTBY(teamList%2C%20teamScore)%2C%0A%20%20%20%20%20%20row_%2CMAKEARRAY(ROWS(teamOrder)%2C4%2CLAMBDA(i%2Cj%2Ci))%2C%0A%20%20%20%20%20%20col_%2CMAKEARRAY(ROWS(teamOrder)%2C4%2CLAMBDA(i%2Cj%2Cj))%2C%0A%20%20%20%20%20%20scoring%2C%20MAP(teamOrder%2C%20MEMBERS%CE%BB)%2C%0A%20%20%20%20%20%20MAP(row_%2Ccol_%2CLAMBDA(r_%2Cc_%2CINDEX(MEMBERS%CE%BB(INDEX(teamOrder%2Cr_))%2Cc_)))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2709555%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2709555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20correction%2C%20it%20was%20just%20a%20bit%20of%20sloppy%20reporting%20from%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2F9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2F9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3Ein%20which%20I%20remembered%20you%20saying%20%22Arrays%20in%20Excel%20are%202D%3B%20Excel%20is%20the%20only%20programming%20language%20in%20the%20world%20that%20doesn't%20have%20the%20concept%20of%20a%201D%20array%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20refactor%20old%20workbooks%20to%20help%20me%20understand%20the%20potential%20of%20the%20new%20Lambda%20functions%20for%20changing%20the%20nature%20of%20Excel%20programming.%26nbsp%3B%20To%20my%20mind%2C%20something%20important%20that%20they%20achieve%20is%20to%20make%20recursion%20something%20more%20than%20a%20'geeks%20corner'%20technique.%26nbsp%3B%20Then%20I%20examined%20the%20idea%20that%20they%20may%20allow%20me%20to%20dispense%20with%20the%20concept%20of%20relative%20referencing%20in%20its%20entirety.%26nbsp%3B%20Both%20the%20notation%20and%20the%20practice%20of%20'copy%20down'%20could%20be%20consigned%20to%20the%20trashcan.%26nbsp%3B%20MAP%20would%20allow%20one%20to%20dispense%20with%20one%20of%20the%20central%20concepts%20of%20traditional%20spreadsheet%20development.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20surprising%20how%20many%20times%20I%20hit%20'arrays%20of%20arrays'%20(and%2C%20to%20a%20lesser%20extent%2C%20arrays%20of%20ranges)%20issues.%26nbsp%3B%20In%20some%20instances%20the%20gains%20of%20simplifying%20Excel%20formula%20were%20then%20lost%20by%20needing%20recursive%20VSTACK%20to%20consolidate%20the%20result%20array.%26nbsp%3B%20What%20you%20are%20doing%20is%20amazing%20but%20I%20imagine%20it%20is%20also%20highly%20speculative%20in%20terms%20of%20user%20acceptance%2C%20so%20I%20can%20see%20why%20you%20might%20not%20wish%20to%20rush%20the%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20raising%20the%20issue%20early%20because%20I%20presumed%20that%20replacing%20a%20%23CALC!%20error%20with%20a%20logical%20result%20might%20not%20cause%20compatibility%20issues%20whereas%20expanding%20a%20truncated%20calculation%20might%20have%20undesirable%20consequences.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712216%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712216%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20each%20for%20contributing%20to%20my%20continuing%20education!%3C%2FP%3E%3CP%3EAt%20first%20I%20was%20distracted%20by%20the%20MAKEARRAYs%20but%20then%20realised%20the%20answer%20lay%20in%20the%20use%20of%20MAP.%26nbsp%3B%20%26nbsp%3BI%20guess%20the%20key%20to%20generating%20a%202D%20array%20is%20that%20the%20array%20parameters%20must%20be%20of%20the%20same%20shape.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20teamList%2C%20%20UNIQUE(team)%2C%0A%20%20%20%20teamScore%2C%20MAP(teamList%2CSCORE%CE%BB)%2C%0A%20%20%20%20teamOrder%2C%20SORTBY(teamList%2CteamScore)%2C%0A%20%20%20%20team%2C%20%20%20%20%20%20IF(%7B1%2C1%2C1%2C1%7D%2CteamOrder)%2C%0A%20%20%20%20place%2C%20%20%20%20%20IF(ISTEXT(teamOrder)%2C%7B1%2C2%2C3%2C4%7D)%2C%0A%20%20%20%20MAP(team%2C%20place%2C%0A%20%20%20%20%20%20LAMBDA(t%2Cp%2CINDEX(MEMBERS%CE%BB(t)%2C%20p))%0A%20%20%20%20)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CEM%3EI%20have%20shortened%20the%20worksheet%20formula%20by%20moving%20the%20formulae%20that%20extract%20arrays%20from%20the%20original%20rawdata%20to%20global%20defined%20names%20along%20with%20the%20definition%20of%20the%20Lambda%20functions.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716755%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20a%20surprise%20playing%20with%20SCAN%20in%20response%20to%20a%20question%20on%20this%20forum.%26nbsp%3B%20Basically%20the%20OP%20wanted%20to%20decrease%20an%20amount%20by%2010%25%20per%20day%2C%20thus%20giving%20rise%20to%20a%20geometric%20sequence.%26nbsp%3B%20Besides%20calculating%20the%20sequence%20directly%2C%20I%20also%20used%20SCAN%20to%20give%20the%20result%20array%20in%20a%20manner%20that%20could%20accommodate%20a%20table%20of%20variable%20rates.%3C%2FP%3E%3CP%3EThen%20I%20turned%20the%20percentage%20into%20an%20array%20%7B10%25%2C12%25%7D%20to%20see%20what%20happens%2C%20fully%20expecting%20SCAN%20to%20moan%20about%20nested%20arrays.%26nbsp%3B%20What%20happened%20was%20that%20the%20direct%20calculation%20of%20geometric%20sequences%20gave%20two%20sequences%2C%20one%20for%2010%25%20and%20the%20other%2012%25%2C%20but%20scan%20did%20something%20entirely%20different.%3C%2FP%3E%3CP%3EWhat%20it%20did%20was%20to%20apply%20an%20alternating%20pattern%20of%2010%25%20and%2012%25%20as%20a%20single%20calculation!%26nbsp%3B%20The%20challenge%20might%20be%20to%20think%20up%20a%20meaningful%20use%20case%20for%20such%20a%20calculation.%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%2F307932i37E5AC6965616A81%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%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-SUB%20id%3D%22lingo-sub-2719590%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719590%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%20Apologies%2C%20took%20some%20time%20to%20digest%20your%20post%2C%20but%20I'm%20not%20sure%20I%20am%20understanding.%20In%20cell%20L4%2C%20I've%20modified%20it%20to%20the%20following%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20rateByDay%2C%20Rate*SEQUENCE(Days-1%2C%2C%2C0)%2C%0A%20%20%20result%2CSCAN(initial%2C%20rateByDay%2C%20LAMBDA(acc%2Cr%2C%20acc*(1-r)))%2C%0A%20%20%20result%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20Rate%20%3D%20%7B10%25%2C12%25%7D%2C%20however%20SCAN%20is%20using%20rateByDay%2C%20which%20is%20a%209x2%20array%2C%209%20rows%20of%20%7B10%25%2C12%25%7D.%20MAP%2FREDUCE%2FSCAN%20perform%20at%20the%20smallest%20unit%20of%20the%202nd%20parameter%20array%2C%20so%20value-by-value%2C%20moving%20from%20top-left%20to%20bottom-right.%20What%20you're%20describing%20seems%20expected%3F%20Am%20I%20misunderstanding%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2719916%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20wanted%20to%20achieve%20was%20an%20array%20of%20two%20SCANs%2C%20one%20down%20each%20of%20two%20columns%2C%20with%20a%20row%20of%20two%20initial%20values%2C%20so%20that%20I%20could%20compare%20the%20effect%20of%2010%25%20depreciation%20against%2020%25.%26nbsp%3B%20I%20had%20picked%20up%20the%20message%20that%20arrays%20of%20arrays%20were%20not%20allowed%2C%20so%20expected%20an%20error%20message.%26nbsp%3B%20The%20surprise%20was%20that%20I%20got%20numbers%20at%20all%2C%20then%20that%20I%20couldn't%20immediately%20recognise%20the%20values%20returned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20MAP%20to%20return%20each%20of%20a%202D%20array%20as%20independent%20calculations%2C%20but%20order%20hardly%20matters%20there.%26nbsp%3B%20What%20I%20hadn't%20realised%20was%20that%20SCAN%20and%20REDUCE%20would%20also%20accept%202D%20arrays%20in%20exactly%20the%20same%20way%20but%2C%20given%20they%20are%20accumulating%2C%20order%20is%20important.%26nbsp%3B%20It%20seems%20to%20work%20like%20the%20Persian%20story%20of%20one%20grain%20of%20rice%20being%20placed%20on%20the%20first%20square%20of%20a%20chess%20board%20and%20two%20on%20the%20next.%26nbsp%3B%26nbsp%3B2%E2%81%B6%E2%81%B4%20grains%20are%20unaffordable.%26nbsp%3B%20The%20modern-day%20equivalent%20might%20be%20'on%20the%20second%20week%20there%20will%20be%20two%20Covid%20cases'.%26nbsp%3B%20Some%20of%20our%20politicians%20were%20noticeably%20unable%20to%20grasp%20the%20consequences.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2719980%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719980%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%3EThanks%2C%20my%20reading%20comprehension%20seems%20to%20be%20off%20the%20past%20day%20or%20two.%3C%2FP%3E%3CP%3EIn%20trying%20to%20work%20through%20other%20routes%20for%20this%20problem%2C%20I%20made%20a%20further%20connection%20that%20seems%20obvious%20after%20typing%20it%20out%3A%20MAKEARRAY(rows_%2Ccols_%2CLAMBDA(i%2Cj%2C...))%20is%20just%20a%20special%20case%20of%20MAP(array_1%2Carray_2%2CLAMBDA(a_1%2Ca_2%2C...)).%20I%20assume%20MAKEARRAY%20just%20does%20the%20handling%20of%20ensuring%20array_1%20and%20array_2%20have%20the%20same%20dimensions%20and%20values%20are%20all%20TYPE%3D1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2729529%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2729529%22%20slang%3D%22en-US%22%3E%3CP%3ESCAN%20iterates%20through%20the%20provided%20array%20argument%20in%20row-major%20order%20and%20returns%20its%20output%20in%20the%20same%20shape%20as%20the%20array%20argument.%20So%20this%20behavior%20is%20expected%2C%20array-of-array%20are%20not%20encountered%20as%20each%20iteration%20of%20SCAN%20only%20returns%20a%20scalar.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731873%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731873%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%3CBR%20%2F%3EFrom%20what%20I%20understand%20the%20%3CEM%3Einitial_value%3C%2FEM%3E%26nbsp%3Bparameter%20of%20SCAN%20is%20a%20scalar%20so%20there%20one%20might%20run%20into%20the%20nested%20array%20limitation%20though%20not%20with%20the%20%3CEM%3Earray%3C%2FEM%3E%20parameter%20as%20Joe%20confirms%20above.%20As%20an%20alternative%20I%20had%20wondered%20if%20SCAN%20might%20be%20able%20to%20return%20a%20col%2Frow%20of%20results%20given%20a%20row%2Fcol%20as%20%3CEM%3Einitial_value%26nbsp%3B%3C%2FEM%3Eparameter%20by%20iterating%20over%20rows%2Fcols%20of%20a%202d%26nbsp%3B%3CEM%3Earray%20%3C%2FEM%3Eparameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20a%20somewhat%20related%20note%2C%20I%20was%20surprised%20to%20find%20that%20the%20TREND%20function%2C%20in%20addtion%20to%20returning%20a%201d-vector%20given%20a%201d-vector%20of%20%3CEM%3Eknown_ys%3C%2FEM%3E%20and%20a%202d-array%20of%20%3CEM%3Eknown_xs%3C%2FEM%3E%2C%20can%20also%20return%20a%202d-array%20given%202d-arrays%20of%20%3CEM%3Eknown_ys%3C%2FEM%3E%20and%20%3CEM%3Eknown_xs%26nbsp%3B%3C%2FEM%3E%26nbsp%3B(in%20row-major%20order).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2733563%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20getting%20there%20slowly%20by%20trial%20and%20error%2C%20but%20it%20is%20good%20to%20have%20the%20behaviour%20confirmed.%26nbsp%3B%20It%20has%20a%20logical%20basis%20for%20understanding%20though%20I%20can%20think%20of%20few%20cases%20where%20I%20would%20be%20likely%20to%20use%20the%20row-major%20order.%26nbsp%3B%20Maybe%20an%20escalation%20calculation%20where%20the%20days%20are%20organised%20by%20weekday%3B%20continuity%20as%20one%20moves%20down%20a%20week%20could%20make%20sense.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20scanning%20an%20array%20with%20multiple%20initial%20values%20would%20require%20separate%20functions%20of%20VSCAN%20and%20HSCAN%3F%26nbsp%3B%20In%20business-based%20calculation%2C%20I%20suspect%20that%20most%20scanned%20accumulations%20would%20normally%20be%20conducted%20in%20parallel%2C%20but%20in%20engineering%20dynamics%20there%20are%20likely%20to%20be%20multiple%20position%20and%20velocity%20values%20required%20to%20capture%20the%20state%20of%20a%20system%20and%20there%20will%20be%20coupling%20between%20the%20system%20coordinates%20from%20step%20to%20step.%26nbsp%3B%20I%20wanted%20to%20check%20the%20simulation%20of%20a%20container%20port%20crane%20moving%20a%20load%20that%20is%20free%20to%20swing%20as%20a%20pendulum.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20gave%20up%20on%20that%20(it%20requires%204%20values)%2C%20but%20have%20attached%20a%20worksheet%20following%20the%20motion%20of%20a%20single%20object.%26nbsp%3B%20That%20only%20required%20one%20position%20x(t)%20and%20the%20corresponding%20velocity%20v(t)%20to%20be%20tracked.%26nbsp%3B%20Because%20SCAN%20only%20works%20the%20way%20I%20need%20over%20a%20single%20column%2C%20I%20combined%20the%20two%20values%20as%20a%20complex%20number%20for%20the%20purpose%20of%20the%20calculation.%26nbsp%3B%20The%20job%20of%20the%20Lambda%20function%20is%20to%20split%20the%20string%20into%20two%20numbers%2C%20to%20calculate%20the%20two%20in%20a%20way%20that%20takes%20account%20of%20the%20current%20velocity%20forces%20acting%20on%20the%20system%20(restoring%20force%2C%20control%20force%20and%20damping)%20and%20then%20uses%20the%20COMPLEX%20function%20to%20recombine%20the%20two%20forces%20into%20one.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20SCAN(%222i%22%2C%20F%23%2C%0A%20%20%20%20LAMBDA(coord%2CF%E2%82%80%2C%0A%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20x%E2%82%80%2C%20IMREAL(coord)%2C%0A%20%20%20%20%20%20%20%20%20v%E2%82%80%2C%20IMAGINARY(coord)%2C%0A%20%20%20%20%20%20%20%20%20x%E2%82%81%2C%20x%E2%82%80%20%2B%20%CE%94t*v%E2%82%80%2C%0A%20%20%20%20%20%20%20%20%20v%E2%82%81%2C%20v%E2%82%80%20%2B%20%CE%94t*F%E2%82%80%2FMass%20-%20%CE%94t*Coef*v%E2%82%80-%CE%BA*x%E2%82%80%2C%0A%20%20%20%20%20%20%20%20%20COMPLEX(x%E2%82%81%2Cv%E2%82%81)%0A%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20result%20is%20generated%20as%20a%20complex%20number%20which%20is%20absolutely%20unintelligible%2C%20so%20I%20split%20real%20and%20imaginary%20parts%20(position%20and%20velocity)%20for%20presentation.%26nbsp%3B%20I%20then%20hit%20the%20problem%20that%20IMREAL%20and%20IMAGINARY%20do%20not%20accept%20array%20arguments%2C%20so%20I%20have%20to%20use%20MAP.%26nbsp%3B%20Map%20will%20only%20transform%20one%20column%20at%20a%20time%20so%20there%20are%20two%20distinct%20applications%20of%20MAP%2C%20which%20I%20then%20combine%20into%20an%20array.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAP(p%23%2C%20LAMBDA(z%2C%20IMREAL(z)))%0A%0A%3D%20MAP(p%23%2C%20LAMBDA(z%2C%20IMAGINARY(z)))%0A%0A%3D%20IF(%7B1%2C0%7D%2Cx%23%2Cv%23)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThere%20is%20a%20lot%20of%20tidying%20up%20I%20could%20do%2C%20but%20VSCAN%20(or%20equivalent%20solution)%20would%20be%20great%20sometime%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3CP%3EMeanwhile%20there%20is%20a%20lot%20to%20learn%20concerning%20how%20best%20to%20use%20what%20we%20have.%26nbsp%3B%20For%20example%2C%20is%20it%20good%20to%20leave%20the%20anonymous%20Lambda%20functions%20at%20the%20point%20at%20which%20they%20are%20used%3B%20is%20it%20better%20to%20use%20a%20LET%20wrapper%20to%20define%20a%20local%20function%20so%20that%20it%20is%20more%20meaningful%20at%20the%20point%20of%20application.%26nbsp%3B%20A%20step%20further%20and%20one%20hides%20all%20the%20working%20within%20Name%20Manager.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20281px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309043i77ACB5D526F3AAF6%2Fimage-dimensions%2F281x444%3Fv%3Dv2%22%20width%3D%22281%22%20height%3D%22444%22%20role%3D%22button%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-SUB%20id%3D%22lingo-sub-2733905%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733905%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20an%20engineer%20-%20any%20mechanics%20courses%20I%20might%20have%20taken%20are%20a%20distant%20memory%20now!%20That%20said%2C%20I%20am%20interested%20by%20the%20analysis%26nbsp%3Band%20will%20make%20a%20note%20to%20review%20this%20once%20these%20functions%20go%20mainstream.%20One%20minor%20observation%3A%20perhaps%20one%20could%20insert%20a%20%2B%20instead%20of%20using%20MAP%3F%20i.e.%3CBR%20%2F%3E%3DIMREAL(%2Bp%23)%3CBR%20%2F%3E%3DIMAGINARY(%2Bp%23)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVSCAN%2FHSCAN%20were%20mentioned%20in%20some%20Microsoft%20Research%20posts%20I%20remember.%20My%20suggestion%20was%20that%20SCAN%20might%20be%20able%20to%20subsume%20these%20two%20cases%20so%20in%20your%20example%20one%20could%20make%20the%20substitution%20initial%20-%26gt%3B%20Initial*%7B1%2C1%7D%20.%20I%20haven't%20thought%20through%20the%20implications%20fully%20so%20not%20sure%20if%20that%20would%20be%20an%20improvement%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20had%20in%20mind%20combining%20REDUCE%20with%20least%20squares%20functions%20like%20LINEST%20(e.g.%20Gauss-Newton%20method)%20that%20would%20involve%20a%20degree%20of%20'coupling'%20and%20which%20has%20curve%20fitting%20applications%26nbsp%3Bin%20finance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2734269%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2734269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20should%20have%20thought%20of%20that!%20The%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20IF(%7B1%2C0%7D%2C%0A%20%20IMREAL(%2Bp%23)%2C%0A%20%20IMAGINARY(%2Bp%23))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eworks%20fine.%26nbsp%3B%20I%20am%20used%20to%20the%20tactic%20for%20EMONTH%20and%20QUOTIENT%20(though%20MOD%20doesn't%20require%20it).%26nbsp%3B%20Another%20strategy%20would%20be%20to%20make%20'p'%20a%20named%20formula%20rather%20than%20a%20spilt%20range%2C%20which%20tidies%20up%20the%20workbook%2C%20though%20somewhat%20at%20the%20expense%20of%20transparency.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20ideas%20for%20extending%20the%20scope%20of%20SCAN%20sound%20workable%20from%20a%20user%20perspective.%26nbsp%3B%26nbsp%3BI%20come%20up%20against%20the%20problem%20quite%20often%20but%2C%20then%2C%20I%20doubt%20many%20other%20users%20are%20making%20such%20a%20determined%20effort%20to%20get%20rid%20of%20relative%20referencing%20from%20Excel%20solutions%20as%20an%20undesirable%20concept.%26nbsp%3B%20The%20main%20exception%20is%20within%20a%20table%20where%20the%20structured%20reference%20%5B%40Column1%5D%20is%20still%20needed%20and%20meaningful%20in%20context.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2734366%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2734366%22%20slang%3D%22en-US%22%3E%3CP%3ESomething%20I%20should%20have%20said%2C%20is%20that%20the%20new%20functionality%20is%20superb%20to%20use%2C%20despite%20my%20appearing%20to%20carp%20at%20shortcomings.%26nbsp%3B%20Two%20years%20ago%20I%20identified%20the%20building%20of%20accumulation%2Fcorkscrews%20as%20a%20problem%20as%20well%20as%20the%20inability%20to%20aggregate%202D%20arrays%20by%20row%20or%20by%20column.%26nbsp%3B%20Both%20of%20those%20issues%20are%20addressed%2C%20firstly%20by%20the%20Lambda%20function%20and%20recursion%20and%20then%20made%20usable%20by%20the%20introduction%20of%20the%20Lambda%20helper%20functions%20SCAN%2FBYCOL.%26nbsp%3B%20The%20challenge%20is%20now%20to%20develop%20techniques%20to%20exploit%20the%20new%20functionality%20to%20the%20full%20and%20in%20a%20way%20that%20the%20logic%20of%20the%20solution%20shines%20through.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2735980%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2735980%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%3EI%20thought%20I'd%20mock%20this%20up%20using%20the%20proto-CORKSCREW%20function%20I%20had%20put%20together%20previously%3B%20in%20terms%20of%20efficiency%2C%20I%20think%20your%20use%20of%20COMPLEX%20to%20pass%20a%20tuple%20in%20SCAN%20is%20probably%20better%20than%20this%2C%20since%20it%20does%20seem%20to%20sputter%20after%20hitting%20enter.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DREDUCE(%0A%20%20%20%20%7B0%2C-0.1%2C1%2C0%2C2%7D%2C%0A%20%20%20%20SEQUENCE(N)%2C%0A%20%20%20%20LAMBDA(acc%2Cval%2C%0A%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20get_prior%2CLAMBDA(x%2CINDEX(acc%2CROWS(acc)%2Cx))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAKEARRAY(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(acc)%2B1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20COLUMNS(acc)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(i%2Cj%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20i%26lt%3B%3DROWS(acc)%2CINDEX(acc%2Ci%2Cj)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20k%2Cget_prior(1)%2B1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20t_%2Cget_prior(2)%2B%CE%94t%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20F_t%2CXLOOKUP(t_%2C%7B0%3B1%3B4%3B6%7D%2C%7B1%3B0%3B1%3B0%7D%2C%2C-1)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20x_t%2Cget_prior(4)%2B%CE%94t*get_prior(5)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20v_t%2Cget_prior(5)%2B%CE%94t*F_t%2FMass-%CE%94t*Coef*get_prior(5)-%CE%BA*get_prior(4)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CHOOSE(j%2Ck%2Ct_%2CF_t%2Cx_t%2Cv_t)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2742122%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2742122%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3Bis%20probably%20holding%20his%20head%20in%20horror%20at%20the%20things%20these%20dammed%20users%20are%20putting%20his%20code%20through!%26nbsp%3B%20Put%20some%20good%20functionality%20out%20there%20and%20they%20take%20the%20Micky%20by%20nesting%20Lambda%20functions%205%20deep%20in%20formulas%20that%20barely%20fit%20the%20screen.%3C%2FP%3E%3CP%3EI%20am%20picking%20up%20ideas%20from%20you%20though%3B%20the%20definition%20of%20the%20Lambda%20function%20'get_prior(%20)'%20within%20LET%20as%20a%20locally-scoped%20function%20is%20something%20I%20have%20adopted.%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formulation%20would%20generalise%20to%204%20problem%20variables%20better%20than%20mine%20but%20a%20lot%20of%20cpu%20cycles%20appear%20to%20be%20required%20in%20order%20to%20copy%20values%20from%20level%20to%20the%20next.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2750870%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2750870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20continued%20to%20explore%20areas%20of%20difficulty%20in%20implementing%20dynamic%20array%20solutions%20that%20Lambda%20functions%20may%20solve.%26nbsp%3B%26nbsp%3BThe%20first%20is%20habit%20of%20aggregation%20functions%20to%20devour%20entire%20arrays%20rather%20than%20working%20through%20them%20term%20by%20term.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollowing%20your%20lead%2C%20I%20used%20named%20formulas%20defined%20locally%20within%20a%20LET%20function.%26nbsp%3B%20The%20aggregator%20was%20the%20MEDIAN%20function%20which%20can%20be%20useful%20when%20one%20needs%20a%20ramp%20function%2C%20here%20used%20to%20calculate%20overlapping%20intervals%20(could%20be%20overtime%20calculation%2C%20marginal%20tax%20rates%2C%20bonus%20levels%20etc.)%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%2F310434i46BCC3D3F626AAD8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20formula%20is%20somewhat%20on%20the%20heavy%20side%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%5C0%2C%20%22Define%20Lambda%20function%22%2C%0A%20%20%20%20%20OVERLAP%CE%BB%2C%0A%20%20%20%20%20%20%20LAMBDA(lower%2Cupper%2C%0A%20%20%20%20%20%20%20LAMBDA(start%2Cend%2C%0A%20%20%20%20%20%20%20%20%20MEDIAN(end%2C%20lower%2C%20upper)%20-%0A%20%20%20%20%20%20%20%20%20MEDIAN(start%2C%20lower%2C%20upper)%0A%20%20%20%20%20))%2C%0A%20%20%5C1%2C%20%22Apply%20function%22%2C%0A%20%20%20%20%20%20MAP(start%2C%20end%2C%20OVERLAP%CE%BB(lowerThreshold%2C%20upperThreshold))%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20it%20works%20OK.%26nbsp%3B%20Despite%20that%2C%20it%20is%20not%20easily%20assimilated%20as%20a%20worksheet%20formula%20(I%20can%20see%20most%20users%20looking%20on%20in%20blank%20incomprehension%20or%20running%20for%20the%20hills!)%26nbsp%3B%20Loading%20the%20Lambda%20function%20to%20a%20defined%20name%20helps%20the%20worksheet%2C%20but%20at%20the%20expense%20of%20obscuring%20the%20definition%20of%20the%20Lambda%20function.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAP(start%2Cend%2C%20OVERLAP%CE%BB(lowerThreshold%2CupperThreshold))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAn%20idea%20that%20I%20came%20up%20with%20was%20a%20GLOBAL%20function%20that%20would%20operate%20like%20LET%20but%20also%20upload%20the%20local%20name%20to%20the%20workbook%20names%20collection.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20GLOBAL(%0A%20%20%5C0%2C%20%22Define%20Lambda%20function%22%2C%0A%20%20%20%20OVERLAP%CE%BB%2C%0A%20%20%20%20%20%20LAMBDA(lower%2Cupper%2C%0A%20%20%20%20%20%20LAMBDA(start%2Cend%2C%0A%20%20%20%20%20%20%20%20MEDIAN(end%2C%20lower%2C%20upper)%20-%0A%20%20%20%20%20%20%20%20MEDIAN(start%2C%20lower%2C%20upper)%0A%20%20%20%20%20))%2C%0A%20%20%20%5C1%2C%20%22Include%20comment%20for%20upload%22%2C%0A%20%20%20%22OVERLAP%CE%BB(lowerThreshold%2CupperThreshold)(start%2Cend)%22%0A%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EDo%20you%20think%20such%20a%20function%20would%20be%20useful%2C%20or%20is%20it%20an%20idea%20to%20be%20consigned%20to%20the%20crazy%20bin%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20problem%20I%20addressed%20within%20the%20workbook%20is%20that%20of%20the%20...IFS%20family%20of%20conditional%20aggregations%20expecting%20a%20Range%20reference%2C%20so%20they%20will%20not%20accept%20functions%20of%20the%20criteria%20arrays%20as%20parameters.%26nbsp%3B%20The%20alternative%20strategy%20of%20FILTER%20and%20aggregate%20is%20fine%20but%2C%20by%20its%20nature%2C%20will%20not%20accept%20an%20array%20of%20criteria%20values.%26nbsp%3B%20Enter%20MAP.%26nbsp%3B%20The%20model%20aggregates%20by%20day%20of%20the%20week%2C%20which%20could%20also%20be%20achieved%20by%20using%20a%20helper%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2751229%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2751229%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%3EI'm%20intrigued%20by%20this%20idea%20you%20present%20for%20GLOBAL%2C%20but%20I'm%20failing%20to%20understand%20how%20it%20functions%3B%20we're%20talking%20about%20a%20VBA%20UDF%20call%20GLOBAL%20that%20performs%20the%20steps%20similar%20to%20the%20macro%20you%20had%20previously%2C%20or%20something%20else%20completely%3F%20It%20reminds%20me%20of%20wayback%20when%20I%20was%20playing%20with%20EVALUATE%20as%20a%20lambda%20and%20doing%20a%20lookup%20on%20a%20table-column%20with%20my%20LAMBDA%20definitions%20so%20I%20could%20try%20getting%20around%20the%20annoyance%20of%20the%20Name%20Manager%3B%20it%20worked%2C%20but%20it%20wasn't%20friendly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20like%20the%20MAP%20application%20on%20the%20...IFS%3B%20I%20thought%20I%20had%20remembered%20building%20a%20LAMBDA%20that%20worked%20with%20AGGREGATE%20as%20you%20have%20with%20AVERAGE%20here%2C%20but%20I%20was%20mistaken%2C%20my%20implementation%20was%20a%20BYROW%2FBYCOL%20equivalent%20using%20MAKEARRAY.%20Realizing%20that%20just%20re-emphasized%20my%20last%20comment%20on%20a%20separate%20thread%20about%20the%20similarities%20of%20MAKEARRAY%20and%20MAP.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'd%20very%20much%20like%20is%20if%20we%20could%20have%20an%20array%20of%20LAMBDAs%2C%20i.e.%20%7BLAMBDA(x%2Cx%2B1)%2CLAMBDA(y%2Cy*3)%2C...%7D%20that%20could%20then%20be%20the%20array%20parameter%20in%20our%20SCAN%20helper%20for%20instance.%20Or%20having%20a%20curried%20LAMBDA%20in%20our%20MAP%20function%2C%20i.e.%20MAP(array_1%2CLAMBDA(val%2CLAMBDA(x%2C...))%20so%20that%20we%20return%20an%20array%20of%20LAMBDAs%20ready%20to%20take%20a%20parameter.%20I%20don't%20have%20specific%20use%20cases%20in%20mind%20off%20the%20top%20of%20my%20head%2C%20but%20I%20think%20it%20might%20make%20some%20lengthier%20constructions%20easier%20to%20process%20for%20the%20end-user.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752105%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGLOBAL%20is%20an%20%3CU%3Eidea%3C%2FU%3E%20for%20a%20native%20Excel%20formula%20(along%20with%20a%20potential%20partner%20in%20crime%2C%20LOCAL%2C%20which%20would%20upload%20a%20name%20definition%20to%20the%20Sheet%20names%20collection).%26nbsp%3B%20As%20yet%2C%20it%20has%20not%20even%20passed%20the%20first%20hurdle%20towards%20being%20a%20feature%20request.%26nbsp%3B%20Does%20the%20idea%20even%20make%20sense%3B%20would%20it%20help%20close%20the%20divide%20between%20the%20experience%20of%20formula%20development%20on%20the%20worksheet%20and%20that%20of%20Name%20Manager%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20think%20about%20a%20demonstrator%20and%20it%20would%20most%20likely%20draw%20upon%20the%20functionality%20of%20the%20Macro%20you%20referred%20to%20from%20my%20former%20work.%26nbsp%3B%20That%20required%20the%20user%20to%20lay%20out%20the%20name%2C%20the%20comment%20and%20a%20working%20formula%20implementing%20an%20anonymous%20Lambda%20function%20in%20a%20predefined%20pattern%20on%20the%20grid%20and%20manually%20run%20a%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20implement%20the%20GLOBAL%20function%20would%20either%20require%20it%20to%20be%20a%20Lambda%20function%20or%20a%20UDF.%26nbsp%3B%20The%20catch%20is%20that%20the%20first%20parameter%20defining%20the%20name%20would%20produce%20a%20%23NAME!%20error%20first%20time%20round%2C%20that%20would%20prevent%20a%20UDF%20from%20working%2C%20though%20I%20suppose%20I%20could%20required%20it%20to%20be%20provided%20in%20the%20form%20of%20a%20string.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20challenge%20is%20that%20UDFs%20are%20not%20allowed%20to%20change%20defined%20names%2C%20so%20the%20real%20action%20would%20need%20to%20be%20triggered%20by%20a%20worksheet%20change%20event.%26nbsp%3B%20%26nbsp%3BAnother%20idea%20for%20a%20demonstrator%20implementation%20was%20to%20allow%20GLOBAL%26nbsp%3Bitself%20to%20be%20a%20named%20Lambda%20function%20which%20would%20reduce%20to%20a%20LET%20function.%26nbsp%3B%20I%20have%20in%20the%20past%20done%20things%20like%20this%20to%20allow%20a%20function%20%E2%88%9A(2)%20to%20be%20synonymous%20with%20SQRT(2)%20-%20pointless%20but%20kind%20of%20fun.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20present%20case%2C%20it%20was%20getting%20late%20and%20it%20was%20too%20complicated%2C%20so%20there%20is%20no%20concept%20demonstrator.%26nbsp%3B%20Another%20option%20might%20be%20to%20suggest%20it%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F137306%22%20target%3D%22_blank%22%3E%40Charles%20Williams%3C%2FA%3E%26nbsp%3Bto%20see%20whether%20he%20can%20be%20tempted%20to%20implement%20the%20idea%20as%20a%20C%20addin.%26nbsp%3B%20Though%20I%20think%20GLOBAL%20might%20be%20a%20good%20idea%2C%20I%20am%20not%20at%20a%20point%20where%20I%20could%20even%20mount%20an%20argument%20that%20this%20is%20the%20killer%20function%20that%20would%20integrate%20the%20user%20experience%20for%20spreadsheet%20development.%26nbsp%3B%20At%20present%2C%20I%20simply%20welcome%20discussion%20which%20might%20add%20to%20my%20education!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752299%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752299%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%3EI%20believe%20one%20could%20create%20a%20VBA%20prototype%20e.g.%20%3CEM%3EGLOBAL_%3C%2FEM%3E%20by%20using%20an%20indirect%20call%20such%20as%20%3CEM%3EEvaluate(%22AddName()%22)%3C%2FEM%3E%20where%20%3CEM%3EAddName()%3C%2FEM%3E%26nbsp%3Bcontains%20the%20%3CEM%3ENames.Add%3C%2FEM%3E%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20has%20potential%20to%20alter%20range%20dependencies%20during%20calculation%20though%2C%20my%20preference%20would%20be%20for%20LAMBDAs%20to%20be%20called%20from%20cells%20as%20well%20as%20names%20-%20if%20there%20were%20an%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752431%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3EI%20had%20tried%20experimenting%20with%20SET.NAME%20and%20DEFINE.NAME%20early%20on%20when%20I%20was%20playing%20with%20LAMBDA(x%2CEVALUATE(x))%20for%20helping%20debug%20my%20constructions%2C%20but%20couldn't%20get%20it%20to%20work.%20I%20was%20able%20to%20get%20a%20handful%20of%20XLMs%20working%20via%20Name%20Manager%2C%20but%20only%20ones%20that%20returned%20info%2C%20nothing%20that%20altered%20the%20workbook.%20In%20my%20XLM%20reference%20I%20don't%20see%20a%20NAMES.ADD%2C%20can%20you%20reference%20the%20syntax%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752648%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752648%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%26nbsp%3B%3CEM%3EEvaluate%3C%2FEM%3E%26nbsp%3Band%20%3CEM%3ENames.Add%3C%2FEM%3E%26nbsp%3Brefer%20to%20VBA%20functions%20-%20presumably%20equivalent%20to%20the%20legacy%26nbsp%3B%3CEM%3EEVALUATE%3C%2FEM%3E%26nbsp%3Band%26nbsp%3B%3CEM%3EDEFINE.NAME%3C%2FEM%3E%26nbsp%3Bmacro%20functions%20though%20XLM%20has%20more%20resrictions%20within%20udfs.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2753185%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2753185%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%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%20Decided%20to%20mock-up%20my%20%22array%20of%20LAMBDAs%22%20suggestion%20since%20EVALUATE%20was%20revived%20in%20my%20memory.%20Perhaps%20I%20can%20configure%20this%20idea%20to%20work%20with%20MAKEARRAY%20similar%20to%20the%20proto-CORKSCREW%20previously%20shown%2C%20will%20give%20that%20a%20try%20later.%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%22Array_of_LAMBDAs.jpg%22%20style%3D%22width%3A%20644px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310614iCA44F7FC4B68E3E1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Array_of_LAMBDAs.jpg%22%20alt%3D%22Array_of_LAMBDAs.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2754397%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2754397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20quite%20as%20devious%20as%20your%20thinking%20but%20I%20got%20as%20far%20as%20defining%20a%20GLOBAL%CE%BB%20to%20be%20the%20Lambda%20function%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LAMBDA(name%2C%CE%BBfn%2C%CE%BBfn)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ethat%20takes%20a%20name%20and%20an%20anonymous%20Lambda%20function%20and%20returns%20the%20function.%26nbsp%3B%20So%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20GLOBAL%CE%BB(%E2%88%9A%2C%20LAMBDA(x%2CSQRT(x)))(25)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewould%20be%20a%20valid%20formula%2C%20despite%20the%20fact%20that%20the%20math%20symbol%20'%E2%88%9A'%20will%20evaluate%20to%20a%20%23NAME!%20or%20%23CALC!%20error.%26nbsp%3B%20The%20next%20step%20is%20to%20read%20the%20formula%20using%20the%20VBA%20change%20event.%26nbsp%3B%20The%20formula%20would%20be%20parsed%20and%20the%20global%20name%20set%20up%20(as%20I%20do%20now)%20but%20reading%20from%20the%20formula%20text%20rather%20than%20a%20pattern%20of%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2755086%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2755086%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%3EIt's%20an%20interesting%20idea.%20Given%20the%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DGLOBAL_(%E2%88%9A%2C%20LAMBDA(x%2CSQRT(x)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20code%20could%20be%20inserted%20into%20a%20new%20VBA%20module%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EDim%20namedef(1)%20As%20String%0A%0AFunction%20GLOBAL_(ParamArray%20args())%20As%20Boolean%0A%0A%20%20%20%20Application.Volatile%20False%0A%20%20%20%20%0A%20%20%20%20Formula%20%3D%20Trim(Mid(Application.ThisCell.FormulaLocal%2C%202))%0A%20%20%20%20Namestr%20%3D%20Mid(Formula%2C%20InStr(Formula%2C%20%22%2C%22)%20%2B%201)%0A%20%20%20%20%0A%20%20%20%20namedef(1)%20%3D%20%22%3D%22%20%26amp%3B%20Left(Namestr%2C%20InStrRev(Namestr%2C%20%22)%22)%20-%201)%0A%20%20%20%20namedef(0)%20%3D%20Mid(Split(Formula%2C%20%22%2C%22)(0)%2C%209)%0A%20%20%20%20%0A%20%20%20%20Evaluate%20%22addname()%22%0A%20%20%20%20GLOBAL_%20%3D%20True%0A%0AEnd%20Function%0A%0AFunction%20addname()%0A%20%20%20%20Names.Add%20namedef(0)%2C%20namedef(1)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20appears%20to%20do%20the%20job%20on%20my%20setup%20(tested%20without%20access%20to%20LAMBDA).%20Clearly%20there%20is%20room%20for%20improvement%20in%20the%20string%20parsing%20to%20handle%20more%20general%20cases.%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20reservation%20I%20would%20have%20with%20worksheet%20functions%20like%20GLOBAL%20is%20introducing%20the%20ability%20to%20alter%20workbook%20state%20which%20is%20not%20strictly%20consistent%20with%20the%20functional%20programming%20paradigm.%20Possible%20side%20effects%20may%20include%20losing%20undo%20and%20changing%20range%20dependencies%20within%20the%20workbook%20during%20a%20calculation%20cycle.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20button%20or%20hyperlink%20upload%20tool%20would%20be%20a%20safer%20approach%20at%20the%20expense%20of%20additional%20user%20interaction.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2755773%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2755773%22%20slang%3D%22en-US%22%3E%3CP%3EP.S.%20The%20attachment%20contains%20the%20following%20modification%20to%20the%26nbsp%3BGLOBAL%CE%BB%20definition%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LAMBDA(name%2C%CE%BBfn%2CIF(GLOBAL_()%2C%CE%BBfn))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20might%20allow%20Peter's%20original%20formulation%20to%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DGLOBAL%CE%BB(%E2%88%9A%2C%20LAMBDA(x%2CSQRT(x)))(25)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758172%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20cool%20that%20works.%20In%20a%20recent%20blog%20post%2C%20it%20sounded%20like%20XLM%20is%20going%20to%20be%20turned%20off%20by%20default%20for%20security%20reasons.%20I%20wonder%20if%20one%20can%20use%20CHOOSE(%7B1%3B2%3B3%7D%2C...)%20with%20multiple%20LAMBDAs%20as%20well%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%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%3EI%20played%20around%20some%20more%20and%20discovered%20one%20can%20also%20add%20names%20via%20data%20validation!%20This%20removes%20the%20need%20for%20creating%20functions%20with%20side%20effects%20and%20also%20works%20with%20undo.%20I've%20attached%20another%20example%20which%20I%20think%20could%20be%20workable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758302%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758302%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%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%20Success!%20I%20was%20able%20to%20avoid%20EVALUATE%20by%20using%20CHOOSE%20to%20create%20my%20array%20of%20LAMBDAs!%20Then%20by%20creating%20a%20COMPOSE%20lambda%2C%20I'm%20able%20to%20reduce%20an%20array%20of%20LAMBDA%20using%20COMPOSE.%20I%20think%20Peter%20will%20be%20able%20to%20run%20with%20this%20more%20quickly%20than%20I%20will%2C%20but%20I%20think%20its%20very%20promising!%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-09-16%20173040.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310880i972FA60BAD22369B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-09-16%20173040.png%22%20alt%3D%22Screenshot%202021-09-16%20173040.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758311%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758311%22%20slang%3D%22en-US%22%3EI%20think%20lightning%20struck%20us%20at%20the%20same%20time%20regarding%20CHOOSE%20from%20looking%20at%20the%20timestamps.%20Just%20had%20to%20put%20a%20workbook%20together!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759962%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759962%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%20I've%20applied%20this%20REDUCE%2FCOMPOSE%20construction%20to%20a%20modified%20version%20of%20your%20original%20problem.%20It's%20not%20as%20readable%20as%20the%20LET%20construction%2C%20but%20I%20think%20it%20will%20have%20applications%20for%20manipulations%20that%20frequently%20happen%20together.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-09-17%20064911.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311028iCE9F3A74518E82EA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-09-17%20064911.png%22%20alt%3D%22Screenshot%202021-09-17%20064911.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759990%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759990%22%20slang%3D%22en-US%22%3EOne%20thing%20that%20was%20on%20my%20wishlist%20that%20this%20re-emphasizes%3A%20if%20some%20legacy%20functions%20were%20converted%20to%20be%20LAMBDAs%2C%20we%20could%20have%20%3DREDUCE(UNIQUE%2C...)(team)%20instead%20of%20%3DREDUCE(LAMBDA(x%2CUNIQUE(x))%2C...)(team).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2761734%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2761734%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20one%20thing%20to%20know%20that%20Lambda%20functions%20are%20first%20class%20citizens%20of%20the%20calculation%20and%20can%20be%20passed%20as%20arguments%20of%20other%20functions%2C%20it%20is%20another%20to%20achieve%20anything%20useful%20with%20the%20functionality.%26nbsp%3B%20Congratulations.%26nbsp%3B%20Presumably%20it%20is%20the%20MAKEARRAY%20that%20allows%20the%20formula%20to%20return%20the%202D%20array%20of%20results%20but%20following%20the%20formula%20is%20far%20from%20straightforward.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20connection%20with%20your%20other%20post%2C%20it%20does%20sometime%20seem%20odd%20having%20to%20create%20a%20Lambda%20function%20that%20does%20no%20more%20than%20the%20built-in%20function%20it%20implements%20%5Bfor%20me%20it%20might%20be%26nbsp%3BSUM%CE%BB%20which%20is%20simply%20a%20restricted%20version%20of%20SUM%5D.%26nbsp%3B%20It%20has%20yet%20to%20cause%20me%20grief%20though%20and%2C%20at%20present%2C%20I%20find%20the%20presence%20of%20the%26nbsp%3B%CE%BB%20a%20useful%20reminder.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeanwhile%2C%20I%20have%20been%20focussing%20on%20simpler%20problems%20on%20this%20forum.%26nbsp%3B%20For%20example%2C%20to%20unpivot%20a%202D%20array%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%22UNPIVOT%CE%BB%22%0A%3D%20LAMBDA(grid%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20fullList%2C%20REDUCE(%20%2C%20grid%2C%20APPEND%CE%BB)%2C%0A%20%20%20%20%20%20SORT(UNIQUE(fullList))%0A%20%20%20%20))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20APPEND%CE%BB%20is%20defined%20by%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%22APPEND%CE%BB%22%0A%3D%20LAMBDA(list%2Cvalue%2C%0A%20%20%20%20IF(value%3D%22%22%2C%20list%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20n%2C%20COUNTA(list)%2C%0A%20%20%20%20%20%20k%2C%20SEQUENCE(n%2B1)%2C%0A%20%20%20%20%20%20IF(k%26lt%3B%3Dn%2C%20list%2C%20value))%0A%20%20))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20have%20actually%20used%20REDUCE%20to%20run%20over%20a%202D%20array%20%3CSPAN%3Ein%20row-major%20order%20to%3C%2FSPAN%3E%26nbsp%3Bdeliver%20value!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2762318%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2762318%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20little%20less%20convoluted%20in%20terms%20of%20application%2C%20I%20think.%20Using%20the%20appropriate%20functions%20with%20SCAN%2C%20I%20get%20the%20minimal%20set%20of%20values%20that%20encapsulate%20the%20changes%2C%20then%20break%20those%20changes%20out%20into%20their%20own%20columns%20in%20the%20final%20step.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20bal%2C100000%2C%0A%20%20%20%20nper%2C120%2C%0A%20%20%20%20rate%2C5%25%2F12%2C%0A%20%20%20%20pmt_%2CPMT(rate%2Cnper%2C-bal)%2C%0A%20%20%20%20array_1%2C%0A%20%20%20%20%20%20%20%20MAKEARRAY(nper%2C3%2CLAMBDA(i%2Cj%2Cj)%20%20%20)%2C%0A%20%20%20%20fxns%2C%0A%20%20%20%20%20%20%20%20CHOOSE(array_1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(x%2Cx)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(x%2Cx*(1%2Brate))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(x%2Cx-pmt_)%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20amort%2CSCAN(bal%2Cfxns%2CLAMBDA(acc%2Cfn%2Cfn(acc)))%2C%0A%20%20%20%20CHOOSE(SEQUENCE(%2C4)%2CINDEX(amort%2C%2C1)%2CINDEX(amort%2C%2C2)-INDEX(amort%2C%2C1)%2CINDEX(amort%2C%2C1)-INDEX(amort%2C%2C3)%2CINDEX(amort%2C%2C3))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2762731%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2762731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooks%20impressive!%20I'll%20need%20to%20see%20if%20there's%20a%20Python%20equivalent%20formulation%20to%20get%20my%20head%20around%20it.%20Regarding%20the%20last%20line%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCHOOSE(SEQUENCE(%2C4)%2C%0A%20%20%20%20%20INDEX(amort%2C%2C1)%2C%0A%20%20%20%20%20INDEX(amort%2C%2C2)-INDEX(amort%2C%2C1)%2C%0A%20%20%20%20%20INDEX(amort%2C%2C1)-INDEX(amort%2C%2C3)%2C%0A%20%20%20%20%20INDEX(amort%2C%2C3))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20looks%20fairly%20readable%20when%20placed%20on%20separate%20lines%20but%20might%20sometimes%20be%20worth%20creating%20a%20LAMBDA%20function%20inline%20in%20such%20cases%20so%20as%20to%20replace%26nbsp%3B%3CEM%3EINDEX(amort%2C%2Ci)%3C%2FEM%3E%20as%20%3CEM%3Ecol(i)%3C%2FEM%3E%2C%20say.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20option%2C%20given%20people%20who%20use%20higher%20order%20functions%20would%20generally%20be%20familiar%20with%20matrices%2C%20might%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DMMULT(amort%2CTRANSPOSE(%7B1%2C0%2C0%3B-1%2C1%2C0%3B1%2C0%2C-1%3B0%2C0%2C1%7D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2762866%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2762866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20quite%20an%20achievement%3B%20an%20entire%20amortisation%20schedule%20in%20a%20cell.%26nbsp%3B%20Rather%20like%20the%20numerical%20analogue%20of%20the%20ship%20in%20a%20bottle!%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%20185px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311244iC11C467B336E8C1F%2Fimage-dimensions%2F185x115%3Fv%3Dv2%22%20width%3D%22185%22%20height%3D%22115%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIt%20might%20take%20me%20a%20while%20to%20assimilate%20the%20implications%20of%20this%20and%20related%20initiatives.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764018%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20some%20advice%20concerning%20the%20original%20'array%20of%20arrays'%20problem.%26nbsp%3B%20Essentially%20the%20advice%20was%20'rather%20than%20trying%20to%20create%20arrays%20of%20arrays%2C%20reduce%20the%20array%20to%20'thunks'%2C%20which%20can%20be%20handled%20as%20scalars%20and%20evaluated%20at%20the%20end%20using%20MAKEARRAY.%26nbsp%3B%20In%20the%20event%2C%20I%20didn't%20even%20need%20to%20do%20that%20because%20I%20was%20already%20working%20with%20Lambda%20functions%20(team%20name%20as%20the%20parameter)%20which%20evaluated%20correctly.%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%20955px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311324i98F5B60D5DD760D2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20teamList%2C%20UNIQUE(team)%2C%0A%20%20%20%20teamScore%2C%20MAP(teamList%2C%20SCORE%CE%BB)%2C%0A%20%20%20%20teamOrder%2C%20SORTBY(teamList%2C%20teamScore)%2C%0A%20%20%20%20result%2C%20MAKEARRAY(COUNTA(teamList)%2C%204%2C%0A%20%20%20%20%20%20LAMBDA(t%2Cp%2CINDEX(MEMBERS%CE%BB(INDEX(teamOrder%2Ct))%2C%20p))%0A%20%20%20%20)%2C%0A%20%20IFERROR(result%2C%20%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764070%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764070%22%20slang%3D%22en-US%22%3EThat's%20a%20nice%20clean%20construction%20that%20encapsulates%20our%20journey%20from%20your%20original%20post!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764241%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764241%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%3ERevisited%20your%20particle%20dynamics%20and%20reworked%20so%20that%20the%20values%20are%20passed%20in%20LAMBDA%20function%20rather%20than%20as%20COMPLEX%2C%20taking%20advantage%20of%20what%20we've%20learned%20about%20arrays%20of%20LAMBDAs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20pairs%2C%0A%20%20%20%20%20%20%20%20SCAN(LAMBDA(x%2CCHOOSE(x%2C0%2C2))%2C%20F%23%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(coord%2CF%E2%82%80%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20x%E2%82%80%2Ccoord(1)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20v%E2%82%80%2C%20coord(2)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20x%E2%82%81%2C%20x%E2%82%80%20%2B%20%CE%94t*v%E2%82%80%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20v%E2%82%81%2C%20v%E2%82%80%20%2B%20%CE%94t*F%E2%82%80%2FMass%20-%20%CE%94t*Coef*v%E2%82%80-%CE%BA*x%E2%82%80%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(x%2CCHOOSE(x%2Cx%E2%82%81%2Cv%E2%82%81))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20x%2CMAP(pairs%2CF%23%2CLAMBDA(fn%2Cignored%2Cfn(1)))%2C%0A%20%20%20%20v%2CMAP(pairs%2CF%23%2CLAMBDA(fn%2Cignored%2Cfn(2)))%2C%0A%20%20%20%20CHOOSE(%7B1%2C2%7D%2Cx%2Cv)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2765732%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2765732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESooner%20or%20later%2C%20I%20may%20try%20to%20generalise%20your%20formulae%20to%20cover%20more%20degrees%20of%20freedom.%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%20756px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311459i3382A3581B696820%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFirst%2C%20however%2C%20I%20think%20I%20need%20to%20take%20time%20out%20to%20review%20the%20progress%20we%20(mainly%20you)%20have%20made.%26nbsp%3B%20I%20need%20to%20analyse%20the%20lessons%20learnt%20and%20would%20like%20to%20be%20sure%20I%20can%20apply%20them%20when%20the%20situation%20arises.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767179%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767179%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%3EI%20think%20I'm%20working%20my%20way%20around%20to%20what%20your%20contact%20had%20suggested%20for%20passing%20thunks%2C%20please%20see%20below.%20I've%20not%20figured%20out%20how%20best%20to%20incorporate%20the%20changing%20F%20value%2C%20and%20I%20seem%20to%20have%20screwed%20up%20the%20calc%20somewhere%2C%20but%20that's%20probably%20an%20error%20on%20my%20part.%20This%20is%20based%20on%20an%20ACCUMULATE%20I've%20built%20to%20use%20an%20array%20of%20LAMBDAs%2C%20but%20in%20this%20case%20I'm%20only%20passing%20in%20the%20single%20LAMBDA.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLAMBDA(init_value%2Cfuncs%2C%5Biterations%5D%2C%0A%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20fxns%2CIF(TYPE(funcs)%3D64%2Cfuncs%2CCHOOSE(%7B1%7D%2Cfuncs))%2C%0A%20%20%20%20%20%20%20%20%20iter%2CIF(ISOMITTED(iterations)%2C1%2Citerations)%2C%0A%20%20%20%20%20%20%20%20%20fxns_array%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20MAKEARRAY(iter*ROWS(fxns)%2CCOLUMNS(fxns)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LAMBDA(i%2Cj%2CINDEX(fxns%2CMOD(i-1%2CROWS(fxns))%2B1%2Cj))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20thunks%2CSCAN(LAMBDA(init_value)%2Cfxns_array%2CLAMBDA(acc%2Cfn%2CLET(carry%2Cacc()%2Ccurr%2Cfn(carry)%2CLAMBDA(curr))))%2C%0A%20%20%20%20%20%20%20%20%20CHOOSE(%7B1%2C2%7D%2CMAP(thunks%2CLAMBDA(thunk%2CINDEX(thunk()%2C1%2C1)))%2CMAP(thunks%2CLAMBDA(thunk%2CINDEX(thunk()%2C2%2C1))))%0A%20%20%20%20%20)%0A)(%7B0%3B2%7D%2CLAMBDA(xv%2Cxv%2B%CE%94t*MMULT(CHOOSE(%7B1%2C2%3B3%2C4%7D%2C0%2C1%2C-%CE%BA%2C-Coef)%2Cxv)%2B%CE%94t*CHOOSE(%7B1%3B2%7D%2C0%2CF%2FMass))%2C12)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767730%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20revisited%20the%20earlier%20depreciation%20example%20and%20am%20posting%20associated%20Python%20code%20in%20the%20hope%20that%20it%20might%20help%20make%20sense%20of%20these%20different%20approaches.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Efrom%20itertools%20import%20accumulate%0Afrom%20numpy%20import%20array%2C%20reshape%2C%20transpose%0A%0An%3D9%0Abal%3D60000%0Ar%3D%5B0.9%2C0.88%5D%0A%0A%231.%202d%20array%0Ascan1%3Daccumulate(array(%5Br%5D*n)%2Clambda%20acc%2Cr%3Aacc*r%2Cinitial%3D%5Bbal%5D*2)%0Aprint(transpose(list(scan1)))%0A%0A%232.%20Generator%0Af%3Dlambda%20rate%3Aaccumulate(%5Brate%5D*n%2Clambda%20acc%2Cr%3Aacc*r%2Cinitial%3Dbal)%0Ascan2%3Dlist(map(f%2Cr))%0Aprint(%5Blist(scan2%5B0%5D)%2Clist(scan2%5B1%5D)%5D)%0A%0A%233.%20Composition%0Ascan3%3Daccumulate(%5Bbal%5D%2B%5Blambda%20x%3Ax*r%5B0%5D%5D*n%0A%20%20%20%20%20%20%20%2B%5Blambda%20x%3Abal%5D%2B%5Blambda%20x%3Ax*r%5B1%5D%5D*n%2Clambda%20acc%2Cf%3Af(acc))%0Aprint(reshape(list(scan3)%2C(2%2Cn%2B1)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAll%20three%20methods%20return%20the%20same%20results%20as%20in%20the%20workbook%2C%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3E%5B%5B60000%2C%2054000.0%2C%2048600.0%2C%2043740.0%2C%2039366.0%2C%2035429.4%2C%2031886.46%2C%2028697.81%2C%2025828.03%2C%2023245.23%5D%2C%0A%5B60000%2C%2052800.0%2C%2046464.0%2C%2040888.32%2C%2035981.72%2C%2031663.92%2C%2027864.25%2C%2024520.54%2C%2021578.07%2C%2018988.70%5D%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CEM%3ENotes%3C%2FEM%3E%3A%3CBR%20%2F%3E%231.%20is%20not%20(yet)%20an%20option%20in%20Excel%20since%20initial_value%20is%20restricted%20to%20a%20scalar.%20Hopefully%20there%20may%20be%20some%20similar%20option%20available%20in%20future.%3C%2FP%3E%3CP%3E%232.%20In%20Python%20'map'%20and%20'accumulate'%20return%20an%20object%20reference%20to%20be%20passed%20to%20list().%20I%20suppose%20the%20Excel%20equivalent%20would%20pass%20LAMBDA(f)%20to%20MAP()%20as%20a%20'thunk'%20to%20be%20evaluated%20with%20ARRAYMAKE()%3F%3C%2FP%3E%3CP%3E%233.%20This%20follows%20your%26nbsp%3Bapproach%20of%20composing%20an%20array%20of%20lambdas.%20In%20Excel%20I%20guess%20one%20may%20be%20able%20to%20leverage%20the%20row-major%20order%20to%20generate%20the%20results%20and%20then%20transpose%20the%20result%20set%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2772195%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3EThanks%20for%20this%2C%20had%20to%20play%20with%20these%20in%20a%20Jupyter%20notebook%20because%20Python%20doesn't%20live%20in%20my%20head.%20I%20confused%20myself%20because%20I%20had%20created%20an%20ACCUMULATE%20function%20that%20overshadowed%20my%20understanding%20of%20itertools'%20accumulate.%20Will%20update%20once%20I%20have%20more%20answers%20to%20your%20scenarios%2C%20but%20what%20I've%20determined%20so%20far%20is%20I'll%20definitely%20have%20to%20get%20accustomed%20to%20thunking%20vector%2Ftuple-equivalents%20in%20Excel%20to%20be%20able%20to%20work%20with%20the%20LAMBDA%20helpers.%20More%20to%20come!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773789%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20code%20defines%20a%20function%26nbsp%3B%20'scanv'%20for%20a%20vector%20accumulation%20by%20iterating%20the%20equation%20x%E2%82%81%3DAx%E2%82%80%2Bb.%26nbsp%3BThe%20results%20match%20the%20particle%20dynamics%20worksheet%20and%20the%20sequence%20of%20Fibonacci%20pairs.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Efrom%20itertools%20import%20accumulate%0Afrom%20numpy%20import%20array%2C%20transpose%0A%0Ascanv%20%3D%20lambda%20A%2CB%2Cx0%3Aaccumulate(B%2Clambda%20x1%2Cb%3Ax1%40A%2Bb%2Cinitial%3Darray(x0))%0A%0A%23particle%20dynamics%0AA%20%3D%20%5B%5B1%2C-0.05%5D%2C%5B0.1%2C0.99%5D%5D%0AB%20%3D%20%5B0%2C0.1%5D*transpose(%5B%5B1%5D*11%2B%5B0%5D*29%2B%5B1%5D*21%2B%5B0%5D*40%5D)%0Ax0%20%3D%20%5B0%2C2%5D%0Aprint(array(list(scanv(A%2CB%2Cx0))))%0A%0A%23fibonacci%0AA%20%3D%20%5B%5B0%2C1%5D%2C%5B1%2C1%5D%5D%0AB%20%3D%20%5B%5B0%2C0%5D%5D*30%0Ax0%20%3D%20%5B0%2C1%5D%0Aprint(array(list(scanv(A%2CB%2Cx0))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20you%20have%20built%20an%20Excel%20equivalent%20using%20lambdas%20which%20is%20quite%20some%20feat%20-%20I'm%20just%20getting%20a%20bit%20lost%20trying%20to%20deconstruct%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776119%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776119%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20taking%20this%20conversation%20forward.%26nbsp%3B%20I%20know%20some%20others%20have%20looked%20at%20the%20discussion%20but%20hesitated%20to%20get%20involved.%26nbsp%3B%20At%20times%20you%20are%20pushing%20me%20to%20my%20limits%20and%20beyond.%26nbsp%3B%20I%20plan%20to%20keep%20the%20discussion%20for%20future%20reference!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20recent%20days%2Fweeks%2C%20I%20have%20tried%20to%20refactor%20a%20number%20of%20workbooks%20to%20exploit%20the%20new%20Lambda%20helper%20functions%2C%20that%20implement%20ideas%20that%20Lori%20first%20introduced%20me%20to.%26nbsp%3B%20The%20challenge%20of%20producing%20true%202D%20arrays%20rather%20than%20copying%20single%20rows%20seems%20to%20reoccur.%26nbsp%3B%20Whilst%20true%202D%20arrays%20are%20comparatively%20unusual%2C%20I%20would%20estimate%20that%20about%2080%25%20of%20solutions%20have%20array%20calculations%20conducted%20in%20parallel%20and%20would%20benefit%20from%20the%20ability%20to%20handle%20arrays%20of%20array%20(Joe%20did%20say%20%22%3CSPAN%3Enow%20that%20arrays%20are%20more%20prominent%2C%20the%20limitation%20is%20more%20apparent%22%3C%2FSPAN%3E).%26nbsp%3B%20An%20example%20of%20this%20is%20the%2012%20month%20rolling%20forecast%20demonstrated%20by%20Danielle%20Stein-Fairhurst.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D4LXG_oDlFhA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EBuilding%20a%20Rolling%20Forecast%20in%20Excel%20-%20YouTube%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20did%20manage%20to%20produce%20a%20fully-dynamic%20rolling%20forecast%20using%20MAKEARRAY%20and%20a%20Lambda%20function%20FORECAST%CE%BB%20that%20called%20the%20inbuilt%20FORECAST.ETS%20function%20by%20row%20and%20column%20indices%20(line%20item%20number%20and%20period%20counter)%20but%20it%20was%20far%20from%20straightforward%20and%20I%20really%20could%20do%20with%20a%20'How%20to'%20guide%20if%20I%20am%20going%20to%20get%20beyond%20trial%20and%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20yet%20to%20achieve%20the%20objective%20with%20'thunks'%2C%20so%20I%20am%20clearly%20falling%20short%20in%20my%20understanding.%26nbsp%3B%20My%20most%20successful%20approach%20has%20been%20to%20build%20a%20Lambda%20function%20that%20will%20return%20a%20single%20value%20from%20the%20desired%20array%20of%20results.%26nbsp%3B%20MAKEARRAY%20(or%20to%20a%20lesser%20extent%2C%20MAP)%20can%20then%20return%20the%202D%20array%20but%20it%20is%20far%20from%20fool-proof%20(unless%20that%20is%20just%20me%20bringing%20a%20finer%20grade%20of%20foolishness%20to%20the%20problem!)%3C%2FP%3E%3CP%3E%26nbsp%3B%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%2F312223i5B0DF307F98D3C1E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20haven't%20attached%20Danielle's%20workbook%20without%20getting%20permission%20but%20have%2C%20instead%2C%20attached%20a%20simpler%20workbook%20which%20I%20used%20for%20development%20purposes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776720%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776720%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%20Let%20me%20know%20if%20this%20helps%20re%3A%20thunks%3B%20or%20anyone%20following%20along%20in%20the%20audience%20who%20may%20not%20know%20what%20we're%20talking%20about.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20as%20not%20to%20cloud%20the%20process%20with%20anything%20overly%20useful%2C%20I've%20defined%20PEEL%20to%20peel%20an%20array%20like%20an%20onion%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLAMBDA(array%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20rows_%2CROWS(array)%2C%0A%20%20%20%20%20%20%20%20cols_%2CCOLUMNS(array)%2C%0A%20%20%20%20%20%20%20%20INDEX(array%2CSEQUENCE(rows_-2%2C%2C2%2C1)%2CSEQUENCE(%2Ccols_-2%2C2%2C1))%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776739%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776739%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20can%20use%20this%20in%20REDUCE%20with%20no%20problem%20because%20REDUCE%20only%20returns%20the%20final%20result%2C%20unconstrained%20by%20the%20dimensions%20of%20the%202nd%20parameter%2C%20unlike%20MAP%2C%20BYROW%2FBYCOL%2C%20SCAN.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PEEL.png%22%20style%3D%22width%3A%20174px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312293i7A65DFE0B66E4875%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PEEL.png%22%20alt%3D%22PEEL.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22REDUCE.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312294iBBA787B18BD10E82%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22REDUCE.png%22%20alt%3D%22REDUCE.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20we%20try%20it%20in%20SCAN%2C%20we%20get%20an%20error%20because%20the%20results%20break%20the%20dimensions%20of%202nd%20parameter.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SCAN_error.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312296i1611EA0B9E5ADE40%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SCAN_error.png%22%20alt%3D%22SCAN_error.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20we%20thunk%20the%20results%20instead%2C%20we%20can%20get%20all%204%20back%3B%20they%20show%20as%20errors%2C%20but%20the%20arrays%20are%20inside%20the%20thunked%20LAMBDAs.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22thunks.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312297iC3BBFE447DD8499B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22thunks.png%22%20alt%3D%22thunks.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776746%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776746%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20we%20define%20EXPAND%20like%20so%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLAMBDA(array_of_arrays%2Cindx%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20target_thunk%2CINDEX(array_of_arrays%2Cindx%2C1)%2C%0A%20%20%20%20%20%20%20%20target_thunk()%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThen%20we%20can%20use%20it%20to%20select%20and%20return%20a%20thunked%20array.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22EXPAND.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312299i56064A5899757552%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22EXPAND.png%22%20alt%3D%22EXPAND.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776755%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776755%22%20slang%3D%22en-US%22%3E%3CP%3EForgot%20to%20attach%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778291%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClever%20use%20of%20REDUCE%20with%20array%20parameters!%20That%20would%20never%20have%20crossed%20my%20mind.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20wasn't%20immediately%20clear%20to%20me%20exactly%20what%20the%20PEEL%20function%20was%20doing%2C%20maybe%20using%20SEQUENCE(n%2Cn)%20for%20the%20screen%20clipping%20could%20assist%20others%20like%20me%20who%20do%20not%20yet%20have%20access%20to%20these%20new%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20case%2C%20this%20looks%20very%20promising%20for%20working%20around%20the%20array%20truncation%20stumbling%20block%20that%20%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%3Bcites%20in%20the%20original%20post.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778894%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778894%22%20slang%3D%22en-US%22%3E%3CP%3EI%20wonder%20if%20the%20'thunk'%20method%20could%20also%20be%20made%20to%20work%20with%20SCAN%3F%3C%2FP%3E%3CP%3EFor%20reference%2C%20this%20notebook%20code%20returns%20the%20same%20results%20as%20your%20sample%20file%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3E%23%20array%20of%20arrays%0Apeel%20%3D%20lambda%20x%2C_%3Ax%5B1%3A-1%2C1%3A-1%5D%0Ascanp%3Daccumulate(%5B0%5D*4%2Cpeel%2Cinitial%3Dnp.eye(9))%0Alist(scanp)%3C%2FCODE%3E%3C%2FPRE%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%23%20array%20of%20thunks%0Apeelt%20%3D%20lambda%20x%2C_%3Alambda%20%3Ax()%5B1%3A-1%2C1%3A-1%5D%0Ascant%3Daccumulate(%5B0%5D*4%2Cpeelt%2C%20initial%3Dlambda%20%3Anp.eye(9))%0Alist(scant)%5B3%5D()%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%5Bnp.eye%20%3D%20MUNIT%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2782834%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3EPlease%20see%20attached%2C%20modified%20EXPAND%20slightly%20to%20handle%202d%20arrays%20of%20thunks.%20You'll%20note%20that%20for%20most%20of%20these%2C%20SCAN%20omits%20the%20init_value%3B%20this%20can%20be%20overcome%20if%20absolutely%20necessary%2C%20but%20is%20an%20added%20complication.%20I've%20also%20included%20a%20version%20of%20reshape%20that%20I%20tried%20basing%20off%20np.reshape%2C%20but%20it%20still%20needs%20some%20polishing.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLAMBDA(array%2C%5BaRows%5D%2C%5BaCols%5D%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20aRows_%2CIF(OR(ISOMITTED(aRows)%2CaRows%3D%22%22)%2C1%2CINT(aRows))%2C%0A%20%20%20%20%20%20%20%20aCols_%2CIF(OR(ISOMITTED(aCols)%2CaCols%3D%22%22)%2CCOUNTA(array)%2CINT(aCols))%2C%0A%20%20%20%20%20%20%20%20rows_%2CROWS(array)%2C%0A%20%20%20%20%20%20%20%20cols_%2CCOLUMNS(array)%2C%0A%20%20%20%20%20%20%20%20seq%2CSEQUENCE(aRows%2CaCols%2C0%2C1)%2C%0A%20%20%20%20%20%20%20%20r_%2C1%2BQUOTIENT(seq%2Ccols_)%2C%0A%20%20%20%20%20%20%20%20c_%2C1%2BMOD(seq%2Ccols_)%2C%0A%20%20%20%20%20%20%20%20reshaped%2CIFERROR(INDEX(array%2Cr_%2Cc_)%2C%22%22)%2C%0A%20%20%20%20%20%20%20%20result%2CFILTER(reshaped%2CBYROW(reshaped%2CLAMBDA(row_%2CNOT(AND(row_%3D%22%22)))))%2C%0A%20%20%20%20%20%20%20%20result%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20enjoying%20these%20side-by-side%20comparisons%2C%20I've%20got%20another%20one%20for%20you%2C%20earlier%20this%20year%20I%20wrote%20a%20LAMBDA%20to%20extract%20subarrays%20of%20size%20%5Br%20x%20c%5D%20from%20a%20given%20array%20of%20size%20%5Bm%20x%20n%5D%20in%20row-major%20order.%20Originally%20it%20was%20returning%20all%20subarrays%2C%20but%20since%20we%20hadn't%20figured%20out%20how%20to%20handle%20arrays-of-arrays%2C%20I%20was%20just%20using%20ARRAYTOTEXT%2C%20or%20subbing%20in%20an%20appropriate%20function%20to%20get%20back%20a%20single%20value.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22subarrays.jpg%22%20style%3D%22width%3A%20374px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312728i60D702073043484D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22subarrays.jpg%22%20alt%3D%22subarrays.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20that%20we%20are%20thunking%20the%20arrays%2C%20I%20can%20provide%20an%20index%20list%2C%20and%20operate%20on%20specific%20subarrays.%20What%20would%20this%20look%20like%20in%20Python%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22subthunk.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312730i1679040B4EBDD2B7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22subthunk.jpg%22%20alt%3D%22subthunk.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2782837%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782837%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20keep%20forgetting%20about%20the%20attachment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783258%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow!%26nbsp%3B%20If%20this%20were%20an%20art%20class%2C%20I%20am%20still%20working%20through%20Picasso's%20cubist%20period%2C%20whilst%20you%20guys%20are%20moving%20through%20Neoclassism%20and%20into%20the%20Surreal.%26nbsp%3B%20%26nbsp%3BDespite%20that%2C%20I%20have%20made%20a%20little%20progress%20and%20come%20up%20with%20a%20Lambda%20function%20that%20appends%20a%20seasonally%20adjusted%20forecast%20to%20a%20table%20of%20actuals.%26nbsp%3B%20The%20prompt%20shows%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20FORECASTS%CE%BB(timeline%2C%20actuals%2C%20forecastPeriods%2C%20fill)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20the%20code%20reads%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LAMBDA(vec%2Carr%2Cn%2Cfill%2C%0A%20%20%20%20MAKEARRAY(ROWS(arr)%2C%20n%2BCOLUMNS(arr)%2C%0A%20%20%20%20%20%20LAMBDA(row%2Ccol%2C%0A%20%20%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20%20%20M%2C%20%20COLUMNS(arr)%2C%0A%20%20%20%20%20%20%20%20%20%20k%2C%20%20SEQUENCE(1%2CM)%2C%0A%20%20%20%20%20%20%20%20%20%20X%2C%20%20INDEX(vec%2Ccol)%2C%0A%20%20%20%20%20%20%20%20%20%20X0%2C%20INDEX(vec%2Ck)%2C%0A%20%20%20%20%20%20%20%20%20%20Y0%2C%20INDEX(arr%2Crow%2C0)%2C%0A%20%20%20%20%20%20%20%20%20%20IF(col%26gt%3BM%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FORECAST.ETS(X%2CY0%2CX0)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20IF(fill%2C%20INDEX(Y0%2Ccol)%2C%20%22%22)))))%0A%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAt%20the%20moment%2C%20I%20find%20the%20creation%20of%20such%20formulae%20requires%20%3CSPAN%3EHerculean%26nbsp%3Beffort%20but%20no%20doubt%20it%20will%20come%20more%20naturally%20in%20time.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ELooking%20through%20the%20discussion%2C%20I%20think%20we%20need%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3Bto%20have%20an%20insider%20beta%20licence%20to%20bring%20his%20Python%20experience%20back%20to%20the%20fold%20of%20Excel.%26nbsp%3B%20Your%20frequent%20contributions%20have%20been%20missed%20both%20here%20and%20on%20Chandoo.%26nbsp%3B%20I%20have%20memories%20of%20a%20number%20of%20'shortest%20formula'%20competitions%20with%20a%20quite%20remarkable%20degree%20of%20innovation.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20worked%20through%20the%20previous%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3Bworkbook%20with%20the%20reducing%20array%20thunks.%26nbsp%3B%20Can%20a%20thunk%20refer%20to%20a%20previously%20calculated%20array%2C%20just%20waiting%20to%20be%20called%2C%20or%20should%20it%20be%20thought%20of%20as%20a%20recipe%20for%20calculating%20the%20array%20should%20it%20ever%20be%20required%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783294%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783294%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20further%20thought.%26nbsp%3B%20It%20looks%20like%20we%20are%20not%20going%20to%20get%20native%20support%20for%20arrays%20of%20arrays%20or%20arrays%20of%20ranges%20within%20the%20foreseeable%20future%20(%3CEM%3Eno%20doubt%20for%20good%20reason%2C%20including%20the%20potential%20loss%20of%20backward%20compatibility%3C%2FEM%3E)%2C%20but%20I%20wondered%20whether%20a%20single%20formula%20could%20be%20co-opted%20to%20the%20cause.%26nbsp%3B%20%3CSTRONG%3EMAKEARRAY%3C%2FSTRONG%3E%20and%20derived%20Lambdas%20such%20as%20%3CSTRONG%3EVSTACK%3C%2FSTRONG%3E%20are%20already%20capable%20of%20packing%20arrays%20into%20an%20array%20'container'%20with%20padded%20spaces.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20a%20'0'%20parameter%20is%20illegal%20within%20%3CSTRONG%3EMAKEARRAY%3C%2FSTRONG%3E%2C%20but%20it%20would%20be%20reasonable%20to%20interpret%20it%20in%20much%20the%20same%20manner%20as%20a%20'0'%20in%20the%20%3CSTRONG%3EINDEX%3C%2FSTRONG%3E%20function%20(%3CEM%3Ei.e.%3C%2FEM%3E%20'I%20want%20it%20all').%26nbsp%3B%20If%20a%20number%20of%20row%20arrays%20were%20to%20be%20packed%20into%20a%202D%20array%2C%20the%20'0'%20would%20be%20interpreted%20as%20'make%20the%20width%20just%20as%20long%20as%20it%20needs%20to%20be%20to%20accommodate%20the%20longest%20row'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2785083%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2785083%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%3CBR%20%2F%3EThanks%20for%20hosting%20these%20lively%20discussions.%20I%20have%20learned%20a%20number%20of%20useful%20general%20techniques%20from%20the%20examples%20and%20suggestions%20in%20these%20posts.%20These%20threads%20also%20provide%20a%20useful%20way%20to%20feedback%20to%20the%20community%20now%20uservoice%20is%20not%20available.%20And%20yes%2C%20it%20would%20be%20useful%20to%20have%20access%20to%20lambda%20functionality%20for%20testing%2C%20with%20any%20luck%20it%20won't%20be%20too%20much%20longer%20to%20wait%20until%20they%20go%20current!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%3CBR%20%2F%3ENice%20solutions%2C%20It%20will%20take%20some%20time%20to%20fully%20grasp%20these%20techniques!%20Interesting%20exercise%20to%20generate%20subarrays%2C%20one%20option%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Er%2Cc%2Cn%3D2%2C3%2C5%0AA%20%3D%20np.arange(n*n).reshape(n%2Cn)%2B1%0Ap%20%3D%20np.mgrid%5B0%3An-r%2B1%2C0%3An-c%2B1%5D.reshape(2%2C-1)%0Alist(map(lambda%20i%2Cj%3AA%5Bi%3Ai%2Br%2Cj%3Aj%2Bc%5D%2Cp%5B0%5D%2Cp%5B1%5D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20believe%20a%20more%20elegant%20solution%20exists%20using%203D%20array%20manipulations%20without%20lambda%2Fmap%20but%20it%20eludes%20me%20at%20present.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Excel%20I%20wonder%20if%20unions%20of%20ranges%20could%20be%20constructed%20of%20the%20form%20ref1%2Cref2%2C...%2Crefn%20via%20REDUCE%20so%20that%20individual%20areas%20could%20be%20selected%20with%20INDEX.%20OFFSET%20might%20also%20work%20though%20it%20is%20volatile%2C%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM(SUBTOTAL(9%2COFFSET(A%2C%7B0%2C1%2C1%7D%2C%7B0%2C1%2C2%7D%2C2%2C3)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAs%20Peter%20says%20arrays%20of%20ranges%20are%20unsupported%20at%20present%20but%20out%20of%20interest%2C%20can%20MAP%20return%20an%20array%20of%20references%20using%20INDEX%20that%20can%20be%20manipulated%20this%20way%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2785507%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2785507%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%3ERe%3A%20Can%20a%20thunk%20refer%20to%20a%20previously%20calculated%20array%2C%20just%20waiting%20to%20be%20called%2C%20or%20should%20it%20be%20thought%20of%20as%20a%20recipe%20for%20calculating%20the%20array%20should%20it%20ever%20be%20required%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20recall%20hearing%20Andy%2FSimon%2Fsomeone%20along%20the%20way%20mention%20lazy%20evaluation%3F%3F%20And%20I%20think%20I%20recall%20that%20meaning%20that%20it%20stores%20the%20LAMBDA%2C%20but%20only%20evaluates%20it%20when%20needed.%20It's%20also%20possible%20that%20my%20brain%20made%20it%20up%20sometime%20during%20my%20research%20on%20lambda%20calculus%20implementations.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2785519%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2785519%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%3EI%20went%20down%20a%20rabbit%20hole%20trying%20to%20play%20with%20MAKEARRAY%20since%20you%20mentioned%20it%20can't%20have%20a%200%20value.%20I%20learned%20that%20you%20can%20omit%20both%20of%20the%20first%20two%20parameters%2C%20and%20that%20led%20me%20even%20further%20trying%20to%20see%20what%20possibly%20could%20go%20in%20for%20those%20parameters%2C%20even%20providing%20SEQUENCE%20formulae%2C%20etc.%20I%20was%20hoping%20to%20reverse-engineer%20MAKEARRAY%20based%20off%20the%20similarity%20to%20MAP%20of%202%20parameters%2C%20but%20what%20I%20developed%20with%20MAP%20had%20sensical%20behavior%2C%20but%20I%20couldn't%20quite%20figure%20out%20what%20was%20going%20on%20with%20MAKEARRAY%3B%20exhibited%20on%20the%20MAKEARRAY%20tab%20of%20attached.%20I'm%20not%20sure%20I%20follow%20how%20to%20make%20it%20work%20with%20the%200%20parameter.%20If%20the%20thunks%20aren't%20your%20cup%20of%20tea%2C%20check%20out%20the%20array_storage%20tab%20where%20I've%20put%20together%20a%20storage%20scheme%20to%20get%20dimensions%20of%20arrays%20and%20flatten%20them%2C%20and%20can%20revive%20the%20array%20from%20the%20storage%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%20I%20was%20able%20to%20use%20the%20union%20(%2C)%20operator%2C%20but%20could%20only%20get%20it%20working%20with%20thunks%2C%20see%20the%20union%20tab.%20I've%20not%20messed%20with%20union%2Fintersection%20much%2C%20so%20not%20fully%20following%20your%20breadcrumbs%2C%20and%20my%20google%20searches%20didn't%20help%20much.%20Any%20references%20you%20can%20point%20me%20to%2C%20or%20suggest%20a%20specific%20INDEX%20construction%3F%20I%20wasn't%20sure%20what%20to%20do%20with%20OFFSET%20either.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2785708%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2785708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20thought%20was%20that%20one%20might%20be%20able%20to%20select%20specific%20sub%20areas%20with%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX((B6%3AD7%2CC7%3AE8%2CD7%3AF8)%2C%2C%2Ci)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20apply%20MAP%20with%20i%3D%7B1%2C2%2C3%7D%20maybe%20within%20a%20name%3F%20Analogously%20one%20can%20define%20a%20name%20'subarrays'%20as%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DEVALUATE(%7B%22B6%3AD7%22%2C%22C7%3AE8%22%2C%22D7%3AF8%22%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20use%20SUBTOTAL(9%2Csubarrays)%20as%20in%20the%20previous%20OFFSET%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Python%20instead%20of%20applying%20map%20to%202D%20arrays%20one%20can%20use%20array%20broadcasting%20in%203D%2C%20e.g.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Earange(3)%2Barange(2)%5B%3A%2CNone%5D%2Barange(3)%5B%3A%2CNone%2CNone%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ereturns%3A%3C%2FP%3E%3CP%3E%5B%5B%5B0%2C%201%2C%202%5D%2C%3CBR%20%2F%3E%5B1%2C%202%2C%203%5D%5D%2C%3C%2FP%3E%3CP%3E%5B%5B1%2C%202%2C%203%5D%2C%3CBR%20%2F%3E%5B2%2C%203%2C%204%5D%5D%2C%3C%2FP%3E%3CP%3E%5B%5B2%2C%203%2C%204%5D%2C%3CBR%20%2F%3E%5B3%2C%204%2C%205%5D%5D%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2786249%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2786249%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%20Ahh%2C%20thanks%20for%20the%20clarification%2C%20I%20had%20never%20noticed%20the%20areas%20param%20in%20INDEX%3B%20I've%20confirmed%20both%20constructions%2C%20MAP%20across%20an%20area%20index%20and%20MAP%20across%20the%20EVALUATEd%20array%20of%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20couldn't%20think%20of%20a%20real-world%20specific%20use-case%20for%20the%20REDUCE%2Funion%20construction%2C%20but%20set%20up%20a%20table%20and%20unioned%20all%20rows%20and%20all%20columns%20separately%2C%20then%20intersected%20them.%20Was%20surprised%20to%20see%20that%20its%20non-commutative%20in%20terms%20of%20order%2C%20but%20also%20makes%20sense%20in%20retrospect.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-09-26%20095124.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312965iB56D39F276D10E6A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-09-26%20095124.png%22%20alt%3D%22Screenshot%202021-09-26%20095124.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2786251%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2786251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3ESorry%2C%20the%20prior%20post%20ate%20my%20image%20for%20the%20MAP%20constructions.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-09-26%20100052.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312966i093567C3AFF8A3DA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-09-26%20100052.png%22%20alt%3D%22Screenshot%202021-09-26%20100052.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2798723%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20%3CEM%3Eunions%20of%20ranges%3C%2FEM%3E%20have%20limited%20application%3B%20%3CEM%3Earrays%20of%20ranges%3C%2FEM%3E%20on%20the%20other%20hand%20can%20be%20more%20useful%20as%20more%20functions%20allow%20them%2C%20eg%20attachment%20uses%20formula%20below%20for%20referencing%204D%20data%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(INDEX(SubArrays%2Ci%2Cj)%2Ck%2Cl)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20guess%20the%20equivalent%20for%20thunks%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(INDEX(Thunks%2Ci%2Cj)()%2Ck%2Cl)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20included%20is%20a%20sample%20implementation%20of%20Peter's%20idea%20from%20earlier%20in%20the%20thread%20of%20adding%20names%20based%20on%20a%20LET%20function.%20I%20think%20this%20is%20quite%20an%20intuitive%20way%20of%20managing%20names%20in%20general%20not%20just%20LAMBDAs%2C%20I%20might%20see%20about%20distributing%20the%20code%20as%20an%20addin%20so%20any%20workbook%20code%20can%20be%20removed.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lori_m_1-1632999113453.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313923iD6D0615E075D1D83%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lori_m_1-1632999113453.png%22%20alt%3D%22lori_m_1-1632999113453.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2798736%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EToo%20easy%20to%20forget%20attachments!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2800261%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2800261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3EI%20forgot%20briefly%20that%20this%20was%20an%20xlsm%20and%20wondered%20what%20voodoo%20was%20happening!%20This%20is%20pretty%20slick!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2800669%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2800669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933333%22%20target%3D%22_blank%22%3E%40tboulden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20took%20a%20bit%20of%20time%20to%20figure%20out%20the%20formula%20parsing%2C%20it's%20not%20100%25%20foolproof%20yet%2C%20but%20as%20a%20proof-of-concept%20it%20works%20better%20than%20expected.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20the%20GLOBAL%2FLOCAL%20functions%20that%20were%20suggested%20earlier%20have%20potential%20to%20further%20improve%20the%20UI%20by%20adding%20the%20validation%20automatically%20-%20which%20looks%20possible%20using%20Evaluate%20-%20I'lll%20have%20to%20wait%20for%20LAMBDA%20for%20that%20though.%20Anyway%20full%20credit%20to%20%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%26nbsp%3Bfor%20speccing%20it%20out!%3C!--%20%2Fdata%2Fuser%2F0%2Fcom.samsung.android.app.notes%2Ffiles%2Fclipdata%2Fclipdata_bodytext_210930_202616_828.sdocx%20--%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2800692%22%20slang%3D%22en-US%22%3ERe%3A%20A%20statement%20of%20truth%20or%20a%20wasted%20opportunity.%20Lambda%20helper%20functions%20reluctant%20to%20return%202D%20arr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2800692%22%20slang%3D%22en-US%22%3EVery%20interesting!%20I%20don't%20know%20about%20VBA%20Evaluate%20limitations%2C%20but%20I%20know%20I%20ran%20into%20the%20256%20char%20limit%20when%20using%20EVALUATE%20via%20Name%20Manager%3B%20is%20that%20char%20limit%20a%20concern%3F%3C%2FLINGO-BODY%3E
Trusted Contributor

I remember @Joe McDaid stating that 2D arrays are the only data types held in Excel; rows columns and scalars are merely special cases with the last being a 1x1 array.

image.png

So why is it such a problem bringing related rows together the form a 2D array?  There are a number of tricks and workarounds, but should that be necessary?  By way of example I show an old data file with the individual results from a cross-country championships.

image.png

(first 20 of 300)

The exercise is to rank the teams by adding the positions of the first 4 of their 8 runners to get a point count (lowest wins).  What I require is an output with the teams in result order showing the names of the 4 scoring members of the team.

image.png

The formula looks to be a monster at first sight

 

 

= LET(
  \0, "Extract source data",
      pos,  --LEFT(rawdata,7),
      name, (MID(rawdata,8,22)),
      team, TRIM(MID(rawdata,31,26)),
      time,--("00:"&RIGHT(rawdata,5)),
      teamList, UNIQUE(team),
  \1, "Build Lambda functions",
      SCOREλ, LAMBDA(t,
         LET(
            pts, FILTER(pos, team=t),
            SUM(INDEX(pts,{1,2,3,4}))
         )),
      MEMBERSλ, LAMBDA(t,
         LET(
            m, FILTER(name, team=t),
            CONCAT(m)
         )),
  \2, "Identify scoring members of teams in order",
      teamScore, MAP(teamList, SCOREλ),
      teamOrder, SORTBY(teamList, teamScore),
      scoring, MAP(teamOrder, MEMBERSλ),
      MID(scoring,{1,23,45,67},22))

 

 

but the section following name '\0' and the text comment is simply extracting individual fields from the source data and could easily be separated off within a helper range.  Similarly the lines following '\1' defines named Lambda functions to calculate the team score given the name of a single team and to generate the array of scoring competitors.

The meat of the calculation is at the end: the teams are scored and ranked by score.  The penultimate line assemble the list of scoring members for each team in order.  My complaint is that each team cannot be treated as an array without triggering an error message.  Instead I have had to concatenate the names to product a column of results.  Only then can I split the column back to give the array I need.   

There are other approaches such as that shown by @tboulden in 

Array of arrays using Lambda helper functions - Microsoft Tech Community

which used REDUCE and a sequence of VSTACK operations.

 

Does the restriction on arrays of arrays serve any purpose given that the spreadsheet is all about the manipulation of 2D arrays?  What do you think? 

@Sergei Baklan 

@lori_m 

@Chris Gross 

69 Replies

@tboulden 

GLOBAL is an idea for a native Excel formula (along with a potential partner in crime, LOCAL, which would upload a name definition to the Sheet names collection).  As yet, it has not even passed the first hurdle towards being a feature request.  Does the idea even make sense; would it help close the divide between the experience of formula development on the worksheet and that of Name Manager?

 

I did think about a demonstrator and it would most likely draw upon the functionality of the Macro you referred to from my former work.  That required the user to lay out the name, the comment and a working formula implementing an anonymous Lambda function in a predefined pattern on the grid and manually run a macro.

 

To implement the GLOBAL function would either require it to be a Lambda function or a UDF.  The catch is that the first parameter defining the name would produce a #NAME! error first time round, that would prevent a UDF from working, though I suppose I could required it to be provided in the form of a string.

 

The next challenge is that UDFs are not allowed to change defined names, so the real action would need to be triggered by a worksheet change event.   Another idea for a demonstrator implementation was to allow GLOBAL itself to be a named Lambda function which would reduce to a LET function.  I have in the past done things like this to allow a function √(2) to be synonymous with SQRT(2) - pointless but kind of fun.

 

In the present case, it was getting late and it was too complicated, so there is no concept demonstrator.  Another option might be to suggest it to @Charles Williams to see whether he can be tempted to implement the idea as a C addin.  Though I think GLOBAL might be a good idea, I am not at a point where I could even mount an argument that this is the killer function that would integrate the user experience for spreadsheet development.  At present, I simply welcome discussion which might add to my education!

@Peter Bartholomew 

I believe one could create a VBA prototype e.g. GLOBAL_ by using an indirect call such as Evaluate("AddName()") where AddName() contains the Names.Add command.

 

This has potential to alter range dependencies during calculation though, my preference would be for LAMBDAs to be called from cells as well as names - if there were an option.

@lori_mI had tried experimenting with SET.NAME and DEFINE.NAME early on when I was playing with LAMBDA(x,EVALUATE(x)) for helping debug my constructions, but couldn't get it to work. I was able to get a handful of XLMs working via Name Manager, but only ones that returned info, nothing that altered the workbook. In my XLM reference I don't see a NAMES.ADD, can you reference the syntax??

@tboulden 

In the above Evaluate and Names.Add refer to VBA functions - presumably equivalent to the legacy EVALUATE and DEFINE.NAME macro functions though XLM has more resrictions within udfs.

@Peter Bartholomew @lori_m  Decided to mock-up my "array of LAMBDAs" suggestion since EVALUATE was revived in my memory. Perhaps I can configure this idea to work with MAKEARRAY similar to the proto-CORKSCREW previously shown, will give that a try later.

 

Array_of_LAMBDAs.jpg

@tboulden 

Not quite as devious as your thinking but I got as far as defining a GLOBALλ to be the Lambda function

= LAMBDA(name,λfn,λfn)

that takes a name and an anonymous Lambda function and returns the function.  So

= GLOBALλ(√, LAMBDA(x,SQRT(x)))(25)

would be a valid formula, despite the fact that the math symbol '√' will evaluate to a #NAME! or #CALC! error.  The next step is to read the formula using the VBA change event.  The formula would be parsed and the global name set up (as I do now) but reading from the formula text rather than a pattern of cells.

 

@Peter Bartholomew 

It's an interesting idea. Given the formula

 

=GLOBAL_(√, LAMBDA(x,SQRT(x)))

 

The following code could be inserted into a new VBA module:

 

Dim namedef(1) As String

Function GLOBAL_(ParamArray args()) As Boolean

    Application.Volatile False
    
    Formula = Trim(Mid(Application.ThisCell.FormulaLocal, 2))
    Namestr = Mid(Formula, InStr(Formula, ",") + 1)
    
    namedef(1) = "=" & Left(Namestr, InStrRev(Namestr, ")") - 1)
    namedef(0) = Mid(Split(Formula, ",")(0), 9)
    
    Evaluate "addname()"
    GLOBAL_ = True

End Function

Function addname()
    Names.Add namedef(0), namedef(1)
End Function

 

This appears to do the job on my setup (tested without access to LAMBDA). Clearly there is room for improvement in the string parsing to handle more general cases.

 

The reservation I would have with worksheet functions like GLOBAL is introducing the ability to alter workbook state which is not strictly consistent with the functional programming paradigm. Possible side effects may include losing undo and changing range dependencies within the workbook during a calculation cycle.

 

A button or hyperlink upload tool would be a safer approach at the expense of additional user interaction.

P.S. The attachment contains the following modification to the GLOBALλ definition:

= LAMBDA(name,λfn,IF(GLOBAL_(),λfn))

This might allow Peter's original formulation to work:

=GLOBALλ(√, LAMBDA(x,SQRT(x)))(25)

 

@tboulden 

That's cool that works. In a recent blog post, it sounded like XLM is going to be turned off by default for security reasons. I wonder if one can use CHOOSE({1;2;3},...) with multiple LAMBDAs as well?

 

@Peter Bartholomew 

I played around some more and discovered one can also add names via data validation! This removes the need for creating functions with side effects and also works with undo. I've attached another example which I think could be workable.

@Peter Bartholomew @lori_m  Success! I was able to avoid EVALUATE by using CHOOSE to create my array of LAMBDAs! Then by creating a COMPOSE lambda, I'm able to reduce an array of LAMBDA using COMPOSE. I think Peter will be able to run with this more quickly than I will, but I think its very promising!Screenshot 2021-09-16 173040.png

I think lightning struck us at the same time regarding CHOOSE from looking at the timestamps. Just had to put a workbook together!

@Peter Bartholomew  I've applied this REDUCE/COMPOSE construction to a modified version of your original problem. It's not as readable as the LET construction, but I think it will have applications for manipulations that frequently happen together.

Screenshot 2021-09-17 064911.png

 

One thing that was on my wishlist that this re-emphasizes: if some legacy functions were converted to be LAMBDAs, we could have =REDUCE(UNIQUE,...)(team) instead of =REDUCE(LAMBDA(x,UNIQUE(x)),...)(team).

@tboulden 

It is one thing to know that Lambda functions are first class citizens of the calculation and can be passed as arguments of other functions, it is another to achieve anything useful with the functionality.  Congratulations.  Presumably it is the MAKEARRAY that allows the formula to return the 2D array of results but following the formula is far from straightforward.  

 

In connection with your other post, it does sometime seem odd having to create a Lambda function that does no more than the built-in function it implements [for me it might be SUMλ which is simply a restricted version of SUM].  It has yet to cause me grief though and, at present, I find the presence of the λ a useful reminder.

 

Meanwhile, I have been focussing on simpler problems on this forum.  For example, to unpivot a 2D array

"UNPIVOTλ"
= LAMBDA(grid,
    LET(
      fullList, REDUCE( , grid, APPENDλ),
      SORT(UNIQUE(fullList))
    ))

where APPENDλ is defined by

"APPENDλ"
= LAMBDA(list,value,
    IF(value="", list,
    LET(
      n, COUNTA(list),
      k, SEQUENCE(n+1),
      IF(k<=n, list, value))
  ))

I have actually used REDUCE to run over a 2D array in row-major order to deliver value!

This is a little less convoluted in terms of application, I think. Using the appropriate functions with SCAN, I get the minimal set of values that encapsulate the changes, then break those changes out into their own columns in the final step.

 

=LET(
    bal,100000,
    nper,120,
    rate,5%/12,
    pmt_,PMT(rate,nper,-bal),
    array_1,
        MAKEARRAY(nper,3,LAMBDA(i,j,j)   ),
    fxns,
        CHOOSE(array_1,
            LAMBDA(x,x),
            LAMBDA(x,x*(1+rate)),
            LAMBDA(x,x-pmt_)
        ),
    amort,SCAN(bal,fxns,LAMBDA(acc,fn,fn(acc))),
    CHOOSE(SEQUENCE(,4),INDEX(amort,,1),INDEX(amort,,2)-INDEX(amort,,1),INDEX(amort,,1)-INDEX(amort,,3),INDEX(amort,,3))
)

 

 

@tboulden 

Looks impressive! I'll need to see if there's a Python equivalent formulation to get my head around it. Regarding the last line:

=CHOOSE(SEQUENCE(,4),
     INDEX(amort,,1),
     INDEX(amort,,2)-INDEX(amort,,1),
     INDEX(amort,,1)-INDEX(amort,,3),
     INDEX(amort,,3))

This looks fairly readable when placed on separate lines but might sometimes be worth creating a LAMBDA function inline in such cases so as to replace INDEX(amort,,i) as col(i), say.

 

Another option, given people who use higher order functions would generally be familiar with matrices, might be:

=MMULT(amort,TRANSPOSE({1,0,0;-1,1,0;1,0,-1;0,0,1}))

 

@tboulden 

That is quite an achievement; an entire amortisation schedule in a cell.  Rather like the numerical analogue of the ship in a bottle!

 

image.png

It might take me a while to assimilate the implications of this and related initiatives.

@tboulden 

I got some advice concerning the original 'array of arrays' problem.  Essentially the advice was 'rather than trying to create arrays of arrays, reduce the array to 'thunks', which can be handled as scalars and evaluated at the end using MAKEARRAY.  In the event, I didn't even need to do that because I was already working with Lambda functions (team name as the parameter) which evaluated correctly.

image.png

= LET(
    teamList, UNIQUE(team),
    teamScore, MAP(teamList, SCOREλ),
    teamOrder, SORTBY(teamList, teamScore),
    result, MAKEARRAY(COUNTA(teamList), 4,
      LAMBDA(t,p,INDEX(MEMBERSλ(INDEX(teamOrder,t)), p))
    ),
  IFERROR(result, ""))
That's a nice clean construction that encapsulates our journey from your original post!!

@Peter BartholomewRevisited your particle dynamics and reworked so that the values are passed in LAMBDA function rather than as COMPLEX, taking advantage of what we've learned about arrays of LAMBDAs.

 

=LET(
    pairs,
        SCAN(LAMBDA(x,CHOOSE(x,0,2)), F#,
            LAMBDA(coord,F₀,
                LET(
                    x₀,coord(1),
                    v₀, coord(2),
                    x₁, x₀ + Δt*v₀,
                    v₁, v₀ + Δt*F₀/Mass - Δt*Coef*v₀-κ*x₀,
                    LAMBDA(x,CHOOSE(x,x₁,v₁))
                )
            )
        ),
    x,MAP(pairs,F#,LAMBDA(fn,ignored,fn(1))),
    v,MAP(pairs,F#,LAMBDA(fn,ignored,fn(2))),
    CHOOSE({1,2},x,v)
)