Lambdas as values in Excel | Hacker News Reader

Lambdas as values in Excel

techcommunity.microsoft.com

126 pointsoccamrazor60 comments

I gotta admit, Microsoft is pushing out some cool features that are widely accessible. I've been enjoying the Github Actions feature, Teams is actually getting my organization to stop using Sharepoint, VSCode is great, and now Excel is getting some nice function upgrades. We've come a long way from the 90s embrace-extend-extinguish MS business strategy (or are at least enjoying a period of embrace+extend).

Agreed - Teams causes me some pain, but it is something we are getting used to now.

Let's also not forget about the current state of the .NET ecosystem. C#9+ on top of .NET 5+ is an amazing developer experience.

Being able to contribute to the framework via any authenticated GitHub account is nice too. I find myself almost accidentally participating in Microsoft's issue threads simply because it is so easy to do so.

Disagree about the progress; See Winget.

Classic bungling.

Can you elaborate how on why you think Winget is being bungled?

Microsoft has 181,000 full-time employees. There's probably quite a few teams in that company that never have anything to do with each other.

It’s definitely not as good as Chocolatey, but they realized there’s a need, and it’s a start.

I think Teams creates a SharePoint site for each Team that is created. Kinda funny really.

> You could author one function for each value which checks the condition but this is error prone and requires a lot of duplication for something pretty basic. For reference, the duplicated formulas might look like this (with one formula for each value):

  =AND(A2>50, A2<80)
  =AND(A3>50, A2<80)
  ...

Appropriately enough, the second line contains a copy-and-paste error!

Realistically you'd just drag the fill handle rather than using copy/paste/edit, so you'd be unlikely to find this bug in a real worksheet.

http://www.eusprig.org/basic-research.htm

> There is a very extensive research base on the risks of using spreadsheets within business see [Panko, 2000] [Panko & Ordway, 2005] [Powell, Baker & Lawson, 2007]. Much of the research has been coordinated and progressed by EuSpRIG [Chadwick, 2003]. Further significant work improving the end user approach to software has been undertaken by the EUSES consortium [EUSES, 2009].

> The main known risks of spreadsheets include:

> a) Human Error – To err is human, hence the majority (>90%) of spreadsheets contain errors. Because spreadsheets are rarely tested [Panko, 2006] [Pryor, 2004] these errors remain. Recent research has shown that about 50% of spreadsheet models used operationally in large businesses have material defects [Powell, Baker, Lawson, 2007] [Croll, 2008]. Approximately 50% of executives recently surveyed had encountered spreadsheet related problems up to and including staff dismissal [Caulkins, Morrison & Weideman, 2007].

But that's a problem in itself that's always bothered me: dragging works slightly different than copy/paste works different than cut/paste. And the different uses are useful on their own, but the inconsistency makes understanding how to manipulate formulas in simple ways overly confusing, especially when first learning the behavior.

The ability copy-and-paste-absolute and to cut-and-paste-relative should be right there next to cut copy and paste when you right click, and should have been there decades ago.

it's insane how fast one can destroy the data in an excel file simply by misunderstanding copy/paste or drag/drop functions, e.g. while you (probably, at least how my excel is configured) can filter your table and then drag the value of one cell down to other cells and the ones that are filtered out are not affected. However, when you think that doing the same for changing the format of the cells (e.g. the background color), you've thought wrong - even the cells that are filtered out are affected.

The biggest challenge with Excel features remains versioning: functions are tied to a specific Excel releases, and it takes years before enough people run a version supporting them.

I wish they made these an add-on.

Until then, I expect to hear my fancy files are "broken."

Quite a lot of organisations are now running Office 365. It gets updated monthly with new features as well as security updates.

Even more conservative organisations will often be running Office 365 with a lag, still getting features after ~6 months.

If you need 100% compatibility that will always take a while, and I'm sure some big enterprises are on old style office. But it seems like the vast majority of small/medium business is on the Office 365 juggernaut now.

True. And a few large organisations are taking those baby steps too. Some of these super-conservative organisations started using subscription software with Adobe’s creative cloud, and got familiar with monthly update schedules thanks to Windows and RHEL patching. They are slowly coming around to accepting that Word and Excel can be deployed and patched just like the OS, and paid for like Photoshop, or used online along with things like Teams and OneDrive inside the organisation.

> The biggest challenge with Excel features remains versioning: functions are tied to a specific Excel releases, and it takes years before enough people run a version supporting them.

Also seems difficult to maintain if these are cell-bound, and both the cell edition interface and the Name Manager interface were an incredibly poor edition experience from what I remember, but maybe that improved since?

