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

31–40 of 140 posts

Re: Lambda: Turn Excel formulas into custom functions

#31

Post by infogulch »

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 thing to blow open the doors on productive programming: a new "function" data type. Since it's just a data type, you put it in a cell just like any other data type. Have some way to call it, like `A1(arg1, arg2)` or something. Now you can leverage the full capabilities of Excel to manage it, name it (named ranges), etc just like other data. ...

From the same thread:

> VBA is just an escape-hatch to a 'real' programming environment; my claim is that excel sheets & formulas alone could be a 'real' programming environment in its own right, no escape hatches necessary.

I wonder if my comments inspired someone. :3

Re: Lambda: Turn Excel formulas into custom functions

#32

Post by [deleted] »

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…

[deleted]

Re: Lambda: Turn Excel formulas into custom functions

#33

Post by oger »

While I welcome this development I must note from long-standing experience that using any type of more advanced functionality in Excel has always come back to bite me. The reason is exchange of workbooks with less skilled individuals. Or cross platform incompatibilities like URLENCODE not working on MacOS since many years now. And many use cases that once required Excel can now be solved by other tools with better documentation. The nature of Excel is in-auditability, intransparency and error proneness. Trust me - I‘ve been using it for too many years now.

Re: Lambda: Turn Excel formulas into custom functions

#34

Post by cdolan »

ttul wrote: post #23
cdolan wrote: post #13

Earlier quoted context omitted.

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

Is it not, though? Google Sheets is available to all GSuite customers. GSuite is the most popular email hosting platform in the world, with roughly 18% market share (Microsoft 365 trails not significantly far behind). I think Google Sheets has a pretty solid user base.

GSuite and Office 365 subscription base, while somewhat correlated to, is not equal to the usage of Excel vs Google Sheets.

Highly anecdotal, but there are far more complex business processes still running in Excel that are not going to be translated over to Google sheets, and they are all offline behind a network firewall.

Those types of sheets will really benefit from this improvement.

Now if they just supported python instead of VBA for scripting!

Re: Lambda: Turn Excel formulas into custom functions

#35

Post by cdolan »

ttul wrote: post #22

The number one reason that I use Google Sheets instead of Excel is the availability of the REGEXMATCH and REGEXEXTRACT functions. No human should forced to use a ridiculous combination of LEFT, RIGHT, and MID to extract things from a string. I just can’t fathom why Excel hasn’t yet introduced regular expressions.

Agreed. I would love to leverage REGEX!

Excel formulas have long been too hard to comprehend if you were not the original author... and even if you did author it, 4 weeks later you won’t remember how it worked without a half hour of review!

Re: Lambda: Turn Excel formulas into custom functions

#36

Post by chaz6 »

ttul wrote: post #22

The number one reason that I use Google Sheets instead of Excel is the availability of the REGEXMATCH and REGEXEXTRACT functions. No human should forced to use a ridiculous combination of LEFT, RIGHT, and MID to extract things from a string. I just can’t fathom why Excel hasn’t yet introduced regular expressions.

Whilst not native, you can emulate these with a user defined function (UDF).

Re: Lambda: Turn Excel formulas into custom functions

#37

Post by DavidPeiffer »

an_opabinia wrote: post #28
DavidPeiffer wrote: post #21

As a frequent user of Excel, I very much welcome this. Being able to build a function library beyond User Defined (UDF, VBA functions that can be called from a formula) and a Personal.xlsb file (VBA that's available in any open Excel file) One common use case this helps takes the general form "If A2+B2 > 10,then A2+B2, else 10". "A2+B2" needs to be updated, it has to be updated twice. Alternately, you could have a "h…

Isn’t that just =MAX(A2+B2,10)? Anyway I know what you mean. It seems fine to make a sheet that behaves as variables. Ie a sheet that is two columns, the comment for an intermediate and its values. Reference it elsewhere. Re-engineering the whole Excel as a website, with its attendant sandboxing, seems to be the future.

Yes, that was a max function. I was trying to keep it simple for people not familiar with the syntax.

I commonly use a lookup function in that context (vlookup, xlookup, or index/match).

Re: Lambda: Turn Excel formulas into custom functions

#38

Post by indymike »

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.

Re: Lambda: Turn Excel formulas into custom functions

#39

Post by maxerickson »

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…

The regex is perhaps still clearer (and in general more powerful), but that Excel method is a catastrophe, not the way it has to be done.

    =MID(B1, FIND("-",B1)+1, 2)

Re: Lambda: Turn Excel formulas into custom functions

#40

Post by will_pseudonym »

rahimnathwani wrote: post #29
an_opabinia wrote: post #28

Earlier quoted context omitted.

Isn’t that just =MAX(A2+B2,10)? Anyway I know what you mean. It seems fine to make a sheet that behaves as variables. Ie a sheet that is two columns, the comment for an intermediate and its values. Reference it elsewhere. Re-engineering the whole Excel as a website, with its attendant sandboxing, seems to be the future.

This reminds me of the IFERROR(). Without IFERROR, a common pattern would be IF(ISERROR(A2+B2),0,A2+B2). With IFERROR, you can just do IFERROR(A2+B2,0)

And IFERROR() wasn't added until Excel 2007. The other way to make that operation less unwieldy if the expression was complicated, to avoid repeating it I'd put that inside another cell, and that way it would be =IF(ISERROR(C2),0,C2), but IFERROR is a much better solution.

Excel is such a good tool in many ways, and such a bad tool in many ways. Really experienced power users can follow Excel formulas much easier than blocks of imperative code. But the more complicated it gets, the harder it can be to follow it all. But that also goes the same for software, too. I do think that there is something powerful about the "debugging" you always have turned on in Excel, in that you always know what value a formula has produced, even after it has run. And you can (usually) easily see at what point an error started in your calculations.

For the programmers out there who aren't fans of Excel, or aren't super familiar with it, if you haven't seen "You Suck at Excel with Joel Spolsky" [0] you might be pretty amazed at what you can do with Excel at an intermediate/advanced level.

[0] https://www.youtube.com/watch?v=0nbkaYsR94c

Post reply on HN