EXCEL FUNCTION- LAMBDA

Table of Contents

INTRODUCTION

The LAMBDA function is a new entry in the vast empire of Excel.

The function is yet to be introduced officially and available only for the beta users of OFFICE 365 only.

LAMBDA FUNCTION LETS THE USER DEFINE HIS OWN FUNCTION WHICH CAN BE REUSED AGAIN AND AGAIN JUST LIKE WE CREATE ONE IN VBA BUT SIMPLER THAN THAT.

LAMBDA function gives us an extreme power to declare our own function for local calculation.

For Example, if we need to use any function over and again, we can declare it using LAMBDA and use it at our convenience.

In this article, we would learn to use LAMBDA FUNCTION , its purpose, syntax formula and a few examples.

PURPOSE OF LAMBDA FUNCTION

LAMBDA FUNCTION lets us create  a custom function in Excel very easily.

It is a great function as we can create a function on our own, as per requirement and use it over and again.

We can declare the variables too.

For example, suppose we need to evaluate an equation with two variable such as (a+b)². This formula contains a and b as variables.

So we can create a function using LAMBDA such as DOUBLEVARIABLESQUARE.

We’ll discuss it further in the examples discussion.

PREREQUISITES TO LEARN LAMBDA FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: LAMBDA FUNCTION

The Syntax for the LAMBDA function is

=LAMBDA( [VARIABLE 1, VARIABLE 2 , VARIABLE 3…VARIABLE 253] EXPRESSION )

VARIABLES are the parameters or the variables  which are going to be passed as the argument when we call our function. The calculation will be done on the basis of the variables.

EXPRESSION is the calculation which will be done by the function being declared by the LAMBDA FUNCTION.

EXAMPLES: CREATING A FUNCTION TO FIND OUT FIFTH POWER USING LAMBDA FUNCTION

We took this example for a specific purpose.

1. This function is not directly available in Excel.

2. This is a very basic function and easy to understand.

The first step is to create and check the function usage.

CREATE THE LAMBDA FUNCTION

FOLLOW THE STEPS TO CREATE A LAMBDA IN A CELL FOR VERIFICATION

LAMBDA IN A CELL means to create the function in a single cell and trying it using the dummy values before putting it into real work.

  • Select any cell where we want to create and check the function.
  • Type the function =LAMBDA(a, a^5)(1)
  • This function DECLARES A AS ONE PARAMETER AND A^5 AS THE EXPRESSION OR CALCULATION. THE NEXT BRACKET PASSES THE VALUE OF THE VARIABLE AS 1.
  • It means 1 will be passed as the value of a and the result of this calculation will be 1 as 1^5=1.

This is the first step , which verifies our function to be correct.

CREATING A LAMBDA IN CELL AND TESTING IT

After we are satisfied with the working of the function, it is time to name it so that we can use it at our will.

So let us name it now.

FOLLOW THE STEPS TO NAME THE CUSTOM FUNCTION MADE USING LAMBDA

  • Go to FORMULAS TAB and choose NAME MANAGER.
  • As we open the NAME MANAGER.by clicking it, the following dialog box will open.
  • Click on the NEW BUTTON to make an entry for our new function.
CLICK NEW IN THE NAME MANAGER DIALOG BOX
  • As we click on NEW BUTTON, a small window opens up asking for the details.
  • Enter the details as below.
  • NAME : Custom name of your function.
  • SCOPE: Workbook is preferred. Although you can choose from the dropdown if you want to limit the scope to any specific sheet only.
  • COMMENT: Enter a brief description.
  • REFERS TO: Enter the lambda function here with full syntax and parameters.
  • For our example we wrote as =LAMBDA(a, a^5).
  • Click OK.
  • We are done and ready to use our newly created function.
ENTER THE RELEVANT DATA AS DESCRIBED ABOVE

USING THE CUSTOM FUNCTION MADE USING LAMBDA

So , we just created a new function known as FIFTH_POWER and we are ready to use this.

FOLLOW THE STEPS TO USE THE CUSTOM FUNCTION MADE USING LAMBDA

  • Select the cell.
  • Use the function as =FIFTH_POWER( NUMBER WHOSE FIFTH POWER IS TO BE FOUND).
  • For example, if we want to find out the fifth power of 45, we’ll use the function as FIFTH_POWER(45).