Recursive Lambda: Unpacking LET function

%3CLINGO-SUB%20id%3D%22lingo-sub-2132456%22%20slang%3D%22en-US%22%3ERecursive%20Lambda%3A%20Unpacking%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2132456%22%20slang%3D%22en-US%22%3E%3CP%3ERecently%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20posted%20the%20FIFO%20Inventory%20challenge%2C%20and%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%20ventured%20%22shouldn't%20take%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%20more%20than%20a%20few%20minutes%20to%20write%20a%20recursive%20Python%20script%20to%20reduce%20any%20LET%20function%20back%20to%20its%20simplest%20form%22.%20I've%20attempted%20this%20using%20a%20recursive%20LAMBDA%20function%20and%20it%20is%20just%20naive%20replacement%20of%20a%20variable%20with%20its%20definition%2C%20please%20see%20attached.%20Summary%20on%20first%20tab%2C%20and%20deep-dive%20on%202nd%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20a%20few%20of%20the%20various%20LET%20formulas%20from%20the%20FIFO%20Inventory%20challenge%20that%20were%20submitted%20as%20my%20set%20of%20test%20cases%2C%20and%20was%20able%20to%20finally%20get%20a%20mostly-working%20version%3B%20feel%20free%20to%20play%20with%20it%2C%20share%20improvements%2C%20and%20possibly%20help%20me%20figure%20out%20my%20remaining%20issues.%20In%20experimenting%2C%20my%20interim%20versions%20of%20my%20LAMBDA%20did%20kill%20my%20Excel%20instance%20a%20few%20times%2C%20but%20I%20think%20this%20version%20is%20stable-ish%20without%20having%20hit%20it%20with%20a%20bevy%20of%20test%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20the%205%20LET%20formulas%20I%20tested%2C%20only%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%20formula%20will%20not%20yield%20to%20my%20function.%20Someone%20will%20probably%20identify%20the%20issue%20readily%2C%20I%20may%20just%20have%20been%20staring%20at%20it%20for%20too%20long%20to%20see%20it.%20%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's%20formula%20was%20the%20only%20one%20that%20didn't%20require%20adjustments%20to%20var%20names%20to%20prevent%20substring%20collisions.%20Perhaps%20others%20have%20ideas%20about%20how%20to%20avoid%20having%20to%20adjust%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20I've%20included%20a%20failed%20attempt%20on%20the%203rd%20tab%20that%20I'd%20like%20others%20to%20perform%20an%20autopsy%20on%20and%20perhaps%20help%20me%20figure%20out%20why%20it%20doesn't%20work%2C%20but%20my%20slightly%20modified%20version%20is%20successful.%20During%20my%20experimenting%2C%20I%20thought%20perhaps%20it%20was%20due%20to%20limits%20on%20parameter%20length%20being%20passed%20to%20a%20LAMBDA%2C%20but%20my%20brain%20is%20a%20bit%20fried%20after%20working%20on%20it%20for%20a%20day%20or%20two.%20Fun%20exercise!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2132456%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2133263%22%20slang%3D%22en-US%22%3ERe%3A%20Recursive%20Lambda%3A%20Unpacking%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2133263%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%2C%20that%20is%20a%20stunning%20effort!%26nbsp%3B%20Perhaps%20the%20Microsoft%20Research%20Team%20might%20like%20to%20take%20a%20look%20at%20this%20to%20see%20where%20their%20'baby'%20is%20being%20taken!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fresearch%2Fblog%2Flambda-the-ultimatae-excel-worksheet-function%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELAMBDA%3A%20The%20ultimate%20Excel%20worksheet%20function%20-%20Microsoft%20Research%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wasn't%20really%20serious%20in%20suggesting%20that%20the%20task%20%3CU%3Eshould%3C%2FU%3E%20be%20performed%2C%20simply%20making%20the%20point%20that%20it%20%3CU%3Ecould%3C%2FU%3E%20be%20performed.%26nbsp%3B%20I%20was%20simply%20expressing%20the%20idea%20that%20reducing%20a%20LET%20function%20to%20traditional%20spreadsheet%20formulae%20(that%20I%20know%20and%20loath)%20is%20a%20purely%20mechanistic%20process.%26nbsp%3B%20I%20had%20thought%20of%20using%20Lambda%20functions%20and%20even%20taken%20a%20step%20or%20two%20in%20that%20direction.%26nbsp%3B%20After%20all%2C%20if%20Lambda%20functions%20make%20Excel%20Turing-complete%20and%2C%20given%20that%20functional%20programming%20is%20well-suited%20to%20the%20task%20of%20parsing%20data%20for%20compiler%20writing%2C%20I%20thought%20it%20should%20be%20possible.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20thoughts%20that%20led%20me%20to%20suggest%20Python%20were%20that%20I%20knew%20that%20Lori%20uses%20Python%20more%20than%20Excel%20and%20the%20development%20environment%20should%20be%20more%20productive.%26nbsp%3B%20I%20did%20have%20one%20advantage%20over%20you%2C%20namely%20I%20use%20Charles%20Williams's%20FastExcel%20which%20includes%20regular%20expression%20support%2C%20so%20I%20was%20able%20to%20search%20for%20strings%20that%20were%20bounded%20by%20word%20breaks%20and%20only%20replace%20those%20with%20no%20adjacent%20word%20characters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20completely%20freaked%20out%20at%20the%20possibility%20of%20matching%20paired%20parentheses%2C%20so%20I%20guess%20I%20wasn't%20sufficiently%20committed%20to%20the%20task.%26nbsp%3B%20After%20all%2C%20I%20am%20the%20one%20that%20has%20declared%20I%20would%20sooner%20consign%20Excel%20to%20the%20trash-can%20than%20go%20back%20to%20spreadsheeting%20as%20it%20used%20to%20be!%26nbsp%3B%20To%20me%20the%20non-LET%20versions%20of%20the%20formulae%20are%20a%20mess%20and%20to%20be%20used%20only%20as%20a%20last%20resort.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20finish%2C%20I%20can%20only%20reiterate%20my%20opening%20sentence%3B%20Wow%20...%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-2133413%22%20slang%3D%22en-US%22%3ERe%3A%20Recursive%20Lambda%3A%20Unpacking%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2133413%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%0A%3CP%3EInteresting%2C%20thanks%20for%20sharing.%20Can't%20say%20I%20understood%20this%2C%20make%20bookmark%20to%20play%20with%20your%20file%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Recently@Twifoo posted the FIFO Inventory challenge, and @Peter Bartholomew ventured "shouldn't take @lori_m more than a few minutes to write a recursive Python script to reduce any LET function back to its simplest form". I've attempted this using a recursive LAMBDA function and it is just naive replacement of a variable with its definition, please see attached. Summary on first tab, and deep-dive on 2nd tab.

 

