Lambda: The Ultimate Excel Worksheet Function

Funny how the top of the article starts with &quot;custom functions without code&quot; and then immediately shows code.<p>I get that calling code by its name can make it sound scary, but this whole notion of it being &#x27;easy because it is not code&#x27; seems to be a big fat lie for comfort. Same goes for the magic no-code systems where code is replaced with &#x27;expressions&#x27; or graphical &#x27;workflows&#x27; which essentially is exactly the same thing, only shaped slightly differently.<p>This makes me wonder if it wouldn&#x27;t be much better if we could focus on making people be able to code and have more &#x27;coding capacity&#x27; instead of having less of that capacity and then reducing it even more by using some of it to create &#x27;let us pretend this is not code&#x27;-applications.

Great to see Excel adding more features!<p>The argument I&#x27;ve heard against doing this sorta thing was that they wanted to keep Excel simple enough to not alienate many non-technical users, sorta forcing it to be a simple, accessible environment for everyone.<p>It&#x27;ll be neat to see how the user-base adapts to a more powerful feature-set. I mean, it&#x27;d seem like a lot of folks will be thrilled, finally having some extra functionality without having to use macros&#x2F;VBA&#x2F;VSTO&#x2F;COM&#x2F;etc., though how might non-technical folks feel about a coworker sending them a spreadsheet with function-values?

<i>&gt; The existing Name Manager in Excel allows any formula to be given a name. If we name our function PYTHAGORAS, then a formula such as PYTHAGORAS(3,4) evaluates to 5. Once named, you call the function by name, eliminating the need to repeat entire formulas when you want to use them.</i><p>That&#x27;s the biggest issue with LET &#x2F; LAMBDA at the moment. Users are terrified of the name manager and simply do not understand what they are for or what &quot;scope&quot; means. On top of that, copying content from one workbook to another leads to names being copied over as well, which is how I often end up with ancient names such as FXRATE1997

This is <i>almost</i> what I always wanted, but it feels a bit bolted on to be honest. I don&#x27;t like that you have to put a complex nested function in one cell, rather than using multiple cells with temporary results. While this allows you to concatenate Excel functions, I think it doesn&#x27;t allow you to write a function in &quot;idiomatic&quot; Excel. If I had to implement functions in Excel, I would use one of two strategies:<p>- You have a special area or special kind of sheet, where some cells are inputs, one is output, and all others are used for temporary calculation<p>or:<p>- You define your calculation as usual, in B5: = 10*A5 - then in C5:<p><pre><code> =MYLAMBDA(B5; A5)(3) </code></pre> Meaning: Take the formula in B5, treat A5 as an argument, and return a function. Then call this function with the argument 3.<p>The benefit of this? You can have an area in your sheet where the user can enter formulas and multi-cell-calculations, not just numbers, and they are applied elsewhere.

There is, perhaps surprisingly (or not), already a relevant XKCD mentioning this feature while poking fun at the computational abominations that were already possible in Excel:<p><a href="https:&#x2F;&#x2F;xkcd.com&#x2F;2453&#x2F;" rel="nofollow">https:&#x2F;&#x2F;xkcd.com&#x2F;2453&#x2F;</a><p>On that note, TFA claims that the introduction of LAMBDA finally makes Excel Turing complete, unlike the kind of Turing machine simulators the stick figure is referring to in the XKCD comic...<p>&gt; (In contrast, Felienne Hermans’s lovely blog post about writing a Turing machine in Excel doesn’t, strictly speaking, establish Turing completeness because it uses successive rows for successive states, so the number of steps is limited by the number of rows.)

This is cool. I work on a lot of spreadsheets at work and am religiously against dropping into VBA for anything. It would often be easier, but it then destroys the portability of the spreadsheet (now it has to be .xlsm, people get scary warnings, etc.).<p>Also, does this:<p>&gt; <i>With LAMBDA, Excel has become Turing-complete.</i><p>sound like a threat to anyone? :)

The blog post&#x27;s title is an homage to a series of papers from the 70s, which formulated the Scheme programming language and the beginnings of what we now know as &quot;functional programming&quot;.<p><a href="https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;History_of_the_Scheme_programming_language#The_Lambda_Papers" rel="nofollow">https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;History_of_the_Scheme_programm...</a>

Naive but serious question: couldn&#x27;t Excel create a special &#x2F; hidden sheet for lambda functions, thereby allowing them to be easily written on multiple lines, with some kind of standard formatting, then calling a lambda from a cell would be of the form:<p>=LAMBDA(global_function_name, [cell_input_1, cell_input_2, ...])<p>Wouldn&#x27;t this be a cleaner design? Trying to deal with cells whose formulas are <i>way too long</i> to be put in a single cell is Excel&#x27;s Achilles Heel (and a footgun that you are nearly guaranteed to enounter sooner rather than later). This LAMBDA proposal as written seems to exacerbate that problem, not improve it.

One small recent thread:<p><i>Lambda: The ultimate Excel worksheet function</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=25923628" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=25923628</a> - Jan 2021 (4 comments)

Am I the only one who read the title and was immediately reminded of <a href="http:&#x2F;&#x2F;lambda-the-ultimate.org&#x2F;" rel="nofollow">http:&#x2F;&#x2F;lambda-the-ultimate.org&#x2F;</a>?<p>(Is the title a deliberate call to that site, or something even older?)

Writing a lambda like that would require documentation. I wish Excel had a mode to look kinda like Jupyter except reactive (or observablehq except a desktop app), so I could easily add documentation for any cell and fold&#x2F;unfold as I wish.

