Excel's Lambda function is a method of a taking a formula that you would normally enter into a cell and giving it a name. You can then use that name wherever you like instead of repeating the formula.
This looks as if it will be hugely useful and I can see it simplifying the maintenance of spreadsheets everywhere. It will potentially mean that some spreadsheets can be re-written without using VBA at all, a great benefit for those organisations where security prohibits the use of VBA code. It comes however with the downside that the use of the LAMBDA() function as implemented will make spreadsheets more difficult to follow and debug – but more of that anon.
But it isn’t really a lambda though.
Lambda functions are normally described as anonymous
functions and would be used without naming.
The purpose of the Excel LAMBA() function is, somewhat different, and serves
to encapsulate an Excel formula and deliver it as a named function for multiple
use in cells.
Let us give an example. I’ll use Python to illustrate:
I could write a simple function as follows:
def square(x): return x**2 (1)
and use it as follows:
map (square,[0,1,2,3]) (2)
This is pretty standard approach to a function and you can
see how something similar can be done in Excel.
In Python however if I didn’t want a function which could be used
elsewhere I could write a lambda statement thus:
map(lambda x: x**2,[0,1,2,3]) (3)
This arguably is similar to the new Excel LET() function
which can be used in a similar way:
=LET(x,{0,1,2,3,4},x*2) (4)
The Excel LAMBDA function is arguably much more useful as it
is reusable, as in (1) above. We write a
function: =LAMBDA(x,x^2), and once we have added it to the Name
Manager and call it “square”, we can use it in any cell as in:
=square(2).
But do you see what we have done by adding a name? We have
really turned it into a version of (1).
In fact, it is functionally identical to the VBA:
public function square (x as double) as double
square = x^2
end function (5)
It’s not a lambda function as such but a clever way of introducing re-usable formulae as user-defined functions without needing to resort to VBA.
As I say, it is a welcome addition to make maintenance of formulae much easier – a “change-once change-all” approach which has to be commended. It however still a little clunky in its implementation though than it should be. The formulae, rather than being in visible cells, are now hidden away in the Name Manager. This makes its use more tricky and debugging spreadsheets considerably more difficult. It almost seems as if it has been shoe-horned into Name Manager and needs a little bit more thought around its interface.
It would be much better if the LAMBA() function names were in their own manager. The Lambda Name manager could then be designed to check the syntax of the function in the same way as it is checked when a formula is entered into a cell and have a help dialog as the fx function does in the formula bar. In fact, the function bar functionality should be imported wholesale into a Lambda Name manager. Finally, when a LAMBDA() function is used, the Lambda function manager should be automatically available showing the underlying formula being called, rather the convoluted way we will need to check at present via Name Manager.
I’ve made these suggestions to Microsoft and I am sure
others have had similar ideas. It is,
like a lot of early iterations of Excel additions, an extremely powerful new
tool which just needs a better implementation.
Overall, what is the conclusion of its first use? It's not a lambda function: it has the potential to be very much more useful.
