Legacy EVALUATE in LAMBDA: Build/Test LAMBDAs from Sheet

Iron Contributor

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.

tboulden_0-1613323960460.png

 

You can do more complicated formulas and spread params across multiple cells, then TEXTJOIN them. This works for LETs and LAMBDAs

tboulden_1-1613324205643.png

 

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.

tboulden_2-1613324367310.png

 

@Peter BartholomewI 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.

 

 

20 Replies

@tboulden 

So far my focus on the development of Lambda functions is to write VBA to upload a formula (a working formula or as text) to the refers to box of the associated name.  I had wondered whether your 'unpacked let' could to run through EVALUATE to test it but it seems size might be a problem. 

 

I played with the naming of your function. 'λ' was one choice, and gets points for being succinct, but the option that surprised me was to name it 'EVALUATE'.  On the spreadsheet, the defined name took precedence over an illegal function but within the Lambda function it calls the XLM function.  The result is that, to all intents and purposes, EVALUATE is now a valid worksheet function!  Maybe Lambda will allow a few other legacy functions to be rehabilitated.

 

I will give some thought to the documentation idea.  In the past, there were occasions when I wrote the documentation in the form of comments first, and only then filled in the gaps with code.

@tboulden 

I tried to play with some of your ideas but it may be that EVALUATE has some limitations when applied to modern dynamic arrays.

image.png

@Peter BartholomewYou're onto something; I tried unpacking your LET function using my previous UnpackLet LAMBDA and that formula works, but EVAL of that formula yields same result as your EVAL("LET..."), being the first value in the 1-d array. Will play with it some more as well; not ready to throw it out just yet, and the more limitations we're aware of will prevent unnecessary frustration. Thanks!!

@Peter BartholomewIn researching this further, I've covered territory that you and @lori_m were traversing prior to the implementation of dynamic arrays: de-referencing and coercion! Due to the primordial nature of EVALUATE, these results would previously have required CSE I think, so we have to provide some incentive at times for it to give us what we want. Please see attached! I'm glad this is a thing of the past, but it is very interesting to learn about, kind of like working on your own car.

Newer versions than 1907 Excel, calculation is broken. xlcalculationAutomatic will take over 50times longer in new versions (2012 and 2101) than early versions. Its totally screwed up.

@tboulden 

Well done. I was sure I had tried the old

N(IF(1,arr))

formula in the INDEX but clearly I got something wrong.

I decided that, if I wasn't allowed 'n' as a formula-local name, I would switch to Unicode symbols.

image.png

One perfectly legal Lambda that I amused myself with recently was

= √(x²+y²)

 

@Peter BartholomewI'm afraid I'm not picking up your comment re: the hypoteneuse LAMBDA, perhaps I'm dense?

 

I've got another bit of troubleshooting I might like your help with, if you'll indulge me. As originally stated in the thread subject, I'm trying to refine using EVALUATE to make a nice worksheet-based "environment" for testing LET/LAMBDAs during construction. I've recently tried putting together your FOREACH and XSTACK in this "environment" and run into trouble with FOREACH, particularly passing in a function as a parameter. If doing so with LET and EVAL, it works happily; for LAMBDA I'm extremely confused. I can pass in the Name Manager-based LAMBDA (Iterate, in this example) and the following construction returns "128" as expected:

 

=EVAL(LAMBDA(function,TYPE(function)))

 

However, if I try to apply function as in the following:

 

=EVAL(LAMBDA(function,function(1)))

 

I'm given #NAME?. Please see attached for more detail, and thanks in advance!

@tboulden 

I have only just seen your post so this represents fresh thoughts rather than a reply.

 

The attachment uses a problem with a 2-D array of number triples and seeks to return the maximum of each triple as an array.  The problem arose within the Chandoo forum but the thing of interest for me was the structure and role of the nested λ-functions that may be combined to provide a solution.

 

I share your interest in providing techniques that make the modern function easier to use and debug.  In this workbook, I have provided a VBA macro that allows the user to specify the λ-function as a working function on the spreadsheet.  FORMULATEXT is used to make the formula visible and the function can be named and a comment provided.  The macro uploads the name definition, by-passing the need for Name Manager.

 

I have also included a working version of the formula 

= √(x²+y²)

within the second file.  It is just 'smoke and mirrors' implemented using the λ-function, '' which is defined to be

=LAMBDA(a,SQRT(a))

I think it does look prettier than

= IMABS(COMPLEX(x,y))

which will also do the job.

image.png

@Peter BartholomewMarvelous, that is much more pleasant to look at!

 

I experimented with your Array MAX Item spreadsheet and added another LAMBDA I've been working on, see λ₃ in the name manager; your VBA code is a reasonable person's implementation of what I'm trying to do with it conceptually.

 

λ₃ = LAMBDA(name,arg₁,arg₂,arg₃,λ_EVAL(INDEX(Definitions,MATCH(name,Names,0))&"("&arg₁&","&arg₂&","&arg₃&")"))

 

I've introduced a sheet "Repository" with 2 named ranges "Names" and "Definitions", and populated them with the details for your LAMBDA Array, slightly massaged to cooperate with EVALUATE. λ₃ takes a name and 3 arguments, and does a look-up on the definition, concatenates the 3 args and the required trappings (I admit this is ugly, trying to come up with something cleaner). Once constructed, it then EVALUATEs it.

 

Use it in a cell like so =λ₃("Array",T,S,2) and it should be the same as your original construction =Array(T, S, 2). I've set this up with 1 arg = λ₁, 2 arg = λ₂, and now 3 arg = λ₃ and its worked with a little massaging.

 

