Lambda function components
Let’s look at an example which puts the two earlier concepts together and creates a basic lambda function.
Suppose we have the following formula:
=LAMBDA(x, x+122)
Deconstructing this example and mapping it to the syntax definition, let’s look at x:
=LAMBDA(x, x+122)
The first occurrence of x is defining the first and only parameter input to LAMBDA.
Now let’s look at x+122:
=LAMBDA(x, x+122)
The second parameter to LAMBDA, x+122, is the calculation, which makes use of the x name and will be substituted for the input value when the lambda function is called.
For example, suppose you called the lambda function and input the value 1 for x, Excel would substitute x for 1 and do the following calculation:
1 + 122
Which, as we all know:
1 + 122 = 123=
It’s almost as easy as… 1… 2… 3…
If you’ve been pasting the example above into Excel, you may have noticed some #CALC! errors. To resolve those, you’ll need to learn and perform the next step.
Calling a lambda function
To put it simply, you call a lambda function the same way you call native functions in Excel.
To illustrate this, let’s revisit the previous example and show how to call it with a value.
Uncalled
=LAMBDA(x, x+122)
Called with the value 1
=LAMBDA(x, x+122)(1)
Returns
123
When should you call the lambda function?
- You will want to return an uncalled lambda function when you initially store and name it, or for passing into other lambda functions which may subsequently call it.
- And you should call the lambda function when you are making use of it during authoring and later re-use.
Naming a lambda function
Once you have authored a lambda function and are happy with the results, you should give it a name and store it for re-use.
To do this, you will want to make use of the Name Manager, so let’s dive in!
Name Manager Usage
To open Name Manager, click Formulas > Name Manager.
In the Name Manager dialog box, click the New button.
In the New Name dialog box, enter the requested information, and then click OK.
- Name: The name of your function.
- Comment: A description and associated tooltip which will be shown when calling your function.
- Refers to: Your lambda function definition.
And that’s it! Now, you can make use of your newly crafted custom function in the workbook by calling it by its name.
For example, we could call this lambda function by authoring a formula which calls MYLAMBDA
=MYLAMBDA(122)
Which would return the value
123