Musing: Rubbish Timing - MMMUK1/LambdaForRealEstate GitHub Wiki

It is very unfortunate that the CALC team at Microsoft rewrote the Excel calculation engine and introduced it to the general public (and while pushing a new subscription model) during one of the most unprecedented business lockdown periods in human history.

While we have been trying to get on with life, the effective overhaul of Microsoft Excel appears to be staggering. Not only were dynamic arrays introduced, but also Lambda formulae.

Dynamic Arrays

The dynamic arrays make it easier for millions of users to use array formulae that "overspill" into as many cells as necessary to address the results from an array. Array logic existed for a long time, but so few -even bright users- know how to use them. 'Power Users' used to be the select few who commanded the 'Super-charged Array'. Listening to Andy Jones and Simon Peyton Jones confirms just how eccentric super-charged arrays and squiggly brackets {} inserted with a mysterious Ctrl + Shift + Enter meant.

Users were always unaware that array formulae had been part of the core calculation engine of Excel for a long time:

This 2022 little exchange shows how array formulae calculations are little-known by quite sophisticated users.

It is difficult to assess the potential dynamic arrays and the thousands of combinations from potential new formulae. This video is excellent by Leila Gharani. But in terms of timing and delivery to the market of this Microsoft upgrade, the announcements through social media show potential to users but with a massive caveat: it's not available to general users to start using, experimenting and learning. How much has been lost in awareness during the launch of these initiatives?

Here is a simple example showing the time saved. I think this is an understatement. The complex formulae can only be written by a super-fluent user or a demo user already prepared. For the average offce365 users, the exercise could easily take up a morning:

Lambda Functions

And as companies are struggling to figure out how to get people back into the office or into an agreed hybrid routine, the Excel team has released Lambda Functions. This was only issued for general release in 2023. It is yet another seismic change for how models can be constructed. Lambda allows Excel's audience of millions of users to define their own functions, and start referring to calculations with accessible language.

These two changes are enough to encourage any 2000s modeller to revisit their way of building a financial model.

Lambdas directly challenge the common office water-cooler chat that 'Named Ranges' produce confusion. Difficult to reference, audit, etc.