This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Creating and using Lambda Functions in Excel Part two of a four-part series of blogs
You can now create and use anonymous functions in Excel (usually called "Lambda functions"). Learn how to add to the stock of Excel functions without having to learn any programming code! Creating and using Lambda Functions in Excel Stages to follow to create a lambda function (this blog) Three examples of lambda functions When will you be able to use lambda functions?

Creating and using Lambda Functions in Excel

Part two of a four-part series of blogs

You can now create and use anonymous functions in Excel (usually called "Lambda functions"). Learn how to add to the stock of Excel functions without having to learn any programming code!

Creating and using Lambda Functions in Excel
Stages to follow to create a lambda function (this blog)
Three examples of lambda functions
When will you be able to use lambda functions?

Posted by Andy Brown on 21 December 2020

In this blog

Step 1 - creating the formula
Step 2 - identify your arguments
Step 3 - create the function
Step 4 - name your function
Step 5 - testing your function

Stages to follow to create a lambda function

This page shows how you can create any lambda function by breaking it down into 5 separate steps (as you become more familiar with the concept you'll be able to merge these together):

Step	Notes
1 - Create the formula	Create the formula for what you're trying to do without using a function.
2 - Identify your arguments	Work out what you need to supply to your function in order for it to return the right answer.
3 - Create the function	Type in the function and test it out with dummy arguments.
4 - Name your function	Use the Excel Name Manager dialog box to name the function that you've created.
5 - Try out your function	Test to see whether your function works!

Let's begin by defining the formula we want to calculate in our function.

Step 1 - creating the formula

The first thing to do is to check that you know what you're trying to achieve:

For our simple example, we will use the & key to concatenate a person's first name, a space and their last name.

Step 2 - identify your arguments

Most functions require one or more arguments to work. Our example needs to know two bits of information: someone's first name and their last name. We will therefore create the following two arguments in our lambda function:

Argument	Notes
FirstName	The first name of a person
LastName	The last name of the person

Note that you can call the arguments anything you like. Good programmers use descriptive names for their arguments, to make it easier to remember what information they represent.

Step 3 - create the function

The syntax of a lambda function is this:

A lambda function consists of a list of arguments followed by a formula determining what the function should return.

When you type =LA into a cell the LAMBDA function should appear:

If you only see the LARGE function appearing, you haven't yet got access to the LAMBDA function (see a later page in this blog for what to do about this).

You can then type in your function (here I've pressed Alt + Enter to create carriage returns to break up the formula with new lines, to make it easier to read):

The function will take two arguments (which we've called FirstName and LastName) and return them joined together with a space in between.

When you press Enter, this will give you a calculation error:

Although you've created a function, you haven't actually passed any arguments to it.

To test your function, pass some arguments to it by adding them in parentheses after the function:

Here we're going to see if the function works with Donald Trump (resisting the temptation to insert any joke at this point). Don't put a space between the function and your list of arguments (if you do, the formula won't work).

When you press Enter, the formula gives Donald Trump:

Yeah - it works!

Step 4 - name your function

So far you haven't gained anything (in fact, you've just made a simple formula considerably more complex). To make your function reusable, first copy it:

Be careful to copy just the syntax of the function, not the values you're testing it with.

Go to Name Manager on the FORMULA tab of the ribbon:

You can also press Ctrl + F3 to go to this.

Choose to create a new name:

Click on this button to create a new lambda function (which is just a type of range name).

Type in a name for your new function, then press Tab to go to the Refers to: box:

As promised, we're calling this function WiseOwlFullName - a bit of a mouthful, perhaps.

Paste in to the Refers to: box your function code:

This is a terrible editor - if you make a mistake, it's often easier to start again rather than try to correct it! Microsoft have hinted that they may improve this editing facility in future Office 365 versions (they need to).

Press OK and then Close:

After confirming the creation of your new function, click on Close or press ESC to close down the Name Manager dialog box.

Step 5 - testing your function

Time now to see if your function works!

Initially things look promising - Excel has heard of your function.

However, when you get to the list of arguments you're not given any help:

It's up to you to remember which arguments came in which order.

But it's all been worth it, surely?

Pressing Enter should give you Donald Trump!

Having shown how to build up a formula, the next page of this blog gives 3 examples of where you might use lambda functions in practice (the last one of which uses recursion to call itself!).

Parts of this blog
Creating and using Lambda Functions in Excel Stages to follow to create a lambda function (this blog) Three examples of lambda functions When will you be able to use lambda functions?

Some other pages relevant to the above blogs include:

Courses in Excel

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Stages to follow to create a lambda function

Step 1 - creating the formula

Step 2 - identify your arguments

Step 3 - create the function

Step 4 - name your function

Step 5 - testing your function

Head office

London

Manchester