Lambda: Turn Excel formulas into custom functions • Hacker News Board

Re: Lambda: Turn Excel formulas into custom functions

#21

Post by DavidPeiffer » Sun, Dec 06, 2020, 6:34 PM UTC

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 "helper column" C2=A2+B2, but this adds clutter to the whole spreadsheet.

I have a number of UDF's which lend themselves well to this, such as a triangular distribution calculator. Execution through Lambda should allow the undo stack to continue working (normally ditched by executing VBA) and hopefully give a performance boost.

Re: Lambda: Turn Excel formulas into custom functions

#22

Post by ttul » Sun, Dec 06, 2020, 6:35 PM UTC

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.

Re: Lambda: Turn Excel formulas into custom functions

#23

Post by ttul » Sun, Dec 06, 2020, 6:39 PM UTC

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

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.

Re: Lambda: Turn Excel formulas into custom functions

#24

Post by danso » Sun, Dec 06, 2020, 6:40 PM UTC

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 easily as:

    =REGEXEXTRACT(A2, "[A-Z]{2}")

I'm sure adding regex isn't a trivial thing, but simple pattern extraction seems such an absolutely massive usecase for every everyday user that just I cannot fathom why Microsoft won't support regex. It would make Excel vastly more powerful for its purportedly non-coding users, especially since GSheets has had it for years now. Maybe someone on the product team believes regex feels too much like "code"? As a triple nested function involving subtr, strlen, and array indexing isn't?

[0] https://support.google.com/docs/answer/3098244?hl=en

Re: Lambda: Turn Excel formulas into custom functions

#25

Post by TuringNYC » Sun, Dec 06, 2020, 6:41 PM UTC

ryanmarsh wrote: post #8

Does Google sheets let you do anything like this?

That is literally how Google Sheets started (excel2web allowing lambda functions, hosted externally) before being acquired by Google.

Re: Lambda: Turn Excel formulas into custom functions

#26

Post by CrazyCatDog » Sun, Dec 06, 2020, 6:42 PM UTC

Here’s hoping that the capability will be available for online excel (which otherwise pales in comparison to the pc client version)!

Re: Lambda: Turn Excel formulas into custom functions

#27

Post by sheetjs » Sun, Dec 06, 2020, 6:45 PM UTC

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…

For that type of pattern, you can also use the new LET function:

    LET(myval, A2+B2, IF(myval>10, myval, 10))

https://support.microsoft.com/en-us/office/let-function-3484...

Re: Lambda: Turn Excel formulas into custom functions

#28

Post by an_opabinia » Sun, Dec 06, 2020, 6:46 PM UTC

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.

Re: Lambda: Turn Excel formulas into custom functions

#29

Post by rahimnathwani » Sun, Dec 06, 2020, 6:49 PM UTC

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.

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)

Re: Lambda: Turn Excel formulas into custom functions

#30

Post by DavidPeiffer » Sun, Dec 06, 2020, 6:52 PM UTC

sheetjs wrote: post #27

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…

For that type of pattern, you can also use the new LET function: LET(myval, A2+B2, IF(myval>10, myval, 10)) https://support.microsoft.com/en-us/office/let-function-3484...

Thanks! Haven't sunk my teeth into the new round of features yet, but have found great utility from the last batch (unique, filter, sort) and the prior batch.