Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Lambda: Turn Excel formulas into custom functions

techcommunity.microsoft.com

Post reply on HN
Print view View original

101–110 of 140 posts

Re: Lambda: Turn Excel formulas into custom functions

#101

Post by nullsense »

jtsuken wrote: post #59

Microsoft: Hey, we have this new feature. It's called Macros. You can execute any code you like and use it as functions in your spreadsheets. Users: Great! Let's start using it everywhere! Users: Hey! Our spreadsheets have become very slow and hackers break into our systems by executing arbitrary code in our spreadsheets Microsoft: OK! From now on you will have to save workbooks that can execute arbitrary code in a d…

I like how Lambda means very different things between AWS and Microsoft.

"Let's just create a Lambda for this"

Ok... But which one?

Re: Lambda: Turn Excel formulas into custom functions

#102

Post by pjgalbraith »

interblag wrote: post #90
zupa-hu wrote: post #84

This is a game-changer level feature. I have been dreaming of this and more other power features for a long while. Well, except for the Name Manager part in their implementation, which seems to be a total disaster. I really hope this is a first version and they are going to keep improving it as they say. One should really define the functions in the cells and be able to reference them like =A1(2). The last thing a be…

Totally agree - this could actually be a really natural bridge into programming for a lot of people whose advanced Excel skills already have them on the cusp. Name Manager though... Would be really nice if they could come up with some idioms for writing these functions in a multi-line format, with indentation, and give a slightly nicer editor. I realize that might be a bit tricky without changing the language syntax,…

They also announced the LET expression which helps a bit with the formatting problem - https://techcommunity.microsoft.com/t5/excel-blog/let-names-...

Re: Lambda: Turn Excel formulas into custom functions

#104

Post by fiddlerwoaroof »

indymike wrote: post #38
geocar wrote: post #9

I love the branding "custom functions without code" right before a bunch of code. Reminds me how early word processors (the person, not the software) were convinced to program word processors (the software this time) just by calling the programs "macros". I'm not joining Microsoft's beta program right now, but I'm curious if anyone knows the data type of a =LAMBDA?

Hmm. A lambda platform that just runs spreadsheets. This is a killer idea.

This is what I thought it was: upload spreadsheet, get API

Re: Lambda: Turn Excel formulas into custom functions

#105

Post by int_19h »

layer8 wrote: post #100

The following bit from the documentation [0] ("step 2") is a bit strange: > A good practice is to create and test your LAMBDA function in a cell to make sure it works correctly, including the definition and the passing of parameters. To avoid the #CALC! error, add a call to the LAMBDA function to immediately return the result: > =LAMBDA function ([parameter1, parameter2, ...],calculation) (function call) > The follow…

(1) is explicitly spelled out:

> If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.

For (2), I think what they're saying is that if you need to test the function with different arguments, for example, it may be more convenient to reference the cell with the lambda.

Re: Lambda: Turn Excel formulas into custom functions

#106

Post by cpitman »

cdolan wrote: post #13
ryanmarsh wrote: post #8

Does Google sheets let you do anything like this?

Yes it supports JavaScript functions that can be called in the formula bar. However Google sheets is nowhere the install base of excel, so this is a really big deal

As already pointed out, these are not the same. Javascript based custom functions in Google Sheets are actually really error prone, meaning that they will just _randomly fail to execute_. Cells will be stuck with a "loading..." message until you trick Sheets into recalculating the value. In the next couple months, I'll likely be ripping out as many custom functions as I can.

And these are not for particularly complicated functions.

I'd really love to have this feature in Sheets. It would simplify a lot of what my sheets do, and also make it more accessible to a Sheets power user that gets scared off from code.

Re: Lambda: Turn Excel formulas into custom functions

#107

Post by int_19h »

ripley12 wrote: post #82
MegaDeKay wrote: post #70

Earlier quoted context omitted.

The article also says this is valid within the grid: One last thing to note, is that you can call a lambda without naming it. If we hadn’t named the previous formula, and just authored it in the grid, we could call it like this: =LAMBDA(x, x+122)(1) Edit: typo

Yeah, that’s why I added a qualifier. I can’t see that being much use outside of testing/debugging functions, since it would usually be simpler to just write that without the LAMBDA call.

They can also be referenced via the cells. You could argue that cells are themselves named, but those names are really indices into a 2D array, you can have relative references or do offset-based math if needed, and there's indirection (i.e. you can use an arbitrary value as an index to read/write another value). Given all this, an index of a cell containing a lambda is really a lot like a function reference, and can be used in much the same way - passed around etc.

So, these are anonymous functions. The more interesting question is whether they're closures - that is, whether a LAMBDA nested in another LAMBDA can reference the latter's parameters, and how it interacts with LET (https://support.microsoft.com/en-us/office/let-function-3484...).

Re: Lambda: Turn Excel formulas into custom functions

#108

Post by int_19h »

daxfohl wrote: post #72
ripley12 wrote: post #62

Earlier quoted context omitted.

=LAMBDA(...) does return an anonymous function - and then you use the Names Manager to name it. It is a bit weird and confusing because you currently can’t really use the anonymous functions without naming them, but maybe they’re going to relax that restriction eventually?

Yeah, I was expecting you to be able to define a lambda in a cell, so like `B1` could be `=LAMBDA(x => $A1 + x + 1)`. Then you could assign another cell say `D1` to like `$B1($C1)`. Drag-filling B1 down would make say `B2` equal to `=LAMBDA(x => $A2 + x + 2)` or something, so that it would work like people would think Excel Lambdas should. That would be more along the lines of lambdas in the traditional sense of anon…

It allows for exactly that - if B1 defines a lambda, then you can do B1(x) elsewhere.

Re: Lambda: Turn Excel formulas into custom functions

#109

Post by int_19h »

tgv wrote: post #3
rajandatta wrote: post #2

This is extremely significant to simplifying spreadsheets.

Yes, but there is no mention of debugging. In the hands of Excel cowboys, this can become another foot gun.

Between this and LET (https://support.microsoft.com/en-us/office/let-function-3484...), Excel seems to be a full-fledged pure functional language now, with lexical scoping, first-class and high-order functions etc.
Post reply on HN