User Profile
tboulden
Iron Contributor
Joined 4 years ago
User Widgets
Recent Discussions
LAMBDA: Recursive Lists and Understanding Recursion Limits of LAMBDA
I think I've found the most intuitive way (for me, at least) to clearly demonstrate the current recursion limits in Excel for LAMBDA functions: recursive lists. Obligatory link for intro to LAMBDA recursion which has this gem toward the end: Furthermore, do note that the current operand stack limit in Excel is 1,024. This should be borne in mind together with calculation times, as the current recursion limit is set as 1,024 divided by (number of lambda parameters + 1). In attached spreadsheet I go about demonstrating this relationship by building a recursive list that takes two main parameters: passing the LAMBDA to itself as "ME", and the current list. The recursive LAMBDA iteratively pushes numbers to the head of the list and we inspect the completed list. To demonstrate the effect of additional parameters, I add additional placeholders that contribute nothing to building the list and only take up space in memory. As we increase parameter count, the length of our list converges on the theoretical limit of 1024/(parameter count + 1), but for 2 and 3 parameters the is a higher difference, and I'm not sure what to attribute this to. Any folks with a comp-sci background that can enlighten? Thanks in advance!LAMBDA: PrettyPrint
I've built some LAMBDAs to try to pretty-print Excel formulas; this relies on the formula being valid, so we leverage the native Excel parser indirectly. I have a base LAMBDA that creates a "naive" pretty-print: new lines for parens, curly brackets, and commas. Parameters are a cell reference or formula text and an indent spacing. It's naive because I don't attempt to determine if a bracket is in a text string wrapped in double-quotes (and probably for other reasons). I have a MakePrettier LAMBDA that works on the output of PrettyPrint.Naive; it takes a left bracket, finds all substrings that are balanced with opening and closing brackets, cleans them of whitespace, and checks to see if the cleaned width is less than a user provided parameter, maxWidth. If so, we use another LAMBDA ReplaceStrings to replace them, then return the new formula string. We execute for both a curly braces, then on that output for parentheses. The expected outcome seems to be reliably yielded using this methodology. However, when I make a single PrettyPrint LAMBDA, composing PrettyPrint.Naive and MakePrettier, my output is as if we had only execute MakePrettier for the first left bracket (in this case curly brackets). My question is why the different outcomes? Hypotheses: * scope confusion on variable names? Probably not. * Resource constraints? Probably. Excel strains when recalculating these formulas, possibly because of the size of my input-formula (an output from my UnpackLet experiment) which clocks in at over 1100 characters long. I played with a few shorter formulas and those matched using both implementations (try it with some of the LAMBDAs in the Name Manager, or you favorite long formula). The thing that I find surprising is: if I'm hitting up on the resource limits LAMBDA is allowed, why return a value at all? What have I tried to make it better? I've used both a recursive version of ReplaceStrings (included) and a non-recursive version. Doesn't affect the outcome. Thoughts hive-mind? PeterBartholomew1lori_m Y'all might find this amusing. Chris_GrossCome see what this lunatic is doing with your LAMBDAs.Legacy EVALUATE in LAMBDA: Build/Test LAMBDAs from Sheet
In working through my UnpackLet LAMBDA, I thought perhaps there might be an easier way to test a LAMBDA while building it, and I believe using the legacy EVALUATE() function that is available via Name Manager (see here: https://exceloffthegrid.com/turn-string-formula-with-evalute/) is a way to do that. Note, you'll have to save any spreadsheet with EVALUATE() in a Named Range as .xlsm. Add this to the Name Manager and call it something appropriate, i.e. lambdaEVAL: =LAMBDA(x,EVALUATE(x)) Now you can evaluate a string as a formula. You can do more complicated formulas and spread params across multiple cells, then TEXTJOIN them. This works for LETs and LAMBDAs I don't know all the limitations of this, but a prominent one is that you can't EVALUATE a string longer that 255 chars. PeterBartholomew1I have you in mind in that this may facilitate commenting on the parts of a LAMBDA as its being built. See "LAMBDA Testing" tab in attached.Recursive Lambda: Unpacking LET function
RecentlyTwifoo posted the FIFO Inventory challenge, and PeterBartholomew1 ventured "shouldn't takelori_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. SergeiBaklan'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!!
