Lambda() downsides need to be fixed

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

The new Lambda() feature in Excel 365 is great, in theory.  But when you try using it the problems quickly become obvious.

Lambda() itself is fine. It’s a welcome addition which more advanced users will make Excel do amazing things or simplify complex formulas.  See Lambda – strange name for a really good extra in Excel

We’ve been trying out Lambda and it’s impressive but a right pain the ‘expletive’ to work with.

Lambda has been grafted onto a very limited interface (Integrated Development Environment or IDE) designed for simple formulas.  That interface hasn’t been updated to meet the needs of modern Excel users – let alone new features like Let() and Lambda().

The approved method of using Lambda is via Define Name.  A Lambda() function is given a name and set in Formulas | Define Name

That means there’s a single short line to edit the Lambda() formula.

image 128 473x221 - Lambda() downsides need to be fixed

It gets worse. Pressing any arrow key on the ‘Refers to’ line inserts a cell reference instead of moving along the existing formula.  It’s a field designed for a simple cell reference, not a complex formula.

In Microsoft’s explanations of Lambda() they show neat and tidy multi-line formulas like this:

=LAMBDA(city1, city2, LET(
     lat_1, city1.Latitude,
     lon_1, city1.Longitude,
     lat_2, city2.Latitude,
     lon_2, city2.Longitude,
))

Paste that into Excel and this is all you’ll see. The last line only and no way to see, let alone edit the formula.

image 129 - Lambda() downsides need to be fixed

If the function works, be happy.  Bad luck if it doesn’t, because you’ll be trying to debug a mostly invisible function.

What Excel badly needs is a proper IDE for formulas that’s fit for the 21st Century.  An extension of what’s available for VBA users.

Presumably the Excel team knows about the problems with Lambda and longer formulas.  Hopefully there’s a group working on a solution – not a partial fix – a real IDE solution that Excel users can work with for years to come.

Workaround

There is a partial workaround.  It’s not very good but better than Define Name.

A Lambda() function can live in an Excel cell with the parameters after it.  Here’s a Pythagorean example …

=LAMBDA(x,y, SQRT(x^2+y^2)) (3,4)

The two parameters are in the brackets after the formula.

In Excel, the Formula bar can be expanded to show the whole formula.

image 130 - Lambda() downsides need to be fixed

At the moment, Lambda() doesn’t get the nice color coding that regular formulas do.  Hopefully that will be fixed before public release.

Tip:  use Alt+Enter to add line breaks within a formula.

Lambda – strange name for a really good extra in Excel
A better Excel Calendar Creator for Office Watch readers
Watch Window finally coming to Excel for Mac

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address