Home Newsletter

Deze pagina in het NederlandsHome > Article index >

Excel LAMBDA function, the basics

Introduction

In March 2021, Microsoft announced the new Excel LAMBDA worksheet function. This new function enables us to define custom functions which may replace UDFs currently written in VBA. These custom functions are powerful for two reasons:

  1. Any user capable of writing a cell formula can now define new functions
  2. Because it uses built-in functions and the built-in multi-threaded calculation engine, these new LAMBDA functions perform significantly better than VBA UDFs.

This page explains the basics of this new LAMBDA function and how you turn it into your very own custom named function.

Please note that for this function to work you need Office 365 and (at the moment of writing) you need to be on the so-called Office Insider program

Download the accompanying file

The LAMBDA function explained

There are some good articles out there on the new LAMBDA function already:

From the Microsft Research blog: LAMBDA: The ultimate Excel worksheet function

From Microsoft Excel Help: LAMBDA function

If you are familiar with the LET function, the structure of the new LAMBDA function will look familiar to you. In its simplest form it might look like this:

=LAMBDA(x,x^2)

All arguments but the last are inputs to the function, the last argument of LAMBDA is the calculation returning the final result. This means a LAMBDA formula always has one more argument than the number of input values it needs. In the example above, x is the input and x^2 is the returned result.

Example: the Quadratic formula

Anyone who has taken math at school has heard of the equation of a parabola

y=ax²+bx+c

and likely also of the Quadratic formula:

The Quadratic Formula

The Quadratic formula solves the problem of determining the intersections of a parabola with the x-axis (the roots of the equation).

In Math terms: it solves x in ax²+bx+c= 0 for given a, b and c.

Translated to an Excel LAMBDA function, this looks like:

=LAMBDA(a,b,c,(-b+{-1,1}*SQRT(b^2-4*a*c))/2/a)/p>

(the {-1,1} is Excel's way to get both results, it behaves as the +/- sign in the Quadratic Equation)

If you write this formula in a regular cell, Excel returns a rather unexpected result, a new Excel error code:

#CALC!

This is because we have not passed the required arguments to the formula. If placed in a cell like here, you must add the arguments -between brackets- behind the formula. Suppose our arguments are in cells A2, B2 and C2:

=LAMBDA(a,b,c,(-b+{-1,1}*SQRT(b^2-4*a*c))/2/a)(A2,B2,C2)

An example of the LAMBDA formula in a cell

Which results in:

An example of the LAMBDA formula in a cell

Transforming into a true function

By now you may be wondering what the fuss is about, this is still a (rather cumbersome looking) regular formula in a cell. Nothing fancy about it, now is it? But hold on, we'll get there. Open Excel's Name Manager (Formula tab, Name Manager button):

Excel's Name Manager

and click that "New..." button to add a new range name. Enter these details:

Adding a new LAMBDA name

Notice how I also took the extra effort of entering a comment, I'll show you why later on. After OK-ing, you can now enter a formula like this into a cell:

Using the LAMBDA name

Presto! A new user-created function!

As promised, here's why entering a comment for the name is useful. Select any empty cell and start typing =Qua, Excel shows the autocomplete drop-down which includes that comment:

The effect of the name comment

Now that is a great way to guide your users and tell them what the name is for!

I've also written an article on how to convert VBA UDF functions to a LAMBDA function.


Comments

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.