Excel Lambda Function Guide (Practical Examples!)

Ever thought of the magnitude of opportunities that rest ahead of you only if you were a programmer?

Programmers can make and run their functions. Well, now you can do that too. And the best part – you don’t need any VBA or JavaScript skills for that.

The Excel LAMDA function will get you through the job in the easiest way possible. If you’ve never heard of it before, this article will help you learn everything about it.

AcuityTraining offers a wide variety of Excel training courses for all levels, check them out here.

What Is The Excel Lambda Function?

First thing first, what is the LAMBDA function even?

It enables users to create custom functions that they can save by any name they want. This function will then be saved and can be used throughout the workbook. You can now compose any formula in Excel, wrap it into the LAMBDA function, give it a suitable name and save it as a function.

The next time you need to apply the same formula, don’t go all the way retyping it. You can instead refer to the custom function built and saved by you previously.

And the good news! You don’t need to be a programmer or VBA specialist to do all of this.

 

Syntax and Arguments

The LAMBDA function reads as follows.

= LAMBDA ( [parameter_or_calculation], [parameter_or_calculation],.. )

  • Parameter: Whenever you type any function in Excel, a small popup shows you the number of arguments a function has and that you must enter for the function to run.

This time it’s you who wears the developer’s hat. Parameters define the arguments that you want to set for your formula.

A parameter is an input value that can be in the form of cell references or values. Excel can accept up to 253 arguments.

  • Calculation: This has to be the formula that you want to be converted into a function.

Let’s jump right into the article to see how you can create your very own function in Excel.

 

How To Create The Lambda Function

It’s time we customize our function in Excel using the LAMBDA function.

Step 1: Write the LAMBDA function

    • Make a core formula. The LAMBDA function is a formula instilled into Excel by the name of a function.

For example, if you want to divide two numbers in Excel, you write the following formula.

= 20 / 5

It involves no function and is a simple formula. Run this in Excel, and you’d get the following answer.

Division formula in Excel

The answer is 4. Simple.

So our formula is 20 (the dividend) divided by 5 (the divisor).

Dividend / Divisor

    • Launch the LAMBDA function to create your function

