Getting to grips with the new LAMBDA function in Excel

LAMBDA formula to create user defined functions in excel

Microsoft have recently introduced a very exciting new function for Excel users.  This function enables Excel users to define their own re-usable functions without resorting to using the macro programming language (VBA).

This opens up enormous possibilities for Excel users who are non programmers.  When Excel functions are used within a worksheet, they can often get hugely complicated especially if they use nested functions.  If the formula has to be used several times within the workbook, it may become difficult to replicate.  You could use copy and paste, but this methodology may cause unexpected errors within the workbook.

LAMBDA provides a solution to this. Once the user has perfected the formula, it can be converted into a custom re-usable function that can be used exactly like the native Excel functions.  It can accept parameters as defined by the LAMBDA function, and the user does not need to worry about whether they used the correct formula, as this becomes a tried and tested function that can be used over and over again within the workbook.

 

Requirements

To use the LAMBDA function in Excel you need to have a Microsoft Office 365 subscription and the latest version of Microsoft Office installed. You also need the latest Microsoft updates installed (including Beta updates). Currently, you also need to join Microsoft Office Insiders in order to receive Beta updates.

 

Syntax

=LAMBDA([parameter1, parameter2, …,] calculation)

The parameter argument defines the values that you want to pass to the calculation.  This could be a cell reference, a text string, or a numeric value.

You can define up to 253 parameters, but this limit is unlikely to be reached!

The parameter argument is optional. Parameter names are defined as a text string e.g. x, number, myPar, etc.

The calculation argument defines the calculation that will be performed using the defined parameters. It must return a result, and it is not optional.

 

Example

=LAMBDA(x,x*10)(1)

This will return the result of 10 in the cell. A single parameter (x) is defined and the calculation argument multiplies that parameter by 10. The value of (1) after the first part of the function defines the value for the x parameter. This is only required if you are using the LAMBDA function directly into a cell instead of using it to create a re-usable function.

You can use standard Excel functions within the calculation argument.

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

This LAMBDA function will use Pythagoras to calculate the hypotenuse of a right-angled triangle.  The parameters x and y represent the horizontal and vertical sides of the triangle. The calculation argument squares both the parameters and adds them together and then uses the Excel SQRT function to get the value of the hypotenuse.

 

Creating a re-usable function with LAMBDA

You can easily create your own custom function which can be used exactly like the native Excel functions within your workbook.

To do this, you need to define a new name. Click on Formulas in the Excel ribbon, and then click on Define Name in the Defined Names group of the ribbon.

Enter a new name into the Name field of the form e.g. MyNewFunction.

Enter your LAMBDA formula in the Refers To field.

In this example, the LAMBDA Pythagoras example is used as the formula in the Refers To field.

Click OK, and your new function is now defined and can be used as a normal Excel function.

To call the function from anywhere within the workbook, you use the following syntax:

=MyNewFunction(3,4)

This passes the x (3) and y (4) parameters to your function and produces the result of 5 (classic 3,4,5 triangle).

You can use your new function from anywhere in the workbook in exactly the same way as if was a native Excel function.

 

Points to watch

More than 253 parameters give a #VALUE error.

Passing the incorrect number of arguments will give a #VALUE error.

If your LAMBDA function creates a circular formula, the this will give a #NUM error.

Lambda names and parameters follow the Excel syntax rules. Also, you must not use a full stop (.) within a parameter name.

 

Recursion

Using LAMBDA, you have the ability to make a function work recursively until a specific condition is met.  Previously, it was only possible to do this using a custom function defined as a macro in VBA.

For example, you could have a text string in a cell where you want to remove certain illegal characters from it.  Previously, you would have to use a function to remove the illegal characters one at a time.  This could get very complicated because you would not always have the same number of illegal characters to be removed in each string, and it would need careful use of functions to prevent errors occurring.

Microsoft has provided this sample LAMBDA function to show how this can now be done:

=LAMBDA(textString, illegalChars,

IF(illegalChars=””, textstring,

REPLACECHARS(

SUBSTITUTE(textString, LEFT(illegalChars, 1), “”),

RIGHT(illegalChars, LEN(illegalChars)-1)

)))

 

The parameter textString is the original string of characters which contains some illegal characters, and the parameter illegalChars contains a string of the characters to be removed from textString.

The function as shown above is turned into a function called REPLACECHARS by using the methodology described above to create a function with a new name.

The function keeps calling itself until the IF statement is true (the illegalChars parameter is empty). It substitutes the left most character in the illegalChars string with nothing, and then uses the RIGHT function to delete the left most character from the illegalChars string.

As the process iterates through the illegalChars string, the illegalChars string gets shorter and shorter until it is empty, at which point the process exits.