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

121–130 of 140 posts

Re: Lambda: Turn Excel formulas into custom functions

#121

Post by fomine3 »

Closi wrote: post #120
fomine3 wrote: post #117

Earlier quoted context omitted.

Yes it's good but "revolutionize" is misleading.

I don’t think it’s misleading, I mean everyone’s definition of “revolutionary” is different, but let’s put it into perspective: this is adding functions and recursion to one of the worlds most popular data analysis languages. I think most people on this site know how powerful the concepts of functions and recursion can be!

Functions and recursion is already there, but only with VBA. I'll say revolutionize if VBA wasn't exist.

Re: Lambda: Turn Excel formulas into custom functions

#122

Post by Grustaf »

infogulch wrote: post #31

Wow this is cool! I felt that something like this could be transformative to excel for some time. 2014: https://news.ycombinator.com/item?id=8116224 > spreadsheets might be an interesting programming environment if you were restricted to the native functionality with a small addition. Namely, add a new value type: "anonymous function,"... 2019: https://news.ycombinator.com/item?id=21356824 > Excel needs exactly one t…

Totally agree, we even applied to YC 5 years ago with an excel replacement specifically because of this limitation.

Re: Lambda: Turn Excel formulas into custom functions

#123

Post by contravariant »

ripley12 wrote: post #62
daxfohl wrote: post #55

Lambda is kind of a weird name. Lambda functions are (traditionally) anonymous and close over local variables. These are just UDFs in Excel syntax, which seems like nothing super exciting (surprised it didn't exist already). I'd be curious what an actual lambda thing would look like in Excel.

=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?

Can you do LAMBDA(...)(x)? That would make it an anonymous function.

Of course the real fun starts when you can do:

    =LAMBDA(f, LAMBDA(x, f(x(x)))(LAMBDA(x, f(x(x)))))

Re: Lambda: Turn Excel formulas into custom functions

#124

Post by darwingr »

Finally! This is something I've been wanting for years. There's always been this midpoint of complexity when making spreadsheets that coworkers will use. Including macros scares them away but using long, un-named formulas does not even though it is much less clear what it's doing. The full power of a macro was not needed, only a label and some arguments to lower the cognitive load.

Re: Lambda: Turn Excel formulas into custom functions

#125

Post by darwingr »

darwingr wrote: post #124

Finally! This is something I've been wanting for years. There's always been this midpoint of complexity when making spreadsheets that coworkers will use. Including macros scares them away but using long, un-named formulas does not even though it is much less clear what it's doing. The full power of a macro was not needed, only a label and some arguments to lower the cognitive load.

My prediction of the next most obvious built-in function after lambda and let: `=register(name, reference_or_formula, [scope])` that registers a defined name for the spreadsheet with code rather than the "Define Name" box.

Re: Lambda: Turn Excel formulas into custom functions

#126

Post by darwingr »

danso wrote: post #24

This is completely orthogonal to the full scope of LAMBDA, But it did make me laugh to see that the first example involved a classic and painful usecase (extracting 2 letters amid a string of numbers), which in Excel has to be written as: =LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1) But if only Excel would support regular expressions like Google Sheets [0], could be done as e…

I found myself going back to this the other day. I learned these long left/right formulas before I learned to program. Rewriting them really made me miss regex.

Re: Lambda: Turn Excel formulas into custom functions

#127

Post by listenallyall »

pearjuice wrote: post #118
nullsense wrote: post #101

Earlier quoted context omitted.

I like how Lambda means very different things between AWS and Microsoft. "Let's just create a Lambda for this" Ok... But which one?

I don't want to be cynical, but they could have done this with reason. They could have also called it functions or methods. But chose to disrupt search results for a competitor.

Your anti-M$FT bias is showing. AMZN struck first... Microsoft was using the term Lambda for a prominent feature in its flagship language (C#) before AWS ever existed.

Here's a Stack question dated 2008: https://stackoverflow.com/questions/167343/c-sharp-lambda-ex...

Re: Lambda: Turn Excel formulas into custom functions

#128

Post by ripley12 »

contravariant wrote: post #123
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?

Can you do LAMBDA(...)(x)? That would make it an anonymous function. Of course the real fun starts when you can do: =LAMBDA(f, LAMBDA(x, f(x(x)))(LAMBDA(x, f(x(x)))))

You can, but you can't (currently?) do =A1(x) where A1 is a LAMBDA function. I think LAMBDA(...)(x) is only useful for testing/development, but maybe I'm missing some good use cases.

Re: Lambda: Turn Excel formulas into custom functions

#129

Post by oezi »

darwingr wrote: post #125
darwingr wrote: post #124

Finally! This is something I've been wanting for years. There's always been this midpoint of complexity when making spreadsheets that coworkers will use. Including macros scares them away but using long, un-named formulas does not even though it is much less clear what it's doing. The full power of a macro was not needed, only a label and some arguments to lower the cognitive load.

My prediction of the next most obvious built-in function after lambda and let: `=register(name, reference_or_formula, [scope])` that registers a defined name for the spreadsheet with code rather than the "Define Name" box.

Absolutely right, the use of the name manager makes this much less usable. Instead of being able to copy lambdas from cells, you now also have to set their name manually.

Re: Lambda: Turn Excel formulas into custom functions

#130

Post by pearjuice »

listenallyall wrote: post #127
pearjuice wrote: post #118

Earlier quoted context omitted.

I don't want to be cynical, but they could have done this with reason. They could have also called it functions or methods. But chose to disrupt search results for a competitor.

Your anti-M$FT bias is showing. AMZN struck first... Microsoft was using the term Lambda for a prominent feature in its flagship language (C#) before AWS ever existed. Here's a Stack question dated 2008: https://stackoverflow.com/questions/167343/c-sharp-lambda-ex...

Eye for an eye then. :-) For someone claiming I'm anti-MS, funny you use the mocking M$FT abbreviation.
Post reply on HN