= LAMBDA (

Writing the LAMBDA function

    • Define the parameters (arguments).

These are the input values that would be required whenever the targeted function is run. We want our function to have two input values, the dividend, and the divisor.

    • Write both these as the arguments of the LAMBDA function.

= LAMBDA ( Dividend, Divisor,

Writing the arguments of the LAMBDA function

    • Next, define the calculation (the formula).

Our core formula is dividend/divisor. Add another comma to the LAMBDA function to define the said formula.

= LAMBDA ( Dividend, Divisor, Dividend / Divisor )

Defining the calculation

The LAMBDA function is all complete and set to go.

Don’t hit ‘Enter’ already. Or if you do, Excel will return a #CALC! error because it has no values to process.

 

Step 2: Test the LAMBDA function

Has Excel rightly picked the function defined above? There’s one way how you may check this.

The ‘function call’ allows you to check if the formula defined in the LAMBDA function works correctly.

    • To test the LAMBDA function, give it some input values right after the parenthesis is closed.

= LAMBDA ( Dividend, Divisor, Dividend / Divisor ) (A1, A2)

The function call

We have added a new parenthesis to the end of the LAMBDA function (A1, A2).

The LAMBDA function will test the formula (Dividend / Divisor) using these two cell references.

Excel runs the function

The answer is 4 which is 20 divided by 5. The results provided by LAMBDA adhere to the result of the simple formula (= 20 / 5).

The function call for the LAMBDA function

It is always good to test your LAMBDA function to ensure everything sits in place.

 

Step 3: Save the function

We have created and tested the LAMBDA function already. Now it’s time to name it and save it in Excel.

    • Copy the LAMBDA formula. Make sure to only copy the following part of the LAMBDA formula.

= LAMBDA ( Dividend, Divisor, Dividend / Divisor )

Must not copy the function call. It is only meant to test the function and has nothing to do with saving the function.

    • Go to Formula > Defined Names > Name Manager. Or use the keyboard shortcut [Control Key + F3] to launch the Name Manager.
    • This launches the name manager as shown below.

The Name Manager

    • From the Name Manager dialog box, Click ‘New’ as highlighted above.
    • In the Name Manager, fill in the following fields:
      1. Against the Name box, type a suitable name for the function. We are setting it as DIVISION.
      2. Against the Scope box, choose ‘Workbook’. This means you can use this function across the whole workbook. Alternatively, if you select a particular Sheet as the scope, you’d only be able to use this function across that sheet.
      3. Against the Refers box, paste the formula copied above. The formula must start with an equal sign (=). To make any changes to the formula once pasted, press the F2 key to enter the editing mode.

Fields of the Name Manager

    • Click ‘Okay’ to save it.

 

Saving the function in the name manager

Testing the function!

Guess what? We are all done. It’s time we test the function we’ve just created.

    • Activate a cell and begin writing with an equal to sign:

= DIVISION (

 

Writing the DIVISION function

Must Note!

Did you notice something? As soon as you typed the first three letters of DIV, Excel identified it as a function. This means the DIVISION function is now saved to the Excel functions library. Using a custom function after it’s created in Excel is seamless enough.

Running the Division function

    • The first argument of the DIVISION function is ‘dividend’.

= DIVISION ( A1

First argument of the DIVISION function

We have created a reference to cell A1 as the first argument it contains the number to be divided.

    • The second argument of the DIVISION function is ‘divisor’.

= DIVISION (A1, A2)

Second argument of the DIVISION function

We have created a reference to cell A2 as the second argument contains the divisor.

    • Hit ‘Enter’ to see the results as follows.

Excel runs the DIVISION function

Excel runs the division function as intended i.e. Dividend / Divisor.

That’s how you can create and save any function in Excel.

Things to Remember about the LAMBDA function

To have a seamless experience creating and running functions with the LAMBDA function, take note of the following:

    1. The function or parameter name must not be longer than 255 characters.
    2. It must not start with any punctuation mark except for an underscore ( _ ) or backslash ( \ ).
    3. Do not use names that coincide with the names of built-in Excel functions.
    4. Do not use names that are similar to cell references like AAB1 etc.
    5. Must not enter more than 253 parameters into a single LAMBDA function.

 

Lambda Function Examples

We are already across one basic example of creating a function in Excel using LAMBDA. Rest is all about practice. By introducing the LAMBDA function, Microsoft Excel has set forth limitless opportunities for Excel users to create functions.

Let’s see some more examples of how you can create amazing functions in Excel which you always thought should’ve been there!

Example No. 1: Basic Formula

Remember the formula for finding the area of a right-angled triangle? No?

Neither do we. Let’s feed it into Excel as a function so that the memorizing responsibility shifts to Excel.

Creating the TAREA function

Step 1:

    • The formula for finding the area of a triangle is as follows:

= (b*h) / 2

B = Base of the triangle; and

H = Height of the triangle

    • Launch the LAMBDA function to create your function

= LAMBDA (

Writing the LAMBDA function

    • Define the parameters (arguments). So our core formula has two parameters and is:

= (Base * Height) / 2

    • Let’s formulate this into the LAMBDA function.

We want our function to have two input values, the base, and the height.

    • Write both these as the input parameters of the LAMBDA function.

= LAMBDA ( Base, Height,

Writing the arguments of the LAMBDA function

    • Next, define the calculation (the formula).

= LAMBDA ( Base, Height, (Base*Height)/2 )

Defining the calculation

The LAMBDA function is all complete and set to go.

Step 2:

Time to test the LAMBDA function!

    • Give it the input values for base and height right after the parenthesis is closed.

= LAMBDA ( Base, Height, Base / Height ) (A2, B2)

The function call

    • The LAMBDA function will test the formula (Base*Height / 2) using the values in Cell A2 and B2.

Testing the TAREA function

The answer is 6. The result provided by the TAREA function adheres to the result of the simple formula (= (4*3)/2).

This means we are all good to go.

    • Copy the following part of the LAMBDA formula.

= LAMBDA ( Base, Height, Base / Height )

    • Go to Formula > Defined Names > Name Manager.
    • This launches the name manager as shown below.

The Name Manager

    • From the Name Manager dialog box, Click ‘New’ as highlighted above.

Saving the TAREA function

    • In the Name Manager, fill in the following fields:
      1. Against the Name box, type a suitable name for the function. We are setting it as TAREA.
      2. Against the Scope box, choose ‘Workbook’.
      3. Against the Refers box, paste the formula copied above.

Fields of the Name Manager

    • Click ‘Okay’ to save it.

Step 4:

And we are all done. Let’s test the function we’ve just created.

Running the TAREA function

Here we have some data points for the base and height of a triangle.

Datapoints for the area of a triangle

    • To find the area using these data points, activate a cell and begin writing with an equal to sign:

= TAREA (

Writing the TAREA function

As soon as you type the first few letters, Excel identifies it as a function.

    • The first argument of the TAREA function is ‘base’.

= TAREA ( A2

First argument of the TAREA function

We have created a reference to cell A1 as the first argument it contains the bases.

    • The second argument of the TAREA function is ‘height’.

= TAREA (A2, B2)

Second argument of the TAREA function

We have created a reference to cell B2 as the second argument contains the height.

    • Hit ‘Enter’ to see the results as follows.

Excel runs the TAREA function

Viola! Excel runs the TAREA function as intended i.e. (Base*Height) / 2.

With this, you now have your function for calculating the area of a triangle in Excel.

Pro Tip!

Unlike ordinary functions, as you begin typing a Custom Lambda, Excel won’t show a descriptive text for the function. For in-built functions, this short text explains what the function is meant to do.

To help this, write a descriptive text for the function in the Comment box of the Name manager. This text will appear with the formula every time you use it in Excel.

Example 2: Multiple parameters

Until now, we have seen examples of creating a custom LAMBDA function with two parameters only. However, the maximum number of parameters that a LAMBDA function can have is 253.

Let us now customize a LAMBDA function with more arguments (but not 253 obviously).

The image below shows the data for the employees of a Company.

Employee data

The data includes details like the joining and leaving date of employees, their salary, and deduction percentage.

How do we calculate the amount of their gratuity?

The formula for calculating gratuity is as follows:

= (LY – JY) * Salary * (100%-Deduction Percentage)

Calculating gratuity using the formula

It has around four input values. How can we set up a gratuity function in Excel for the above formula?

Step 1:

    • Launch the LAMBDA function to create a gratuity function.

= LAMBDA (

Writing the LAMBDA formula

    • Define the parameters for your LAMBDA function.
    • Our formula for gratuity has four parameters:
      1. Joining Year (JY)
      2. Leaving Year (LY)
      3. Salary
      4. Deduction percentage
    • Write all the above four input values as parameters of the LAMBDA function.

= LAMBDA (JY, LY, Salary, Deduction,

Writing the arguments of the LAMBDA function

    • Next, define the calculation argument (the formula).

= LAMBDA (JY, LY, Salary, Deduction, (LY– JY)*Salary*(100%-Deduction))

Defining the calculation

We have defined the calculation with all four input values.

Pro Tip!

If your formula has more than one operation performed, ensure using parenthesis in the right place.

The operation that you want to be performed first must be enclosed in parentheses. If you miss out on the parenthesis, Excel will perform the operations in the PEMDAS order.

PEMDAS stands for Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction.

Step 2:

    • To test the LAMBDA function, give it the input values for all four parameters.

= LAMBDA (JY, LY, Salary, Deduction, (LY – JY)*Salary*(100%-Deduction)) (B2, C2, D2, E2)

The function call

Here comes the testing phase.

Testing the gratuity function

The LAMBDA function computes the gratuity payment as follows:

LAMBDA returns the gratuity

The answer is $88,000.

Let’s double-check the same by applying the formula separately in Excel.

Writing the Gratuity formula

The answers match! Good to go.

    • Copy the following part of the LAMBDA formula.

= LAMBDA (JY, LY, Salary, Deduction, (LY– JY)*Salary*(100%-Deduction))

    • Go to Formula > Defined Names > Name Manager to launch the name manager.

The Name Manager

    • In the Name Manager dialog box, fill in the following fields:
      1. Against the Name box, set the name of the function as ‘GRATUITY’.
      2. Against the Scope box, choose ‘Workbook’.
      3. Against the Refers box, paste the formula copied above.

Fields of the Name Manager

    • Click ‘Okay’ to save it.

Let’s test the function we’ve just created using the same dataset.

Data for employees

To find the Gratuity payment for employee A, activate a cell and begin writing:

= GRATUITY (

Writing the Gratuity function

    • For each parameter of the GRATUITY function, refer to the relevant cell from the given data.

= Gratuity (B2,C2,D2,E2)

Second argument of the TAREA function

      • Where Cell B2 contains the joining year.
      • Cell C2 contains the leaving year.
      • Cell D2 contains the salary of each employee.
      • Cell E2 contains the deduction percentage.
    • Hit ‘Enter’ to see the results as follows.

Excel runs the gratuity function

Here we have the gratuity for Employee A. Drag and drop it to the remaining employees to find the gratuity payment for them all.

Gratuity payment for all employees

Pro Tip!

Besides all the good that the LAMBDA function has to offer, it is only restricted to a single workbook. With all due regret, this means you have to create a custom function for every workbook. These are not reusable functions.

An easy way to copy the LAMBDA function from one book to another is – copying a sheet from that workbook (that contains the custom function) to the new workbook.

 

Example 3: Embedding other functions in LAMBDA

Have you ever calculated the average in Excel using the Average function of Excel?

Easy, right? However, if you have ever happened to calculate the weighted average in Excel, you’d know Excel doesn’t offer any inbuilt function for it.

You need to set up a formula to calculate the weighted average. Or else, you might have to use a combination of other functions to calculate the weighted average. If Excel lacks a function, let’s create one for ourselves. And here we go!

The image below shows the data for an investment portfolio.

Gratuity payment for all employees

The data includes the share prices of three different companies. And the number of shares held in each company.

Can we calculate the weighted average share price for this portfolio?

The core formula for calculating the weighted average is as follows:

= Sum of all values * Sum of their weights / Sum of weights

In our example, this translates to:

= Sum of Share Price * Sum of No. of Shares / Sum of number of shares

To make it simpler, we can write the above formula in Excel as follows:

= SUMPRODUCT (Values, Weights) / SUM (Weights)

Pro Tip!

The SUMPRODUCT function multiplies any two given arrays and sums the answer.

Need help working with Arrays in Excel? Try our guide to the UNIQUE Function here!

Before we make a function for it, let’s try doing it simply in Excel.

Weighted average share price in Excel

We applied the SUMPRODUCT function to multiply the sum of share prices with the sum of the number of shares. Next, we used the SUM function to find the sum of the number of shares. We divided both the above numbers to find the weighted average share price.

Too gruesome. It’s time we compress this all into a handy function.

If you need more of an explanation on how Arrays work in Excel, we give a breakdown in this article on Cell Array Formulas.

 

Step 1:

    • Let’s begin writing. Launch the LAMBDA function to create a weighted average function.

= LAMBDA (

Writing the LAMBDA function

    • Define the parameters for your LAMBDA function.

So, we have two input values (parameters) to our LAMBDA function:

    1. Values (Share Prices)
    2. Weights (No. of shares)
    • Write the above input values as parameters of the LAMBDA function.

= LAMBDA (Values, Weights,

Writing the arguments of the LAMBDA function

    • Define the formula for calculating Weighted Average in Excel.

= LAMBDA (Values, Weights, SUMPRODUCT(Values, Weights)/SUM(Weights))

Defining the calculation

    • To test the LAMBDA function and avoid any #CALC error, let’s populate the function call.

= LAMBDA (Values, Weights, SUMPRODUCT (Values, Weights) / SUM (Weights) ) (A2:A3, B2:B3)

The function call

We have referred to two ranges (A2:A3) and (B2:B3) as the Value and Weight of the function call.

Testing the W.AVG function

The answer is 38.44.

Let’s double-check the same by applying the formula separately in Excel.

SUMPRODUCT and SUM function

The matching answers tell us we are on the right way.

    • Copy the following part of the LAMBDA formula.

= LAMBDA ( Values, Weights, SUMPRODUCT (Values, Weights) /SUM (Weights) )

    • Go to Formula > Defined Names > Name Manager to launch the name manager.
    • From the Name Manager dialog box, Click ‘New’.
    • Fill in the following fields:
      1. Against the Name box, set the name of the function as ‘W.AVG’.
      2. Against the Scope box, choose ‘Workbook’.
      3. Against the Refers box, paste the formula copied above.

Fields of the Name Manager

    • Click ‘Okay’ to save it.

The W.AVG function

Ready to test the function we’ve just created using the same portfolio data?

Investment portfolio

    • To find the weighted average share price for this portfolio, write the W.AVG function we just created.

= W.AVG (B2:B4, C2:C4)

Writing the Weighted average function

> The range B2:B4 contain the values (share prices).

> The range C2:C4 contain the weights (number of shares).

    • Hit ‘Enter’ to see the results as follows.

Excel runs the weighted average function

The weighted average function calculates the weighted average of the shares as 39.30.

A formula that otherwise seemed so long with multiple functions and arguments is now only a matter of two arguments. Using the very versatile LAMBDA function, you can create custom functions for anything and everything.

Just like the LAMBDA function, other advanced functions of Excel include the XLOOKUP and EFFECT functions. Learn them with us now!

 

Possible Errors And How To Fix Them

The magnitude of problems that the LAMBDA function might pose is proportionate to its versatility.

Here are a few common problems you might face with the LAMBDA function.

 

1.      Inappropriate Naming:

The LAMBDA functions give you the privilege to set function and parameter names of your choice.

But this comes for a price. You must be careful while you set the names for the function you create. And for the parameters of that function.

For example, if you set the names of the parameters as DIV1, DIV2, and so on, here’s how the LAMBDA function would react.

too less arguments error

This is because DIV1 and DIV2 are cell references in Excel. When named as a parameter, Excel takes these as a cell reference and not as the name of parameters.

You may change it to something different such as DIVIDEND and DIVISOR.

And Excel wouldn’t pose the error as before.

2.      #VALUE! error

If your LAMBDA function gives back a #VALUE error, there could be many reasons for it. Check for either of the following errors:

    • Your LAMBDA function has more than 253 parameters to it. This breaches the maximum limit for the parameters of the LAMBDA function.
    • The names defined for the parameters are not the same as those used in the calculation. This might be a spelling error or an oversight. For example:

= LAMBDA (Dividend, Divisor, Dividand / Devisor)

The parameters defined in the above function have different spelling. However, the names used in the calculation differ in spelling. This will give back a #VALUE error.

    • You have used the wrong number of arguments while using the same function in the workbook.

3.      #CALC! error

With the LAMBDA function, Excel is likely to pose a #CALC! error if you save the function without testing it by using the function call.

It is suggested to save a LAMBDA function only after you have tested it.

Conclusion

That’s all about the LAMBDA function. Excel experts call it the most powerful function of Excel – a function that makes Excel Turing-complete.

By introducing the LAMBDA function, Microsoft has made Excel computationally universal.

At any time, if you find the function library of Excel short for your creative needs, create and add new functions to it.

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.