There is a new and better Name Manager in the Mac version.

My Org has Office 365 but the Office installed on our machines are controlled by the Corp. Until recently we had the V2008 release from July 2020, just a month ago we got updated to V2102 from Feb 2021.

This is really the biggest problem with Excel portability: feature fragmentation.

While these new features seem exciting, I don't expect to use them anytime soon if I'm planning on sharing my work with others because it's not that easy tracking down which features are tied to specific Excel releases.

O365 alleviates this.

I do not send spreadsheets anymore and just send a onedrive view only link to whoever needs to see it.

On the chance they need to manipulate I share the edit link (which has version control) and my stress levels are minimal.

Excel now has MAP, REDUCE and other helper functions for functional programming.

And an F# like language, Power Query.

I haven’t heard of this before. Do you happen to have any more details?

EDIT: Found it: https://docs.microsoft.com/en-us/power-query/. But it doesn’t look particularly F#-like — Power Query seems more focused around, well, querying data.

I think parent comment was referring to the M-code [0]

[0](https://www.howtoexcel.org/power-query/m-code/)

Yep that was it.

Ah, thanks!

I really like the M Language and Power Query (PQ) makes it really easy and convenient to build data mashups. You can even use it as a SQL query builder by viewing the Native Query code and then copying that.

The main issue I'm facing is how to distribute the library of PQ/M functions I've developed?

Currently I have a central library workbook and then have to copy code from there. Problem is that updates and bugfixes don't flow downstream.

I really hope Microsoft creates a solution for this. Would love to hear about some approaches for handling this in the meantime.

There are a few third party libraries/projects that are useful for that situation. Libpq[1] is one, and this[2] project is another. The readme for that second one includes links to several other M/PQ related projects.

[1] https://libpq.ml/

[2] https://github.com/KiaraGrouwstra/pquery

for a moment I thought it would now be possible to call a serverless lambda function with excel being the runtime.

I've been through scenarios where sophisticated calculations were done in excel by domain experts and then it was necessary to translate them into code by developers, so having an excel-as-a-service would be great.

If you're in the Office 365 ecosystem, you can do just that (in a sense).

The MS Graph API has a workbook endpoint[1] that lets you do nifty stuff with Excel workbooks hosted in OneDrive or Sharepoint. Pair that with non-persistent sessions[2], and you basically get an Excel workbook as a serverless runtime environment.

If you structure the workbook with this type of use in mind, it works really handily. Create a non-persistent session, updated named items with the input values, run an explicit calculate on the workbook, and grab whatever result you're after (a table, named range, pivottable, chart graphics, etc), close the workbook session (or let it expire).

If you need to keep the data around for historical reasons, you can follow the same process but copy the template workbook and create a persistent session against the copy, so the inputs/outputs are saved.

You can also leverage Power Automate[3] (Microsoft's version of Zapier) to create an actual serverless function for your specific workflow that can accept your inputs, call the appropriate Graph endpoints for those steps, and return the output. Although the licensing gets funky, most people with an Office 365 license have some level of usage included already.

It's definitely not a solution architecture you want to use for anything mission-critical or high-volume, but it's super handy for anything that's going to be Excel based anyway and you'd like to minimize the surface area for human error during the process. Also nifty for situations where a process/scenario/PoC is still being matured and developed in Excel, but you need to use it for production use cases. Create a stable input/output interface with a Power Automate workflow (or other serverless interface) that consumers can work against, then continue your Excel-based process development without disrupting them. At some point when it's stable/mature, port it over to code that maintains that same input/output structure and cut over the downstream consumers to the new endpoint.

[1] https://docs.microsoft.com/en-us/graph/api/resources/excel

[2] https://docs.microsoft.com/en-us/graph/api/workbook-createse...

[3] https://powerautomate.microsoft.com/en-us/

I once had a client who wanted to make a web app out of a spreadsheet that relied on Excel's optimization functionality. I wonder if that would work? They might still be interested in it.

> I wonder if that would work?

I can't say for sure, but likely not. It sounds like your client may have been relying on one of the optimization add-ons that are automatically installed with Excel[1][2], rather than actual Excel features. The company that makes those add-ins has developed new versions that work with Excel Online, but add-ins in Excel Online execute in the local browser context. So I don't think they're loaded/usable when you create a headless workbook session via the Graph API (although I've never actually tried to do that, so could be wrong).

That said, Frontline Systems (the company that makes those Excel add-ins) does have a web API[3]. The optimization models there are a superset of the capabilities in the Excel add-ins, so your client's Excel optimization model could likely be ported over to that pretty easily.

[1] https://support.microsoft.com/en-us/office/use-the-analysis-...

[2] https://support.microsoft.com/en-us/office/define-and-solve-...

[3] https://rason.com/

Easy, via OLE Automation API.

The problem is handling Excel instances.

Thank you. This approach sounds really handy for some things in my environment.

same here ! This would have been the most exciting feature ever!

Build a backend for Excel

I used to have a start-up that built a streaming data backend for Excel. You can see an animated gif of it here: https://www.reddit.com/r/dataisbeautiful/comments/8ddmui/rea... the IP was acquired, but I reckon there's still ideas to be iterated on here.

this is brilliant! how did u integrate with excel ?

I'm very excited about Lambda functions. Create your own set of clean logical functions on top of quirkier default functions. Optionally with namespacing (e.g. "m." for math functions / "t." for text functions). Use recursion in the function definition, without external programming.

Anyone know if this is possible in Google sheets?

Also, one thing I always find challenging when applying formulas to a column is applying it to the entire column. Either you remember to copy and paste it when you add a new row, or you drag it down to row 10000 and then have a bunch of formula errors because your data doesn’t have that many rows. Or add if(empty()) prefixes which makes them much harder to read. Any better way around that?

So new functions in the spreadsheets instead writing those functions in VBA? If VBA was too hard, Microsoft thinks that functional programming will go down easier?

This lets you more easily reuse excel formulas (within a spreadsheet) without enabling VBA or extension modules, which is a big boon from a security standpoint.

Given the existing wonkiness of the excel formula programming model, anyone who already had a grasp of actual tables and array formulas (which to be fair, is already a small subset of excel formula users) will be able to pick up these pretty easily.

Why not? VBA is confusing and outdated. If they ported macros to JS I think a lot more people would be comfortable with it. In the same vein being able to stay in Excel's formula language instead of teaching yourself VBA seems a lot more tenable for formula gods who haven't quite figured out imperative programming outside of Excel yet.

Js is already supposed to be the preferred way to make addins now since it's the only way to make them work in both the desktop and web version. There's just no built in editor.

VBA was "too powerful" in 2 ways. Primarily it was a security risk, it had way too much capability beyond implementing custom functions on spreadsheet data. Secondarily it was too decoupled from the spreadsheet, an programming environment bolted on top of the spreadsheet, while lambdas are much smaller in scope targeted specifically at just reusable spreadsheet functions.

This is really neat.

As someone using Excel to do slot game mathematics calculations, sometimes the excel formulas used are very long, convoluted, and hard to grok when coming back to them. Many cells often exist as calculation cells only, used for intermediate steps which leads to even more logic complexity.

I’m excited to experiment with these to try and simplify some of the long, previously-deemed-necessary calculation methods.

Your move, Google Sheets.

Now, if they’d just do that for T-SQL I’d be very happy!

They could have used some notation to separate the arguments from the function body. The arrow => would be a good candidate.

It would be an awful option, think of the diversity of excel users. Millions of them would struggle to understand that here, this time, that does not represent “equal or greater”, and it would be an endless source of confusion.

More importantly, regularity is nice, and Excel already has no syntactic delineation between functions and special forms e.g. IF will only evaluate the expression matching the condition (though AND and OR are somewhat unexpectedly eager).

You're right of course, but I secretly dream they would just change the syntax to straight up s-expressions.

Greenspun's tenth rule at work?

Y combinator possible?

Meanwhile hitting Ctrl-A in any text field doesn't do anything. Single quotes don't work in formulas and the list goes on...

I feel like this feature was built because the engineering team spends too much time reading functional programming/ML zeal here on hacker news instead of a real user/customer desire. The irony is that this feature will almost certainly be met with derision/scorn from the CS crowd and clueless shrugs from excel users.

> this feature will almost certainly be met with derision/scorn from the CS crowd

Why?

SPJ was the driving force behind the feature: https://www.microsoft.com/en-us/research/podcast/advancing-e..., so I don't think that is true.

> clueless shrugs from excel users.

https://www.youtube.com/watch?v=Rm4y5UqauRw

https://www.youtube.com/watch?v=L7s6Dni1dG8

Demonstrably false.

What has instead happened, I shit you not, is we have "Excel influencers" teaching people recursion "without code!" (aka without VBScript).

I cried and I laughed when I first saw, it was a watershed moment in CS education.

https://www.youtube.com/watch?v=0yKf8TrLUOw

Congratulations, Microsoft. Welcome to 1958.

Reply on news.ycombinator.com