30 ms·
Lambda: The Ultimate Excel Worksheet Function
- dang 5y agoRecent and related: Lambda: The ultimate Excel worksheet function - https://news.ycombinator.com/item?id=25923628 https://news.ycombinator.com/item?id=25923628 - Jan 2021 (4 comments)
- wgetch 5y agoThere 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: https://xkcd.com/2453/ https://xkcd.com/2453/ 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... > (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.)
- maest 5y agoTo be fair, no computer is an actual TM since memory is finite.
- quonn 5y agoIt can pause and ask you to temporarily attach another hard disk. Could be infinite as long as one can afford buying disks.
- deleted 5y ago[deleted]
- jaza 5y agoBut the amount of raw material on the planet needed for manufacturing more disks is finite. The amount of raw material in the universe is finite at a given point in time (it could be infinite over time, we don't know if time is infinite either). I think we've already established (especially over the past year) that fiat money is infinite. The Turing machine is a mathematical model. Infinity only exists in the world of mathematics. The physical world is by definition finite.
- gkop 5y ago> The physical world is by definition finite This isn't obvious to me, would you elaborate?
- anamexis 5y agoClassic HN: from article on new Microsoft Excel feature to semantic debate on the definition of the universe in record time.
- willhslade 5y agoTake everything in the world. Every physical piece. Break it into the smallest slice you care to: (atoms, quarks, whatever). The count of those things is bounded. It's a huge number, but it's finite. Infinity is not a real concept, it's imaging that there is no number that can be bounded.
- Dangeranger 5y agoMaybe, there are hypothesis’ that the Universe is infinite. The observable Universe is finite.
- bickeringyokel 5y agoIf the universe is infinite, Then the observable universe is only as finite as the length of your life and capability to traverse through space.
- aidenn0 5y agoRight, all computers have a bounded amount of state, so they can only parse regular languages.
- dang 5y agoOne small recent thread: Lambda: The ultimate Excel worksheet function - https://news.ycombinator.com/item?id=25923628 https://news.ycombinator.com/item?id=25923628 - Jan 2021 (4 comments)
- teraflop 5y agoThe blog post'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 "functional programming". https://en.wikipedia.org/wiki/History_of_the_Scheme_programming_language#The_Lambda_Papers https://en.wikipedia.org/wiki/History_of_the_Scheme_programm...
- devin 5y agoSpecifically the "Lambda: The Ultimate" bit.
- nexuist 5y agoFunnily enough the two examples they provide in this article (reverse string and factorial) were the exact two first problems I encountered in the Scheme class I took in college. I'm guessing both of these problems are defined in those papers.
- unhammer 5y agoThe authors of the post have both been central to Haskell's development, among other PL things – I'm guessing they've read them all :)
- alkonaut 5y agoI 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?
- dllthomas 5y ago> lambda function names I'm not sure what you mean by this.
- alkonaut 5y agoI was under the impression that it would ship with a standard library with functions, and not require the user to define common ones in every workbook (HEAD, MAP, REDUCE, ...) I can see the definition of HEAD in their example but having to do that in my Swedish Excel would be bloody terrible.
- msla 5y agoThe "Lambda" paper Steele and Sussman never wrote! http://lambda-the-ultimate.org/papers http://lambda-the-ultimate.org/papers Including: "Lambda the Ultimate Imperative" "Lambda the Ultimate Declarative"
- jhgb 5y agoDoesn't Sussmann's propagator model work kind of count as "Lambda the Ultimate Spreadsheet"?
- kkylin 5y agoCame here to say this: my first thought was that this can provide a nice interface for implementing Radul & Sussman's propagator model https://dspace.mit.edu/handle/1721.1/44215 https://dspace.mit.edu/handle/1721.1/44215
- booleandilemma 5y agoThe 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. Your scientists were so preoccupied with whether or not they could, they didn’t stop to think if they should.
- dang 5y ago"Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something." https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
- deleted 5y ago[deleted]
- _Nat_ 5y agoGreat to see Excel adding more features! The argument I'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. It'll be neat to see how the user-base adapts to a more powerful feature-set. I mean, it'd seem like a lot of folks will be thrilled, finally having some extra functionality without having to use macros/VBA/VSTO/COM/etc., though how might non-technical folks feel about a coworker sending them a spreadsheet with function-values?
- SirSourdough 5y agoMost organizations I have seen already only have a couple people who can actually make and edit the advanced sheets used by the org and lots of people who use those sheets with a very, very rudimentary knowledge of Excel to generally get their jobs done. I don't really see the addition of new advanced functionality changing that paradigm.
- akdor1154 5y agoOh no - can't they just integrate M instead? It's a great (skeleton of a) language with a similar basis but far nicer to write.
- aperrien 5y agoWhat is M?
- dragonwriter 5y agoM is the language of Power Query. https://docs.microsoft.com/en-us/powerquery-m/ https://docs.microsoft.com/en-us/powerquery-m/
- phonon 5y agoYou might be interested in https://powerapps.microsoft.com/en-us/blog/introducing-microsoft-power-fx-the-low-code-programming-language-for-everyone/ https://powerapps.microsoft.com/en-us/blog/introducing-micro...
- tunesmith 5y agoWriting 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/unfold as I wish.
- zamadatix 5y agoIf you save it as a named function you can add a comment and that will appear as the tooltip while you're working on calling the function. If you're just wanting to comment how/why the function works in the cell you're defining or instantiating and immediately using it Excel has comments and notes you can attach to the cell or a group of cells.
- jxy 5y agoNow we have a legitimate use case for the Y combinator. What's next? A full implementation of scheme? Common lisp?
- taltman1 5y agoCheck out this paper from my friend Ronen, and Prof. Fateman, putting Lisp into Excel: "A paper written with Ronen Gradwohl on Lisp and Symbolic Functionality in an Excel Spreadsheet: Development of an OLE Scientific Computing Environment, August, 2002. (code available on request) " https://people.eecs.berkeley.edu/~fateman/algebra.html https://people.eecs.berkeley.edu/~fateman/algebra.html
- ashton314 5y agoImplement Emacs Lisp and throw in evil-mode. Now you can run the world's most extensible editor with Vim bindings INSIDE EXCEL!! Also, Emacs's org-mode implements some basic spread-sheet utilities, so...
- zhengyi13 5y agoAm I the only one who read the title and was immediately reminded of http://lambda-the-ultimate.org/ http://lambda-the-ultimate.org/? (Is the title a deliberate call to that site, or something even older?)
- joliv 5y agoThe common parent is "Lambda: The Ultimate Imperative" (1976) https://dspace.mit.edu/bitstream/handle/1721.1/5790/AIM-353.pdf https://dspace.mit.edu/bitstream/handle/1721.1/5790/AIM-353....
- SatvikBeri 5y agoAnd more generally, the Lambda Papers by Guy Steele and Gerald Sussman, which also include "Lambda: The Ultimate Declarative", "Lambda: The Ultimate GOTO", and "Lambda: The Ultimate Opcode". https://commons.wikimedia.org/wiki/Lambda_Papers https://commons.wikimedia.org/wiki/Lambda_Papers
- dllthomas 5y agoAnd it's being discussed there (... shallowly, so far) as well :D http://lambda-the-ultimate.org/node/5621 http://lambda-the-ultimate.org/node/5621
- ZeroCool2u 5y agoWow, that recursive example is a nightmare. I can't imagine being the person that's asked to debug what's going wrong here a few years down the line.
- disconcision 5y agoI assume you don't mean the string reversal example. The fixed-point fibonacci isn't meant to show the way you'd actually write fibonacci; rather it's intended to show the debatably interesting property that you /can/ write a recursive function without giving it a name.
- zamadatix 5y agoThis 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 "scratch" 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 "send" on the email. The additions they've added make this so simple and ubiquitously available I wouldn't even call it ugly for that use case anymore.
- perl4ever 5y agoI don't understand the point of this function, when Excel already has Power Query. It doesn't seem like anyone who is literate in functional programming would want to use this, and anyone who isn't up to it wouldn't either. One of the most annoying things about Excel is it has so many parts apparently designed by people or groups that didn't talk to each other and didn't have a grasp of all the rest of it, let alone the world of the (various groups of) users. Who ordered another 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? Saying "yay people are making videos" only makes me think of all the horrific tutorials on Power Automate. And this: https://xkcd.com/763/ https://xkcd.com/763/
- greggyb 5y ago> I don't understand the point of this function, when Excel already has Power Query. Because Power Query is not a spreadsheet application, and has some much more severe performance cliffs than Excel proper does.
- perl4ever 5y agoTo you and easton, my point is that even if Power Query has shortcomings, it's clearly the best thing to build on and improve, assuming VBA is dying a slow death and can't be revived. Even if, like, you wanted to make another separate language, it should still resemble Power Query, only better. I don't think people at Microsoft are looking at Excel as a whole, like lost souls squatting in a mansion and building sand castles in the room that they live in that have no relationship to the actual building and what it needs to keep from falling down. I'm not sure what you mean by performance cliffs. Can you give an example of where and how you would better accomplish something without Power Query? Are you talking about processing data in the range of a few hundred megabytes?
- Closi 5y agoPowerQuery isn’t a replacement for excel though - it’s a data preprocessing tool for analysts. It’s not going to replace functionality of core spreadsheet-based excel for accountants, for instance, who typically won’t have a use for PowerQuery as their data is structured differently.
- MegaDeKay 5y agoIf you want to stay on top of what is going on in Excel, the team's Excel Blog is worth checking out now and then. https://techcommunity.microsoft.com/t5/excel-blog/bg-p/ExcelBlog https://techcommunity.microsoft.com/t5/excel-blog/bg-p/Excel...
- eweise 5y agoI really like what these guys are doing https://numbrz.com/ https://numbrz.com/ 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's stores are updated.
- Noumenon72 5y agoI'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.
- deleted 5y ago[deleted]
- airstrike 5y ago> 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. That's the biggest issue with LET / LAMBDA at the moment. Users are terrified of the name manager and simply do not understand what they are for or what "scope" 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
- the_other_b 5y agoI have a BS in Computer Science and whenever I help people pick up programming I notice scope is always one of the toughest concepts for them to grasp. It could of course be a reflection of my teaching ability, but it always seems to be a tough one.
- oooooooooooow 5y agoStrange! Sadly I don't remember the experience of learning about scope myself (I was too young), now I find it hard to see the "mind state" that makes it hard to understand. Isn't it a feature of natural languages to have the same word assume different meanings depending on where it's used? The concept translates nicely, and in PLs it's completely explicit whenever this happens.
- VVertigo 5y agoThat is an interesting thought: although, it is something that non-native speakers struggle with when learning a new language. I wonder if that is a factor when learning a new computer language/concept too. I suspect it is also related to the Curse of Knowledge (https://en.wikipedia.org/wiki/Curse_of_knowledge https://en.wikipedia.org/wiki/Curse_of_knowledge). Once you are past the hurdle of initially learning a concept it makes it hard to imagine not being able to grasp it: especially when dealing with abstract concepts such as scopes.
- technicalbard 5y agoSo they are building LISP inside Excel.... It is now a corollary of Greenspun's Tenth Rule of Programming...
- oneplane 5y agoFunny how the top of the article starts with "custom functions without code" and then immediately shows code. I get that calling code by its name can make it sound scary, but this whole notion of it being 'easy because it is not code' seems to be a big fat lie for comfort. Same goes for the magic no-code systems where code is replaced with 'expressions' or graphical 'workflows' which essentially is exactly the same thing, only shaped slightly differently. This makes me wonder if it wouldn't be much better if we could focus on making people be able to code and have more 'coding capacity' instead of having less of that capacity and then reducing it even more by using some of it to create 'let us pretend this is not code'-applications.
- srfvtgb 5y agoI guess they mean without writing VBA code.
- addicted 5y agoWhere’s the immediate code? I think those are basically Excel formulas, which in context, are not what Excel users would consider “code”.
- chrisseaton 5y agoWhat do you think is the difference between Excel formulas and code?
- qwertox 5y agoUsing an inbuilt Excel formula vs creating a formula with VBA?
- chrisseaton 5y agoBut they're both code.
- qwertox 5y ago
- iask 5y agoJust bringing the Visual Studio code control into Excel is a plus. This will remove several steps when creating and deploying addin.
- function_seven 5y agoThis 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.). Also, does this: > With LAMBDA, Excel has become Turing-complete. sound like a threat to anyone? :)
- kazinator 5y ago8 days ago: https://www.reddit.com/r/lisp/comments/mr1fvt/ms_excel_is_unpopular_due_to_lots_of_irritating/ https://www.reddit.com/r/lisp/comments/mr1fvt/ms_excel_is_un...
- peterkelly 5y agoThis is great and all, but it struck me that only Microsoft could come up with phrases like "LAMBDA is available to members of the Insiders: Beta program" and "LAMBDA complements the March 2020 release of LET". It sounds like something someone might write in a parody press release on comp.lang.scheme 30 years ago.
- maweki 5y agoI always thought the Excel computational model was elegant and useful as it didn't allow for non-termination. Now they lose termination for the use case where someone knows how to program but can't or won't program in some "normal" programming language.
- captainmuon 5y agoThis is almost what I always wanted, but it feels a bit bolted on to be honest. I don'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't allow you to write a function in "idiomatic" Excel. If I had to implement functions in Excel, I would use one of two strategies: - 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 or: - You define your calculation as usual, in B5: = 10*A5 - then in C5: =MYLAMBDA(B5; A5)(3) Meaning: Take the formula in B5, treat A5 as an argument, and return a function. Then call this function with the argument 3. 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.
- solatic 5y agoNaive but serious question: couldn't Excel create a special / 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: =LAMBDA(global_function_name, [cell_input_1, cell_input_2, ...]) Wouldn't this be a cleaner design? Trying to deal with cells whose formulas are way too long to be put in a single cell is Excel'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.
- DavidPeiffer 5y agoYou can create a multiple line formula. Alt+Enter when editing the formula. If I'm writing a longer formula that's going to be tough to read, I make it multiple lines and add spaces at the start of the lines for indentation. Makes readability so much better!
- Vaslo 5y agoA good tip there for folks to help readibility. I also love to paste in really crazy formulas here to really see them in a well formatted mode in the cell. https://www.excelformulabeautifier.com/ https://www.excelformulabeautifier.com/
- mursicale 5y agoThis has to be an April Fools, it's just posted early and we're talking about it late. It's not real right? right?
- mafalda 5y agoComing 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?
- chalst 5y agoThe PhD work of Sruti Ragavan appears to lie behind part of this development. She hasn't defended her thesis yet, but she's been recruited by the MSR Cambridge team behind this work, where she interned in 2018, IIUC just before she began her PhD work. https://sruti-s-ragavan.com/research/ https://sruti-s-ragavan.com/research/