I&#x27;ve used Excel to automate things like splitting spreadsheets into separate files, and I write functional code with lambdas as a programmer. The way they phrased this makes it sound like their goal is to allow you to do hideously complex things, not to let you do more powerful things simply. I started out excited and ended up scared.

This is great and all, but it struck me that only Microsoft could come up with phrases like &quot;LAMBDA is available to members of the Insiders: Beta program&quot; and &quot;LAMBDA complements the March 2020 release of LET&quot;. It sounds like something someone might write in a parody press release on comp.lang.scheme 30 years ago.

So they are building LISP inside Excel.... It is now a corollary of Greenspun&#x27;s Tenth Rule of Programming...

This is great, there were a few network functions I used a lot (validate IP format, find network address, find broadcast address, check if address is in subnet, etc) and would always just stick a &quot;scratch&quot; sheet in a workbook duplicated for each time I needed to perform an operation and nobody was ever going to be able to decode after I hit &quot;send&quot; on the email. The additions they&#x27;ve added make this so simple and ubiquitously available I wouldn&#x27;t even call it ugly for that use case anymore.

Now we have a legitimate use case for the Y combinator.<p>What&#x27;s next? A full implementation of scheme? Common lisp?

If you want to stay on top of what is going on in Excel, the team&#x27;s Excel Blog is worth checking out now and then.<p><a href="https:&#x2F;&#x2F;techcommunity.microsoft.com&#x2F;t5&#x2F;excel-blog&#x2F;bg-p&#x2F;ExcelBlog" rel="nofollow">https:&#x2F;&#x2F;techcommunity.microsoft.com&#x2F;t5&#x2F;excel-blog&#x2F;bg-p&#x2F;Excel...</a>

I really like what these guys are doing <a href="https:&#x2F;&#x2F;numbrz.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;numbrz.com&#x2F;</a> You can build data stores that can be published to other numbrz users. They can augment your data and republish. As the source data changes, everyone&#x27;s stores are updated.

I always thought the Excel computational model was elegant and useful as it didn&#x27;t allow for non-termination.<p>Now they lose termination for the use case where someone knows how to program but can&#x27;t or won&#x27;t program in some &quot;normal&quot; programming language.

Oh no - can&#x27;t they just integrate M instead? It&#x27;s a great (skeleton of a) language with a similar basis but far nicer to write.

I wonder how a programming language “extension” to Excel works together with the fact that the excel language is localized. Will the lambda function names be similarly localized?

The PhD work of Sruti Ragavan appears to lie behind part of this development. She hasn&#x27;t defended her thesis yet, but she&#x27;s been recruited by the MSR Cambridge team behind this work, where she interned in 2018, IIUC just before she began her PhD work.<p><a href="https:&#x2F;&#x2F;sruti-s-ragavan.com&#x2F;research&#x2F;" rel="nofollow">https:&#x2F;&#x2F;sruti-s-ragavan.com&#x2F;research&#x2F;</a>

I don&#x27;t understand the point of this function, when Excel already has Power Query. It doesn&#x27;t seem like anyone who is literate in functional programming would want to use this, and anyone who isn&#x27;t up to it wouldn&#x27;t either.<p>One of the most annoying things about Excel is it has so many parts apparently designed by people or groups that didn&#x27;t talk to each other and didn&#x27;t have a grasp of all the rest of it, let alone the world of the (various groups of) users.<p>Who ordered <i>another</i> Turing-complete system in Excel? One that is, like all the others, a pain and a half to debug or analyze? Has anyone figured out how to turn this into a security vulnerability yet?<p>Saying &quot;yay people are making videos&quot; only makes me think of all the horrific tutorials on Power Automate. And this: <a href="https:&#x2F;&#x2F;xkcd.com&#x2F;763&#x2F;" rel="nofollow">https:&#x2F;&#x2F;xkcd.com&#x2F;763&#x2F;</a>

This has to be an April Fools, it&#x27;s just posted early and we&#x27;re talking about it late. It&#x27;s not real right? right?

Coming up next: Excel Lambda back-end experimental support on LLVM. (Just a joke) Excel is a very useful tool for many non-programmers employees. With LAMBDA they can have a more expressive system without the need to touch VBA, JS, PQ M language, nor jump to Python. Are we able to generate side-effects with it? Like poking the value of a cell?

8 days ago: <a href="https:&#x2F;&#x2F;www.reddit.com&#x2F;r&#x2F;lisp&#x2F;comments&#x2F;mr1fvt&#x2F;ms_excel_is_unpopular_due_to_lots_of_irritating&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.reddit.com&#x2F;r&#x2F;lisp&#x2F;comments&#x2F;mr1fvt&#x2F;ms_excel_is_un...</a>

Recent and related:<p><i>Lambda: The ultimate Excel worksheet function</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=25923628" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=25923628</a> - Jan 2021 (4 comments)

The &quot;Lambda&quot; paper Steele and Sussman never wrote!<p><a href="http:&#x2F;&#x2F;lambda-the-ultimate.org&#x2F;papers" rel="nofollow">http:&#x2F;&#x2F;lambda-the-ultimate.org&#x2F;papers</a><p>Including:<p>&quot;Lambda the Ultimate Imperative&quot;<p>&quot;Lambda the Ultimate Declarative&quot;

Just bringing the Visual Studio code control into Excel is a plus. This will remove several steps when creating and deploying addin.

Wow, that recursive example is a nightmare. I can&#x27;t imagine being the person that&#x27;s asked to debug what&#x27;s going wrong here a few years down the line.

<i>The Calc Intelligence project at Microsoft Research Cambridge has a long-standing partnership with the Excel team to transform spreadsheet formulas into a full-fledged programming language.</i><p>Your scientists were so preoccupied with whether or not they could, they didn’t stop to think if they should.