LAMBDA: The ultimate Excel worksheet function
April 13, 2021 3:45 AM   Subscribe

A longer blog post from Microsoft Research announcing the availability of =LAMBDA in Excel.

Written by Andy Gordon and Simon Peyton Jones they clearly show where they are coming from:
Our partnership with the product team exemplifies a symbiosis between research and practice. For example, like many long-lived programming systems, the only really precise documentation of Excel semantics is its source code. So to understand what Excel really does, we developed a written semantics for it and a reference implementation of formula evaluation in TypeScript, Calc.ts.
Turning Excel's formula language into a Turing-complete programming language is just the first step. It appears that their research group within Microsoft, Calc Intelligence is up to even more innovation:
More broadly, we’ve built on our partnership to develop a research program around the theme of end-user programming, specifically involving a research crossover between programming languages, human-computer interaction, and machine learning, as demonstrated by this selection of papers:
posted by kmt (100 comments total) 39 users marked this as a favorite
 
oh nooooooo

I do wonder if this will make sprawling, unreadably complex Excel spreadsheets less cursed, but let's be honest here: they're about to become even more cursed.
posted by Merus at 5:14 AM on April 13, 2021 [48 favorites]


Does this mean that Excel viruses don't need to hide in macro code any more?
posted by scruss at 5:20 AM on April 13, 2021 [15 favorites]


IIRC Excel has an existing alert that tells users if a spreadsheet has circular references. This new lambda ability introduces the potential for a problem that programmers of other languages have experienced: infinite loops. I think to maintain user- and business-friendliness Excel should come with a built-in function HALT(lambda, range) which will be TRUE if the given lambda function terminates on the given range, and FALSE otherwise. This seems conceptually similar to the existing check for circular references so it shouldn't be too hard to program.
posted by Joe in Australia at 5:25 AM on April 13, 2021 [60 favorites]


This is A Big Fucking Deal in many ways.

Millions of Excel Engineers will now be able to weaponize Excel into a near-sentient AI that's immediatly reduced to copy-pasting data from Excel to email for printouts because management is too stupid to do any real work.

Or our fearless Excel Engineers will create a endless Cthulhuian Horror Show as millions of =LAMBDA's devour the world with their incomprehensible black magic.

Someone please create the mandatory novelty Twitter account that documents future =LAMBDA horrors. Because you know shit is about to get crazy when this is the example that the authors chose to introduce =LAMBDA's with:
=LAMBDA( X, Y, LET( XS, X*X, YS, Y*Y, SQRT( XS+YS ) ) )

Who knew the real horror of 2021 was the malevolent =LAMBDA entity in cell A1.
posted by Foci for Analysis at 5:25 AM on April 13, 2021 [26 favorites]


Simon Peyton Jones is just fucking with us, right?
posted by rdr at 5:39 AM on April 13, 2021 [3 favorites]


but will it run doom
posted by AzraelBrown at 5:45 AM on April 13, 2021 [4 favorites]


This is uniformly terrible on all fronts. The whole thing that made spreadsheets even remotely comprehensible is that at the bottom it's just a series of algebraic equations executed in order. You could (at least in theory) understand exactly what it was doing without having to get into P=NP level kinda bullshit.

Turing complete, though? In less than a dozen trivial statements on a Turing machine you can create something that you can never truly understand.

This is 0xabad1dea
posted by seanmpuckett at 5:46 AM on April 13, 2021 [16 favorites]


will it run doom

The possibility of a C to excel compiler just became a lot easier to imagine
posted by dis_integration at 5:47 AM on April 13, 2021 [7 favorites]


I'll forget about this before I ever use it, but I can see it coming in handy.

More handy though would be an option to turn off date formatting forever.
posted by Huffy Puffy at 5:48 AM on April 13, 2021 [50 favorites]


Turing complete, though? In less than a dozen trivial statements on a Turing machine you can create something that you can never truly understand.

No need to overthink this one, it's just the next stage of Roko's Basilisk creating itself.
posted by otherchaz at 5:50 AM on April 13, 2021 [5 favorites]


If it was that simple I'd just throw a loaf of bread and run.
posted by seanmpuckett at 5:54 AM on April 13, 2021 [1 favorite]


This is 0xabad1dea

It is kind of hilarious though. They're taking this ubiquitous locked-down platform and adding one eensy-weensy change that suddenly makes it the most powerful language on most users' desktop, with goodness knows what side effects. I had to check the date when I read it; I thought it was an April Fool's.
posted by Joe in Australia at 5:57 AM on April 13, 2021 [22 favorites]


I used to work a lot with Excel - for lack of a better programming environment in our office. Don't get me started on how many times I googled for "save formula as function" over the years.