I used a few of the various LET formulas from the FIFO Inventory challenge that were submitted as my set of test cases, and was able to finally get a mostly-working version; feel free to play with it, share improvements, and possibly help me figure out my remaining issues. In experimenting, my interim versions of my LAMBDA did kill my Excel instance a few times, but I think this version is stable-ish without having hit it with a bevy of test cases.

 

Of the 5 LET formulas I tested, only @mathetes formula will not yield to my function. Someone will probably identify the issue readily, I may just have been staring at it for too long to see it. @Sergei Baklan's formula was the only one that didn't require adjustments to var names to prevent substring collisions. Perhaps others have ideas about how to avoid having to adjust??

 

Finally, I've included a failed attempt on the 3rd tab that I'd like others to perform an autopsy on and perhaps help me figure out why it doesn't work, but my slightly modified version is successful. During my experimenting, I thought perhaps it was due to limits on parameter length being passed to a LAMBDA, but my brain is a bit fried after working on it for a day or two. Fun exercise!!

4 Replies

@tboulden 

Wow, that is a stunning effort!  Perhaps the Microsoft Research Team might like to take a look at this to see where their 'baby' is being taken!

LAMBDA: The ultimate Excel worksheet function - Microsoft Research

 

I wasn't really serious in suggesting that the task should be performed, simply making the point that it could be performed.  I was simply expressing the idea that reducing a LET function to traditional spreadsheet formulae (that I know and loath) is a purely mechanistic process.  I had thought of using Lambda functions and even taken a step or two in that direction.  After all, if Lambda functions make Excel Turing-complete and, given that functional programming is well-suited to the task of parsing data for compiler writing, I thought it should be possible. 

 

The thoughts that led me to suggest Python were that I knew that Lori uses Python more than Excel and the development environment should be more productive.  I did have one advantage over you, namely I use Charles Williams's FastExcel which includes regular expression support, so I was able to search for strings that were bounded by word breaks and only replace those with no adjacent word characters.

 

I completely freaked out at the possibility of matching paired parentheses, so I guess I wasn't sufficiently committed to the task.  After all, I am the one that has declared I would sooner consign Excel to the trash-can than go back to spreadsheeting as it used to be!  To me the non-LET versions of the formulae are a mess and to be used only as a last resort.

 

To finish, I can only reiterate my opening sentence; Wow ...

 

 

@tboulden 

Interesting, thanks for sharing. Can't say I understood this, make bookmark to play with your file later.

@Peter BartholomewMuch appreciated! I did make several false starts with regard to matching opening and closing chars, but I think the saving grace is that I assumed it only should work on formula that Excel has already properly parsed and calculated; I think building a formula validator might be an interesting exercise as well.

 

I'll have to check out FastExcel, I've used regex in VBA with the appropriate reference, but having it available in the worksheet itself would be nice. If I recall correctly building regex for bracket matching is a bit complicated with a novice's understanding; I know that my balanced tests would fail if a brace was within a pair of double-quotes without a corresponding matching brace before the next formula-level brace.

 

I agree that non-LET will be a thing of the past after some time, just wish we'd had it sooner, would've made spreadsheet modeling much less aggravating at times.

And I've sorted the issue with mathetes formula not yielding to my function: my helper LAMBDA CheckCommaBeforeOpenChar throws an error because there are no curly braces in the formula. Updated the helper LAMBDA to this, though probably better to handle errors differently than pass a value that isn't correct:

 

T_KthCharInstance,LAMBDA(myStr,myChar,k,IFERROR(FIND(CHAR(1),SUBSTITUTE(myStr,myChar,CHAR(1),k)),LEN(myStr)+1))