However!! DataRange is a RANDARRAY introduces something I didn't suspect, but makes sense now that I've seen it. My λ₃ doesn't update as often as your Array function; I have to change something in the named ranges for the functions to be in sync. I tried doing a plain λ_EVAL in the sheet, which is worse! It only reacts to changes in the spill range.

 

Interesting!! My list of limitations is growing, though. As I said before, the VBA approach you're taking is seeming very reasonable at this point.

 

 

@tboulden 

What I was trying to do was to provide a better formula development environment than Name Manager and, at the same time, to avoid too many round trips between the worksheet and the name manager (especially problematic for recursive λ functions).  I hadn't attempted the formula walkthrough that you achieved using EVAL within a λ function. 

 

The idea is great and, where applicable, it contributes hugely to the process of testing or understanding a complex LET function.  Ultimately an extension of the concept to apply to recursive λ functions would be amazing but I will settle for one step along the way right now.

 

Do not bother about the random data, it is not important.  If you dislike seeing the result out of step with the source data, you could try

=λ_EVAL(IF(TODAY(), "LAMBDA(T,S,I,INDEX(DataRange,N(IF({1},T)),N(IF({1},I+3*(S-1)))))(T,S,2)") )

to make the function volatile.

Haha, that sort of wizardry does just the trick!! Thanks!!

I forgot to ask, but is my lambdifying "trick" an example of what I've seen you refer to as currying? I started doing a bit of research, but didn't dive in to deeply. I suspect knowing more about it will help figure out why my passing a function isn't currently working fully as expected,

@tboulden 

Sorry I have been somewhat unresponsive.  I seem to have got myself locked out of the forum when using my main machine.  Microsoft created a brand new (unwanted) ID for me when it noticed I had accessed a client company account to collaborate with them.  When I deleted the profile it went into a sulk and refuses to talk to me.  I am currently using a resurrected machine which still has my original ID.

 

As I understand it, 'Currying' is a term for the practice of passing functions to a Lambda function one at a time.  I think the function remains represented as a string until a full set of parameters are provided, when it gets evaluated.

No worries at all, I noticed the username change, but the language-style seemed consistent, so I figured it wasn't an impostor and that some circumstance had occurred to instigate the new name.

@tboulden 

Just noticed I had been tagged in this thread. Interesting - though not that surprising - the old 'Evaluate' method doesn't fully support dynamic array formulas.

 

RE: 'Currying', as Peter says, refers to passing function parameters one at a time as a sequence of evaluations, I posted an example, when I had access to LAMBDA, in a reply to this post.

 

If one splits function parameters into groups of more than one input this technique is usually referred to as 'Partial Application', eg one can rewrite INDEX(A,x,y) as Array(A)(x,y) by defining the name,

Array:=LAMBDA(A,LAMBDA(x,y,INDEX(A,x,y)))

This allows one to access arrays using index notation eg: X:=Array({1,2;3,4}) -> X(1,2) = 2

@lori_mThanks for both the link to your currying example and this partial application construction, these will give me something to play with. I've been trying to figure out why you need a dummy character to end your series of parameters. Why doesn't something like this work?

 

ADD := LAMBDA(a,LAMBDA(b,IFERROR(ADD(a+b),a)))

 

I've wrestled with it off an on all day, testing various sorts of error-handling, but always back with some variation of your original construction that needs a "superfluous" parameter to throw a proper error.

@tboulden 

Dunno, I suppose it's to do with exception handling - some other languages also need a terminator of some sort. The TYPE function was used instead of ISERROR because it always returns a single value so can handle array parameters more easily.

 

I'd be interested to know if there's a workaround for the recursion limits which are quite restrictive at the moment. If Excel doesn't optimise tail recursion something like this might work (though I struggle to get my head around it!)

@lori_m @Peter Bartholomew This got me going down a rabbit hole, and I certainly don't imply that I know what I've been doing, but I think it's helping me get my head wrapped around it (slowly).

 

Based on the links I ended up doing some googling, and this is something I missed skimming the Microsoft article the first time: the GIF shows their construction of the Z/fixed point combinator.

 

tboulden_0-1614262754602.png

 

I searched a bit more to find a derivation of the Z combinator, and came across this, which seemed reasonably "accessible" (obvious scare quotes due to subject material). So I set about trying to step through these constructions in my LET/LAMBDA testing spreadsheet; please see attached. There were a few interim steps that I couldn't figure out how to implement, but ultimately the final "fact_16" is very similar to the "my_fact" I got from the Microsoft gif. I'm going to be getting some textbooks I haven't looked at in 20 years out over the weekend to try and remember the very little I "learned" about lambda calculus to facilitate making sense of this, because its mostly been pattern matching through the exercise.

 

@tboulden 

Good info, thanks for posting. I'd recommend 'Computerphile' which has some nice videos at a basic level (like mine). After going through some of that material I concluded the modified combinator method I linked to before needs a while loop so wouldn't help with recursion limits.

 

There's also a 'Funcalc' spreadsheet project that is the basis of some of the Microsoft Research material, details of which can be found here. It does look like Excel is heading toward a fully fledged functional programming language!

@lori_m 

Perhaps recursion limit is the main concern, but not only. I hope we will have another iteration of lambdas soon - Ignite and some other events are coming, that's usual time to announce such kind of news.

 

By the way, failed with recursion version on test function Excel, TEXTTOARRAY() while waiting for the production LAMBDA() | LinkedIn  , practical limit is quite low.