Have I mentioned that excel apparently now has
  • Custom Datatypes (with dot notation!)

  • dynamic arrays?


  • It appears that for whatever reason (perhaps SPJ played a role here) excel is getting a lot of genuinely new and useful features. But that's just my 0.02$.

    On the other hand, they really should fix the automatic date conversion.

    posted by kmt at 6:09 AM on April 13, 2021 [10 favorites]


    Oh holy crap is this a bad idea. People already do incomprehensible stuff in Excel worksheets using the IF function. The problems isn't that it might work. It's that when it doesn't work, it is an unreadable mess. You can't read it and you can't debug it because there are no debugging tools. People will use this because it takes about 8 hours of class time to learn the basics of VBA to be able to do user defined functions. However the drawbacks are huge for maintainable worksheets.

    I don't want Turing complete worksheets...that is what a programming language is for. Using VBA, which is built in, or any other programming language with a bit more work, I can create user-defined functions to do anything I want. There it is readable as text, and I can use the debugging tools to figure out when it is wrong.

    For example, this code in VBA defines a new function Pythagoras that does the theorem and can be used anywhere on the worksheet (indenting lost because HTML) that you can read and debug. It will show up along with all the built in functions in Excel:

    Public Function Pythagoras(ByVal x As Double, ByVal y As Double) As Double
    Pythagoras = Sqr(x * x + y * y)
    End Function
    posted by Xoc at 6:13 AM on April 13, 2021 [4 favorites]


    I remember describing this as a lesser known sign of the apocalypse.
    posted by NoxAeternum at 6:15 AM on April 13, 2021 [6 favorites]


    After a long day of interrogations and raking everyone over the coals just to test the upper limits of how prepared we were, an ISO auditor singled me out and tried to make small talk by asking if I liked spreadsheets. I wish I could remember exactly how he described his like of spreadsheets because it was the kind of monologue you'd expect a sadistic auditor to give just before dropping the hammer. It was something to do with how relaxing it was to study them and how fascinating it was that you make one small change here or there and a cascade of values immediately flows through the rest of the sheet. I think I was visibly sweating by the time he finished.

    I feel like these new features are for people like him.
    posted by RonButNotStupid at 6:19 AM on April 13, 2021 [22 favorites]


    For good reasons, a lot of folks are saying this is a bad idea. Spreadsheets already get too complicated and tenuous...

    Still: I feel this is a natural progression of Excel capabilities and is a net positive for users - especially advanced users - who live in spreadsheets all day.
    posted by alrightokay at 6:32 AM on April 13, 2021 [5 favorites]


    Back in the 80s, Xerox had ASP, which allowed any cell in the spreadsheet to be a complete Smalltalk program.
    posted by CheeseDigestsAll at 6:32 AM on April 13, 2021 [3 favorites]


    For good reasons, a lot of folks are saying this is a bad idea.

    THE FORBIDDEN CELL
    posted by thelonius at 6:33 AM on April 13, 2021 [4 favorites]


    This seems conceptually similar to the existing check for circular references so it shouldn't be too hard to program.

    I see what you did there.
    posted by teh_boy at 6:57 AM on April 13, 2021 [10 favorites]


    When you look at imperative code, you can see pretty clearly the transformations that are done to data, but you can't see the data itself. The logic is visible, the data is invisible. In a spreadsheet, the user interface presents the data, but the logic that transforms the data from one area to another is hidden, you can only see it if you go looking for it, and then it is almost always compressed into a single line of text. Control-flow exists, but is done by an IF function that does not visually separate the consequent branches of the formula.

    For added fun, Spreadsheet Horror Stories, via the European Spreadsheet Risks Interest Group.
    posted by rustcrumb at 7:09 AM on April 13, 2021 [8 favorites]


    Prisoner without a name, cell without a =LAMBDA.
    posted by Cardinal Fang at 7:13 AM on April 13, 2021 [2 favorites]


    The obvious solution to the code- vs. data-oriented interface issue is a notebook environment, where multiple code snippets and data artifacts they produce don’t have to exist within a single matrix of cells. Personally, I think it would be fantastic if Peyton Jones had a secret plan to slowly morph Excel into a Haskell notebook with all the same financial and plotting features.
    posted by mubba at 7:33 AM on April 13, 2021 [3 favorites]


    Let the insane people have their LAMBDA. But, cordon it off. These features must be turned on with a registered USB knife switch. Any spreadsheet using these features must begin with a large black splash screen featuring a skull and crossbones and the word TOXIC flashing in yellow.
    posted by njohnson23 at 7:36 AM on April 13, 2021 [11 favorites]


    I am so excited by this. This is seriously the coolest thing to happen to Excel in a long time.
    posted by interogative mood at 7:38 AM on April 13, 2021 [6 favorites]


    I disagree with the objection that lambda will make spreadsheets messier. People will certainly use it in crafting unholy nightmares, such abominations have been created for decades. The added ability to clearly express logic when used well is more than worth whatever abuse it sees.
    posted by skymt at 7:45 AM on April 13, 2021 [10 favorites]


    They crammed this into the Name Manager of all things. You'll get to edit your new lamda functions inside of a two inch text box where the goddamn arrow keys aren't even usable because they'll insert cell references instead of moving the cursor. You won't get smart completion, hints, or highlighting of any kind there either.
    posted by paper chromatographologist at 7:47 AM on April 13, 2021 [30 favorites]


    I got into a role this year where because of PANDEMIC reasons I was suddenly trying to learn to solve scheduling and routing and queuing and supply chain problems mostly by mainlining excel tutorials and Wikipedia and white papers late at night. I have no programming or math background and hooo boy did I learn a lot fast. One of my friends who does do that stuff said “for gods sake learn R” and that was very useful, and Python has been useful, and I felt all accomplished and grown up. But the thing is that literally no one else in my 1000 person company knows anything about R or Python or really even Excel that requires a nested function. So really, from a functional and ethical standpoint, I have to do all that stuff in excel if I’m not just sharing the results with someone, and if I want anyone else to be able to use what I make as a tool. And even then, as the links speak to, I am not an expert in making robust, user friendly tools, and no one else in my org or reasonably expected expected to be hired by my org can parse my functions, so I need to keep simplifying and documenting and generally trying to make sure that a vital tool I pour dozens of hours into is salvageable and not actively dangerous if I am not actively maintaining it. So I’m of at least three minds about LAMBDA; my sadomasochistic side loves the horrific Dr Moreau monstrosity that is advanced Excel, and I can scarcely imagine the eldritch horrors I shall create here. On the other hand, if I put one of these things into a shared workbook no one other than me will ever, ever debug, maintain, or upgrade it, and it may be actively harmful because in my absence (or false confidence!) the bugs will probably not even be checked for. But also, Excel and Google Sheets are definitely a valuable DIY space that sort of bridges the what is possible and what is free spaces, and it’s really valuable/risky for orgs like mine. In health care, social services, and I’m sure other spaces, our ability to solve analytical and projection problems saves lives, but my org and certainly most can’t afford either the serious software or the expertise needed to solve these things cleanly and well. So a lot of “how people get care and medicine and lots of other stuff” is effectively hacked together by a handful of nerds like me and built in consumer software out of Lincoln logs and and rubber bands into horrific Rube Goldberg Machines. And I lie awake at night sometime and think that if I hired a real programmer to recreate things I’ve built in excel, after they were done crying, they’d wind up with something a thousand times more stable, and useful, and elegant. But look, I can’t hire them, and as risky as it is to use this Goldberg Machine it’s still better than not doing this because we’re doing better for people now, even if we’re risking the future; the future would suck anyway. Anyway, if you’re a budding developer or an aging developer or whatever and you are looking for a place to make a difference, the very unsexy areas of hospitals and public health and home care could really really use affordable, stable, easy to use software.
    posted by skookumsaurus rex at 7:50 AM on April 13, 2021 [34 favorites]


    I mean, if I never ever have to write another function in visual basic, that's no great loss, right? I, for one, welcome our new LAMBDA overlords.
    posted by simra at 8:09 AM on April 13, 2021 [2 favorites]


    Can I just flag this research right here:
    Understanding and Inferring Units in Spreadsheets (VL/HCC 2020): We combine a formal type system with machine learning to predict the units of numbers in spreadsheets.

    as more fucking terrifying than =LAMBDA because Excel already infamously, spectacularly and irreversibly shits up anything it thinks looks like a date; get ready for more spacecraft slamming into Mars and whatnot because the 2023 Excel release decided that auto converting everything that it thought seemed a little metric into imperial units was a cool feature.
    posted by Superilla at 8:20 AM on April 13, 2021 [29 favorites]


    I love that this is targeted at users who very much are Not Programmers, but who have pieced together enough bits of programming over the years that they would benefit from something more powerful than what's available in Excel macros today, and so the press release gives us some really simple examples--oh, no, wait, it's a giant tangled morass of spaghetti code solving a common problem for programmers that no one else would possibly ever care about, and the demo is an ANIMATED GIF of it in action.
    posted by Mayor West at 8:23 AM on April 13, 2021 [7 favorites]


    I'm going to post "Excel is the only useful Lisp" on hacker news until they crucify me
    posted by theodolite at 8:29 AM on April 13, 2021 [30 favorites]


    a) You can't read it and you can't debug it because there are no debugging tools.

    b) They're taking this ubiquitous locked-down platform and adding one eensy-weensy change that suddenly makes it the most powerful language on most users' desktop, with goodness knows what side effects.

    i wonder how this is going to go in DOD environments. existing instructions dictate: no dev tool on desktops, ever.

    c) I don't want Turing complete worksheets...that is what a programming language is for.

    srsly. it boggles my mind that any profit-driven entity would put business-critical tools in a format that: can't be diffed if source code control, can be modified by any end user, doesn't have step-through debugging...i guess the argument is that it will work for most of the people most of the time.

    except like when they fucked up the entire 2008 recovery by using a model with shit formulas in excel. you know, stuff that impacts millions of people for the rest of their lives.
    posted by j_curiouser at 8:35 AM on April 13, 2021 [6 favorites]


    A lifetime ago when the Sarbanes–Oxley Act (SOX) was released and public companies had to suddenly sign-off on their internal controls, I ws roped into doing a lot of SOX controls testing (*shudder*). You can almost never overstate how much of corporate America runs on Excel (or is glued together by it). Trying to figure out 'controls' over spreadsheets (to ensure the confidentiality, integrity, availability trifecta) was one of the least fun things I had to do. We had a (mid eight figure's) professional service practice purely dedicated to Spreadsheet Management and Controls. I have no real skin in the game on Lambda and will probably have a play with it just out of curiosity - but holy cow can I see a portal to the void opening......a whole bunch of "prototype" business code that becomes production in the back office....
    posted by inflatablekiwi at 8:35 AM on April 13, 2021 [7 favorites]


    theodolite: I'm going to post "Excel is the only useful Lisp" on hacker news until they crucify me

    If Socrates would've said that they would've forced the hemlock down his throat instead of giving him time to drink it himself.
    posted by clawsoon at 8:38 AM on April 13, 2021 [10 favorites]


    Jesus, if the problem was that VBA was an unpleasant language for creating user-defined functions, why on earth break the spreadsheet model instead of, you know, replacing VBA with something better? Or better still, making a sandboxed API so that users could create functions in any language they like. Imagine being able to solve a problem in Excel by writing a function in Python that treated the sheet like a pandas DataFrame...

    Also, calling it LAMBDA was a mistake. I know the CS meaning of the term, but it's completely opaque to the vast majority of Excel users. FUNCTION would be only a little longer and much more obvious in its meaning. FUNC would be shorter and almost as obvious.

    Finally: if you're going to encourage people to write full-blown functions in cells, then for god's sake add an option to make the cell editor a normal text editing space where the arrow keys, enter, etc aren't overloaded. This is just going to lead to impossible to decipher messes of LAMBDAs and parentheses trailing off the side of the input box. But then I'm of the opinion that if you're doing something in Excel that requires more than 3 levels of nested parentheses then you should either break up your formula or use a different tool, usually the latter.
    posted by jedicus at 8:42 AM on April 13, 2021 [14 favorites]


    j_curiouser: srsly. it boggles my mind that any profit-driven entity would put business-critical tools in a format that: can't be diffed if source code control, can be modified by any end user, doesn't have step-through debugging...i guess the argument is that it will work for most of the people most of the time.

    Excel, having no fixed structure, deals with process changes more cheaply than anything else. Humans are constantly coming up with process changes, whether they should or not, so humans love it. Part of the software I work on is "use this instead of Excel", and it only wins occasionally for precisely this reason.

    In other news, I can't wait until Excel's new AI starts identifying dates as gene names.
    posted by clawsoon at 8:44 AM on April 13, 2021 [6 favorites]


    Consider the bigger picture: you can use LAMBDA to implement another more user-friendly language on top, like Brainfuck.
    posted by Pyry at 8:47 AM on April 13, 2021 [17 favorites]


    process changes

    Not Invented Here® syndrome is usually reserved for expensive software developers using complex tools. Excel brings Not Invented Here® syndrome to the regular user.
    posted by clawsoon at 8:50 AM on April 13, 2021 [6 favorites]


    yeah, i worked at a network management shop that dictated everything be done with powershell scripts so 'anyone can change them as needed'. outside source code control. some bozo changes something, and i have to parse a thousand lines of shit procedural code to clean up their mess. ugh.

    and undo whatever bullshit the *really* did the network.

    got outta there fast.
    posted by j_curiouser at 8:58 AM on April 13, 2021 [1 favorite]


    Is this the singularity?
    posted by Jacen at 8:58 AM on April 13, 2021 [3 favorites]


    I had to check the date when I read it; I thought it was an April Fool's.

    The blog post was published on March 12th, but the last update on the right hand side shows December 3rd. Thus proving this was definitely written by the Excel team.
    posted by Lanark at 9:04 AM on April 13, 2021 [62 favorites]


    Can Excel mine cryptocurrency now?
    posted by Jacen at 9:08 AM on April 13, 2021 [4 favorites]


    The blog post was published on March 12th, but the last update on the right hand side shows December 3rd. Thus proving this was definitely written by the Excel team.

    ahahahahahahahahahahahahahahahahaha
    posted by clawsoon at 9:09 AM on April 13, 2021 [18 favorites]


    Good news. According to the Office 365 help page for Lambda, it will return a #VALUE! error if you try and use more then 253 parameters. So I guess...err....yeah......I mean surely no one would try and add more than 253 parameters...oh god...they would, right?

    Why 253? I'd understand 255 given 2^8, but 253?
    posted by inflatablekiwi at 9:31 AM on April 13, 2021 [5 favorites]


    Why 253? I'd understand 255 given 2^8, but 253?

    Makes as much sense as the 260 character path length limit.
    posted by clawsoon at 9:34 AM on April 13, 2021 [3 favorites]


    260 I believe is made up of the dive letter, a colon, a slash, a 256 max character string, and then a nul character. So 260 at least makes *some* sense (e.g. 'C:\this-part-can-be-256-characters-before-you-have-the-NUL')
    posted by inflatablekiwi at 9:39 AM on April 13, 2021


    This is a great thing and will increase transparency, readability and debuggability of Excel workbooks, as the linked posts clearly elucidate.
    posted by ambrosen at 9:44 AM on April 13, 2021 [4 favorites]


    I assume it's 253 because there are 2 parameters used internally.
    posted by skymt at 9:48 AM on April 13, 2021 [4 favorites]


    Why 253? I'd understand 255 given 2^8, but 253?

    Presumably the other two parameters are the formula and the cell's location, which is nessecary to calculate all those fancy r1c1 relative offset things.
    posted by pwnguin at 9:50 AM on April 13, 2021 [5 favorites]


    I interviewed at a company several years ago for a CTO-ish position (really more of a big fish in a small pond thing). This company was basically a middle man, connecting customers with wholesalers in a certain industry.
    In our first conversation the headhunter had asked me I "knew Excel." Sure, I figured. I can VLOOKUP with the best of them.

    Well.

    Because this industry is a lot of mom-and-pop operations it's very low tech, so there's no EDI data transfer or other standard ways of taking the end-customer orders and transmitting them to the wholesaler. Instead, the company had a large collection of spreadsheets, where one could take customer data and then run a macro to re-format that data into whatever format was expected by the specific wholesale system required.

    So in the actual interview, I was asked how I felt about maintaining business-critical data conversion macros, which were the CEO's sacred cow and would never be replaced.

    I was very diplomatic in raising some questions of this approach, but alas, they never called me back.

    I just looked them up and not only are they still around, they've been getting millions of dollars in investment money. I wonder if any of those dollars went towards killing the sacred cows.
    posted by Nonsteroidal Anti-Inflammatory Drug at 9:52 AM on April 13, 2021 [8 favorites]


    I assume it's 253 because there are 2 parameters used internally.

    "it was the first time since Excel's inception, that I can remember a user taking the parameters internally, and it will be a shame indeed if the param-dectomy can't be performed on them in time to save this quarter's forecast deadline"
    posted by inflatablekiwi at 9:52 AM on April 13, 2021


    Why 253?
    Excel also has a limit of 253 for the 'Maximum number of line feeds per cell', so I'm guessing row no. and col no. are stored in the same place as the contents of each cell.
    posted by Lanark at 10:11 AM on April 13, 2021


    Ahh the 253 thing I guess makes sense now...thanks all
    posted by inflatablekiwi at 10:17 AM on April 13, 2021


    Can each of the 253 parameters be an array?
    posted by paper chromatographologist at 10:23 AM on April 13, 2021 [7 favorites]


    Can one of the parameters be another lambda?
    posted by clawsoon at 10:42 AM on April 13, 2021 [8 favorites]


    In health care, social services, and I’m sure other spaces, our ability to solve analytical and projection problems saves lives, but my org and certainly most can’t afford either the serious software or the expertise needed to solve these things cleanly and well. So a lot of “how people get care and medicine and lots of other stuff” is effectively hacked together by a handful of nerds like me and built in consumer software out of Lincoln logs and and rubber bands into horrific Rube Goldberg Machines. And I lie awake at night sometime and think that if I hired a real programmer to recreate things I’ve built in excel, after they were done crying, they’d wind up with something a thousand times more stable, and useful, and elegant. But look, I can’t hire them


    So I'm seriously curious, do folks think it's better to be (or move towards) the generalist hack or the hired gun 'pro'?

    (I ask this full aware how ludicrously unstandardized the programming field is)
    posted by Reasonably Everything Happens at 10:46 AM on April 13, 2021


    I love that this is targeted at users who very much are Not Programmers

    ...yeah, and so naturally it's based in a conceptual model that's beyond the reach of many Actual Programmers.

    (Or maybe it's just me. I swear I think I sort of understand what currying is but when it comes to figuring out when or why I'd want to transform a function that takes multiple arguments in a tuple as its argument, into a function that takes just a single argument and returns another function which accepts further arguments, one by one, that the original function would receive in the rest of that tuple, I kinda draw a blank)
    posted by ook at 10:56 AM on April 13, 2021 [3 favorites]


    I assume it's 253 because there are 2 parameters used internally.

    You know what they say about assumptions about Excel...
    posted by They sucked his brains out! at 10:58 AM on April 13, 2021 [4 favorites]


    Literally popped popcorn for this thread, not disappointed. ;-)

    Can the lambda's write another lambda and insert it into any given cell?

    Are there TB sized excel spreadsheets common in the wild?
    posted by sammyo at 11:04 AM on April 13, 2021 [3 favorites]


    Oooo ooo, can they rewrite themselves, and oh oh oh, side effects!
    posted by sammyo at 11:05 AM on April 13, 2021 [2 favorites]


    The Lambda Classical Caucus is going to get some confused mentions and emails.
    posted by lesbiassparrow at 11:05 AM on April 13, 2021


    At first I thought it was hilarious that their answer to giving a spreadsheet a domain-specific programming language was to make lambda a primitive

    Then I realized that functional programming probably does fit well with a spatial programming paradigm, so at least they didn't introduce for loops and gotos as the answer
    posted by polymodus at 11:06 AM on April 13, 2021 [1 favorite]


    I see this as something like adding subroutines to assembly language. It's a step towards structured programming and so mostly an organizational thing. It gives you at least a glimmer of hope of devising a spreadsheet based system that isn't a total labyrinth. You can provide sanely named high level functions for common business tasks which can be composed together with normal excel functions in formulas.

    The fact that these named routines can be fairly sophisticated in a functional programming sense is something that should probably be hidden from the end user if you are designing a set of reusable functions for your company, but can allow someone who gets it to do pretty wild stuff under the hood. Even still, this is conceptually like pivot table level stuff here, so it is not actually a full computer science education. It's not simple but it doesn't do simple things, either.

    People will create nightmares in any language regardless of power. It's part of the learning process to do that.
    posted by feloniousmonk at 11:11 AM on April 13, 2021 [4 favorites]


    polymodus: Then I realized that functional programming probably does fit well with a spatial programming paradigm

    Excel is a functional language. Forget about Haskell, Excel is the most popular functional language in the world.
    posted by clawsoon at 11:18 AM on April 13, 2021 [9 favorites]


    So I'm seriously curious, do folks think it's better to be (or move towards) the generalist hack or the hired gun 'pro'?

    The aforementioned health care and social services should be paid commensurate to their contribution to society, as should the developers (I am one) that they can't currently hire. This solves the entire problem and they'll be able to bring on as many developers as they need.
    posted by Nonsteroidal Anti-Inflammatory Drug at 11:21 AM on April 13, 2021 [4 favorites]


    So I'm seriously curious, do folks think it's better to be (or move towards) the generalist hack or the hired gun 'pro'?

    (I ask this full aware how ludicrously unstandardized the programming field is)


    Well I can’t speak for everyone in the field, but in my experience most people who are ‘techie’ who I’ve encountered are generalists hacking up; managers who are moonlighting as analysts (me), analysts who are programming, etc. In general health care and social services are made out of many many more little players than large organizations, and there is a substantial disparity. So my mother works for a hospital system which employs actual developers and real-ass mathematicians, and is part of a Public Ivy school that can bring tremendous resources to bear. Like, during the early days of the pandemic, they didn’t just make their own sanitizer, they developed and patented a Covid test, and created modeling software for supply chain and icu predictions and helped make their own ventilators. Super cool. But I work for a lil old Medicaid provider and we got... nothing like that. So our problems are large enough to be insurmountable but small enough not to matter, and we just don’t pay enough (or make enough) to afford in house development or good hired guns. We’ve been working on service scheduling and routing, and my IT department says we can’t afford to host and maintain a bespoke option even if it was free and the SAAS we can find is either too general or too costly or too niche and often all three. So we signed with this new software which is going to be a godsend in a lot of ways but we’ll still have to do a lot of work outside of it for optimization.

    I think one thing we may see is that as/if programming becomes more of a trade than an elite role, we’ll see more people working in social services at lower salaries for good outcomes. For example, food bank operations are just not a sexy area for programmers, and food banks are super broke, and grants are unreliable, and it’s just not gonna be a space that can support lots of devs making $200k or even $100k; the scales just don’t pencil out. But maybe in the future a small-city sized food bank could afford an analyst and a dev at $70ish each to optimize deliveries and keep stock fresh and automate eligibility and all that good stuff. Right now my organization is trying to figure out how we can hire an analyst or two without overwhelming them, because we could use 5 but we can afford 1. It’s just tough to balance, and we can’t just lower our profit margin to make up the difference; we don’t have a profit margin, and every penny is public money, and we just gotta keep making it work.
    posted by skookumsaurus rex at 11:35 AM on April 13, 2021 [7 favorites]


    Yes, but under which SCP Object Class should LAMBDA be classified?
    posted by lumosh at 12:15 PM on April 13, 2021 [4 favorites]


    >Excel should come with a built-in function HALT(lambda, range)
    I have a marvelous proof that this =HALT() works, but this spreadsheet is too small to contain it.
    posted by k3ninho at 12:16 PM on April 13, 2021 [17 favorites]


    Simon Peyton Jones is just fucking with us, right?

    He's the guy who does all his slides in (the Apple version of) Comic Sans, so ¯\_(ツ)_/¯.
    posted by acb at 12:57 PM on April 13, 2021


    Lolol so glad to see others commenting whose mouths literally dropped open or who audibly said "SAY WHAAAAT" upon reading this news
    posted by potrzebie at 1:00 PM on April 13, 2021


    Wait does this mean Kelly Rowland is finally going to be able to text her boyfriend with Excel?
    posted by Mr.Encyclopedia at 1:03 PM on April 13, 2021 [2 favorites]


    > The blog post was published on March 12th, but the last update on the right hand side shows December 3rd. Thus proving this was definitely written by the Excel team.

    The blog post is dated "12-03-2020 08:00 AM", which is either March 12 or December 3 depending on how you write and read dates.

    Since the first comment on the blog post is dated "Dec 03 2020 08:35 AM", It's safe to assume the blog was posted on the third of December.

    Make fun all you want of automated tools being unable to get dates right, but humans don't have a fantastic rate of success either.
    posted by at by at 1:07 PM on April 13, 2021 [7 favorites]


    Spreadsheets in general (and Excel in particular) are an interesting beast. They offer a user a tool that offers some level of programmability and database without having to learn a full programming language, or have a database installed. Lots of stuff (indeed, far too much) gets built and extended there.

    I get it. I can code (Perl, Lisp, and JavaScript), and have an understanding of databases. But a lot of times, it's easier to just build something quick and dirty in Excel, especially when another tool is not available. For this reason, I'm actually rather excited that this looks like I could put together my own formula fairly quickly.

    That said, I've also done desktop support. Plenty of times, some random, non technical user builds a spreadsheet not just because it's what they have but it's what they know. They don't treat it as disposable, or try to build it in such a way it will be a sustainable bit of code. Two years later, the company runs on that, and cleaning it up would break more than it fixes.

    I totally get how creating this capability can be like handing a toddler a nuclear weapon.

    Can Excel mine cryptocurrency now?

    No, but a Commodore 64 can.
    posted by MrGuilt at 1:08 PM on April 13, 2021 [3 favorites]


    First impression: This is twelve days late for April fools' (yeah, yeah, the article is from Jan 25)

    Second impression: time to immanentize the eschaton. let's go write a new LLVM backend!
    posted by qxntpqbbbqxl at 1:12 PM on April 13, 2021 [3 favorites]


    Can Excel mine cryptocurrency now?

    No, but a Commodore 64 can.


    OH YE OF LITTLE FAITH
    posted by clawsoon at 1:24 PM on April 13, 2021 [3 favorites]


    theodolite: I'm going to post "Excel is the only useful Lisp" on hacker news until they crucify me

    Does anyone else remember SIAG Office, aka Scheme In A Grid?
    posted by cstross at 1:43 PM on April 13, 2021 [1 favorite]


    The way I define functional programming is that functions are first class citizens, I think that's how SICP teaches it. Does Excel allow passing of functions, prior to this new thing? Actually, if they did then having lambda isn't that unreasonable.
    posted by polymodus at 1:47 PM on April 13, 2021


    Scheme In A Grid would be a good name for chess if they hadn't already called it chess
    posted by theodolite at 3:06 PM on April 13, 2021 [3 favorites]


    Now all we need is a bunch of Twitch streamers to take up LAMBDA as their game of choice, so we can eventually get Excel running inside Excel like some sort of redstone-circuit-meets-quine abomination, at which point the singularity will occur and the simulation will reset.
    posted by Mayor West at 4:13 PM on April 13, 2021 [1 favorite]


    This thread is... oof. I have spent a lot of my career stepping in when something outgrows Excel. I'm painfully aware of its limitations. And I think LAMBDA is great.

    People are already writing complex logic in Excel formulas, the world basically runs on that. LAMBDA lets them easily extract that logic into a named function. You can go nuts with CS theory (Andy Gordon and Simon Peyton Jones did, that's kind of their thing) but for the vast majority of Excel users this is just providing a simple+essential mechanism for encapsulation and reuse.

    For all that "programming in Excel" gets a bad reputation, it's the world's most successful IDE for people who don't consider themselves programmers. I think it's important to take that success seriously and improve on it, instead of shunting people off to a "real programming language."
    posted by ripley_ at 5:13 PM on April 13, 2021 [18 favorites]


    ♫ - "Paging Bob Howard to the white courtesy phone ..."
    posted by sebastienbailard at 6:50 PM on April 13, 2021 [1 favorite]


    I am optimistic. The bad spreadsheets I have to deal with have usually accreted garbage over years of quick fixes, like a terrible pearl. I cannot imagine most of the people who makes those would want to wade into lambda calculus; it's rare enough to find somebody who will write a new macro.
    posted by solarion at 7:33 PM on April 13, 2021 [3 favorites]


    Please put in a unit testing framework, Simon?

    They could have called it =FUNCTION() and not elicited the "weird Greek comp sci aaaaa" response, but name binding already existed separately, so unnamed function values it is.

    I don't see that it will make spreadsheets worse, honestly. I expect it'll replace some existing copy-pasting of formulas, making them more correct. Spreadsheets are notoriously incorrect to start with, though.
    posted by away for regrooving at 9:13 PM on April 13, 2021 [3 favorites]


    I don't see a use case where Excel VBA wouldn't do just as well or better.
    posted by hypnogogue at 11:17 PM on April 13, 2021


    VBA is powerful but... it’s a separate scripting language bolted onto Excel, with security concerns up the wazoo (especially when sharing spreadsheets). LAMBDA lets you define functions using Excel’s usual formula language, and in particular it allows for a nice incremental development story (write a formula inline as usual, lift it up to a LAMBDA function when needed).

    I don’t see LAMBDA as replacing VBA, but it’s a nice option to have.
    posted by ripley_ at 11:27 PM on April 13, 2021 [1 favorite]


    The lambda that has a name is not the true lambda.
    posted by acb at 1:09 AM on April 14, 2021 [3 favorites]


    The bad spreadsheets I have to deal with have usually accreted garbage over years of quick fixes, like a terrible pearl.

    When I've run across these they've been less your pearl and more your fatberg.
    posted by flabdablet at 4:24 AM on April 14, 2021 [4 favorites]


    I've been a programmer for decades. When I need to calculate something, I will often write a quick python script. But if I need to calculate something and I don't have a complete grasp of yet, or that I need to share with others, I often open a spreadsheet. It's easy to rag on complex spreadsheet logic, or complain about people using spreadsheets as databases, but the truth is that they solve a problem better than the alternatives. Yes, there are many messy spreadsheets I can "fix" by converting them into a set of tables and forms with a database. And for some processes, this is appropriate. But for many, it is not. It's often the job of a programmer to be reductionist, but a spreadsheet rarely has a single easily definable purpose - even when the people who use it think it does. The process of converting a moderately complex spreadsheet into an application is absolutely going to be just as complex and error-prone as the spreadsheet itself, and at the end, you might have an application that does not do half of what the users of the spreadsheet really needed.
    posted by Nothing at 4:50 AM on April 14, 2021 [8 favorites]


    This thread is... oof. I have spent a lot of my career stepping in when something outgrows Excel. I'm painfully aware of its limitations. And I think LAMBDA is great.

    People are already writing complex logic in Excel formulas, the world basically runs on that. LAMBDA lets them easily extract that logic into a named function. You can go nuts with CS theory (Andy Gordon and Simon Peyton Jones did, that's kind of their thing) but for the vast majority of Excel users this is just providing a simple+essential mechanism for encapsulation and reuse.

    For all that "programming in Excel" gets a bad reputation, it's the world's most successful IDE for people who don't consider themselves programmers. I think it's important to take that success seriously and improve on it, instead of shunting people off to a "real programming language."


    Totally. The right solution to something running on an overly complex Excel sheet is to gradually fix it, surprisingly coders despite their notional love of Agile style development are all too keen to re-write from scratch when the original is in Excel.

    Here are the steps we take when spot an Excel that's getting too complex and that cannot simply be moved into PowerBI:

    1) Store it centrally. Often the problem is actually that the "definitive" version is being emailed around. Stick that sucker on sharepoint.
    2) Add a manual version control sheet and enforce at least quick notes on what changes are being made by whom and when
    3) Separate inputs, calculation, and outputs within the workbook by putting them on sheets
    4) Document key logic in the sheet
    5) If persistent data exists that is driving logic, and that data is actually coming from somewhere else (often something that is a database), modify the sheet to automatically pull "fresh" data from that db. That reduces the Excel file to doing post-processing and reduces the amount of state that is just floating around.
    6) If there is very complex but never changing data processing going on in the Excel sheet to prepare the data from the db for analysis, split some or all of that into a db query instead. (Only if this really doesn't need to be changed by end-users) and document in the Excel sheet what and how is being done to get the data.
    7) If possible, find someone in the user team who can be trained on basic SQL so that they have some understanding of the previous step.

    In many cases, only a few of these easy steps resolve virtually all of the difficulties without introducing any new ones.

    Sure, sometimes what you need to do is replace the whole thing but a lot of ad-hoc analysis continues to be required and we don't all live in a world where everyone has a Jupyter notebook open at all times (soon may that come to pass!).
    posted by atrazine at 5:04 AM on April 14, 2021 [17 favorites]


    Excel is fantastic. The spreadsheet is perhaps the only information era innovation that's not a readily recognizable facsimile of some pre-existing non-digital process. Word processors mimic writing on paper, email and chat are kind of like letters or talking, a database is a faster filing cabinet. But the spreadsheet is like a white collar workbench in a way that I don't think has a close analogue analog. It's Emacs for the masses.
    posted by dmh at 6:36 AM on April 14, 2021 [4 favorites]


    But spreadsheets pre-date computers by a long shot. Now of course most people had no exposure to such things save for perhaps the ledger when balancing their check book.
    posted by mmascolino at 8:59 AM on April 14, 2021 [3 favorites]


    The problem with complex spreadsheet logic is that they tend to be incredibly fragile tools. I'm currently working in logistics for a large project, and we are running everything through one janky excel document, which falls over whenever anyone entering stock movements accidentally types into the wrong cell, uses cells that are outside the reference range of the formulae because we had more stock movements this month than last, or a billion tiny little things where the person who hacked it together thinks "well OBVIOUSLY it will break if you do THAT" but isn't necessarily apparent to everyone (or sometimes anyone) else.

    In some ways, this is a fairly extreme example (we should have better tools given the scale and scope of operation we are) in other ways not at all. Similar excel-based solutions I've encountered elsewhere have all had the same problem: they are very, very easy to break, sometimes in hard-to-detect ways, if used even slightly wrong. And any time it is a document that several people on an organisation need to interface with, that will happen.
    posted by Dysk at 10:04 AM on April 14, 2021 [5 favorites]


    5) If persistent data exists that is driving logic, and that data is actually coming from somewhere else (often something that is a database), modify the sheet to automatically pull "fresh" data from that db. That reduces the Excel file to doing post-processing and reduces the amount of state that is just floating around.
    6) If there is very complex but never changing data processing going on in the Excel sheet to prepare the data from the db for analysis, split some or all of that into a db query instead. (Only if this really doesn't need to be changed by end-users) and document in the Excel sheet what and how is being done to get the data.


    These are incidentally also very good ways to get your database admin to hate you with the fire of 1000 suns.

    Our ancient barely-supported MIS sucks, so rather than lookup data IN the MIS, the admin staff just nag the db admin to write them some custom excel sheet that pulls the exact data they want out of the underlying MIS database so they can play with it. This is of course a trap, as it means the db admin is also now on the hook to debug, fix and further customise these spreadsheets forevermore, and moreover, is the only one able to do so as the database structure is gnarly as fuck.

    Of course, we can't ever replace the MIS with one that is aware the internet exists or is actually usable, because there are now a bajillion critical workflow spreadsheets infecting every facet of administration that would all need to be updated and it would be 'too disruptive', as well as super expensive for a new MIS. Nor can we afford a backup db admin to take some of the weight off.

    Yes, the IT department are aware we're totally fucked if the db admin gets hit by a bus or retires. Some nights, I even get to go to sleep *without* remembering that first.
    posted by Absolutely No You-Know-What at 10:06 AM on April 14, 2021 [5 favorites]


    If I was that DBA I'd be going for 50% per year salary increases.
    posted by seanmpuckett at 12:24 PM on April 14, 2021


    But spreadsheets pre-date computers by a long shot. Now of course most people had no exposure to such things save for perhaps the ledger when balancing their check book.

    My grandfather used spreadsheets much like that! So that definitely came to mind. But I think those are more like sheets for musical notation. Where the digital spreadsheet differs is that it doesn't just represent the musical symbols, so to speak, but in a way it models and produces the sounds as well. It's precisely this WTF-is-going-on weave of representation and modeling/processing that makes Excel so infuriating from an admin/software engineering perspective, and this LAMBDA thing is sure to add WTF-circles to WTF-hell, but now recursively. To an extent it's a problem of riches, though. Part of the reason why the Excel-mindset generates so many shitty solutions is because overall it's so productive.
    posted by dmh at 1:42 AM on April 15, 2021 [1 favorite]


    These are incidentally also very good ways to get your database admin to hate you with the fire of 1000 suns.

    Sucks to be them, I guess. Databases are for using.

    The counterfactual which is often proposed here is to build a non-Excel software solution which will also lead to custom queries to feed it so there's no way out.
    posted by atrazine at 1:58 AM on April 15, 2021 [2 favorites]


    Yes, the IT department are aware we're totally fucked if the db admin gets hit by a bus or retires.

    I had a very unwelcome sobering moment a couple of years ago when I realized that for several crucial things at work, I am the Bus Guy.

    Fortunately, I can't afford to retire. Ever.
    posted by Mr. Bad Example at 7:06 AM on April 15, 2021 [1 favorite]


    As Bus Guy, you perhaps can't afford not to retire. Leaving the company no choice but to retain you as a consultant for twice the price.
    posted by Sockdown at 8:27 AM on April 16, 2021 [5 favorites]




    « Older 412 is the new 420   |   “If a sheep could, it’d die twice.” Newer »


    This thread has been archived and is closed to new comments