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

51–60 of 140 posts

Re: Lambda: Turn Excel formulas into custom functions

#51

Post by djeiasbsbo »

croes wrote: post #16

Yeah, let's make the moloch even more unmaintainable. Does still have the leap year error to be compatible with Lotus?

What bothers me greatly about Excel is something that native English speakers perhaps have never had to deal with.

The "keywords" are language specific/dependent. Everytime I have to google how to do a specific thing in Excel I then have to spend 5 times longer to translate the instructions into my language.

It is a problem because in a locked down corporate environment, a worker/user cannot easily change the language.

Re: Lambda: Turn Excel formulas into custom functions

#52

Post by kaishiro »

craftinator wrote: post #45
cm2187 wrote: post #42

Earlier quoted context omitted.

Given the number of developers who spit on the floor as soon as they hear “regex” (including me), I am not sure regex will bring much to regular users. If it is hard to developers, it is impossible to regular users. (And if you really want regex, there are hundreds of results on google on how to build your own VBA UDF to get it).

> Given the number of developers who spit on the floor as soon as they hear “regex” > If it is hard to developers, it is impossible to regular users. What are you talking about? Regular expressions are used everywhere. On the backend it's text parsing, on the front end it's input validation. I have never written a complete application without using it. You're also the first person I've heard grumble about them. I get…

Just to throw another anecdote on top of yours, I've never worked in a shop where they weren't grumbled about from time to time - so I personally don't find the parent's claim to be baseless at all.

Many developers struggle with the "language" of regex - and no matter how many times I "learn it", it doesn't change the fact that I have to pull up references every time I'm building out an expression.

Grandparents post re: Excel had me curious actually - because I (personally) find the use of Left, Mid, Right, etc generally far more logical and readable than trying to parse a regex string.

Re: Lambda: Turn Excel formulas into custom functions

#53

Post by rahimnathwani »

will_pseudonym wrote: post #40
rahimnathwani wrote: post #29

Earlier quoted context omitted.

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 tha…

"to avoid repeating it I'd put that inside another cell"

100%! I like to do that in other cases as well, just to keep formulae simple enough that someone else can easily audit the whole spreadsheet.

I'd rather have 5 extra columns in a calculation, then have a huge formula in a single column. This habit is so strong that I often do the same thing with Pandas: adding extra columns to a dataframe for intermediate calculations, when it would be better to write a larger function and .apply() it all at once.

Re: Lambda: Turn Excel formulas into custom functions

#54

Post by bencollier49 »

A lot of this sort of functionality which is appearing at the moment from MS was built into a fantastic spreadsheet called ResolverOne which was released back in around 2008 by a company in the UK called Resolver Systems. It was based on IronPython and allowed an entire spreadsheet to be exported as a Python package.

The company never seemed to gain traction, and unfortunately the open-source tool released which was based on ResolverOne had none of the power or elegance of the original.

I'd be interested to know if MS had consulted Giles Thomas from R.S. prior to this - it's certainly giving me a bit of deja vu.

Re: Lambda: Turn Excel formulas into custom functions

#55

Post by daxfohl »

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.

Re: Lambda: Turn Excel formulas into custom functions

#56

Post by will_pseudonym »

rahimnathwani wrote: post #53
will_pseudonym wrote: post #40

Earlier quoted context omitted.

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 tha…

"to avoid repeating it I'd put that inside another cell" 100%! I like to do that in other cases as well, just to keep formulae simple enough that someone else can easily audit the whole spreadsheet. I'd rather have 5 extra columns in a calculation, then have a huge formula in a single column. This habit is so strong that I often do the same thing with Pandas: adding extra columns to a dataframe for intermediate calcu…

Yep. Exactly the same reason we break up procedures and functions into smaller units in procedural code, to be able to reason about individual pieces, and understand each step.

I've never used Pandas, but I imagine I'd have the exact same instincts to break calculations up as you do, since Excel was my "first programming language" that I was first exposed to in the 4th grade, haha. I certainly didn't learn much advanced stuff at that point, mostly because of the time period and being in a rural Midwest area there weren't a lot of programmers around to learn from and the internet was rather different in the mid 90's. :) But Excel planted the seed of programming in my mind, even though I didn't know what "programming" was.

Re: Lambda: Turn Excel formulas into custom functions

#57

Post by Spooky23 »

cm2187 wrote: post #42
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.

Given the number of developers who spit on the floor as soon as they hear “regex” (including me), I am not sure regex will bring much to regular users. If it is hard to developers, it is impossible to regular users. (And if you really want regex, there are hundreds of results on google on how to build your own VBA UDF to get it).

Its much easier than the legacy garbage that many office users are stuck with.

My guess in my organization (huge, 100k people) we probably have ~50 FTEs who do stupid work solely because of this feature not existing.

Re: Lambda: Turn Excel formulas into custom functions

#58

Post by cm2187 »

somurzakov wrote: post #49
cm2187 wrote: post #42

Earlier quoted context omitted.

Given the number of developers who spit on the floor as soon as they hear “regex” (including me), I am not sure regex will bring much to regular users. If it is hard to developers, it is impossible to regular users. (And if you really want regex, there are hundreds of results on google on how to build your own VBA UDF to get it).

same developers don't like SQL because it's complicated (or rather they don't understand it) and end up using an ORM mess. yes still SQL is everywhere

I was under the impression the primary use for ORM isn't because SQL is complicated (it really is not) but rather because it is embedded in strings and therefore un-testable and opaque to the compiler.

And the complexity of SQL to someone who already codes is marginal. Here in Excel we are talking about the complexity to someone with no coding experience.

Re: Lambda: Turn Excel formulas into custom functions

#59

Post by jtsuken »

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 dedicated file format, which will only open after showing 15 warning messages.

....

Microsoft: _Hey, we have this new feature. It's called Lambda. You can execute any code you like and use it as functions in your spreadsheets._

Re: Lambda: Turn Excel formulas into custom functions

#60

Post by vic20forever »

djeiasbsbo wrote: post #51
croes wrote: post #16

Yeah, let's make the moloch even more unmaintainable. Does still have the leap year error to be compatible with Lotus?

What bothers me greatly about Excel is something that native English speakers perhaps have never had to deal with. The "keywords" are language specific/dependent. Everytime I have to google how to do a specific thing in Excel I then have to spend 5 times longer to translate the instructions into my language. It is a problem because in a locked down corporate environment, a worker/user cannot easily change the languag…

I think the Functions Translator[1][2] add-in might be worth a look to you. Note: I haven't used it myself, and it's a Microsoft Garage project, so there's no guarantee of support or maintenance.

From the description:

Functions Translator helps people use a localized version of Excel by helping translate from the US Excel function names, or research how to create a solution on the web with predominately English content.

Easily find the equivalent localized functions and formulas in any of the supported 15 languages. Functions Translator will automatically configure the language settings to US and the Localized version, and people can provide feedback on the translation of functions if it is not what they expected.

[1] https://www.microsoft.com/en-us/garage/blog/2018/03/new-gara... [2] https://www.microsoft.com/en-us/garage/profiles/functions-tr...

Post reply on HN