How to use LAMBDA in Excel 365

 

In Excel 365, the lambda function is a powerful feature that allows you to create custom calculations on the fly. Here's a step-by-step guide on how to use lambda functions in Excel 365:

 

 1. Check Your Excel Version:

   Ensure that you are using Excel 365 or a later version, as lambda functions are a feature introduced in these versions.

 

 2. Understand Lambda Function Syntax:

   A lambda function in Excel 365 is defined using the `LAMBDA` function. The syntax is as follows:

   =LAMBDA(parameters, expression)

   - `parameters`: The input parameters for your function.

   - `expression`: The calculation or expression that defines your function.

 

 3. Create a Simple Lambda Function:

   Let's start with a basic example. Suppose you want a function to square a number. You can define a lambda function like this:

      =LAMBDA(x, x^2)

Note: This expression in the cell will give you an error “#CALC!”

 

   This lambda function takes one parameter (`x`) and returns the square of that parameter.

 

 4. Use the Lambda Function in a Formula:

   Once you've defined your lambda function, you can use it in any formula by calling it like any other Excel function. For example:

      =LAMBDA(x, x^2)(5)

   This formula calculates the square of 5 using the lambda function.

 

 5. Handle Multiple Parameters:

   Lambda functions can have multiple parameters. For instance, to create a function that adds two numbers:

      =LAMBDA(a, b, a + b)

Note: This expression in the cell will give you an error “#CALC!”

  

   Use it in a formula like:

      =LAMBDA(a, b, a + b)(3, 4)

      This formula returns the sum of 3 and 4.

 

 6. Create Complex Lambda Functions:

   Lambda functions can involve complex calculations. For example, a lambda function to calculate the area of a circle given its radius (`r`) could be:

  

   =LAMBDA(r, PI() * r^2)

Note: This expression in the cell will give you an error “#CALC!”

  

   Use it in a formula as:

      =LAMBDA(r, PI() * r^2)(2)

      This formula calculates the area of a circle with a radius of 2.

 

 7. Name and Manage Lambda Functions:

   You can name your lambda functions for better clarity. Use the `LET` function to assign a name to your lambda function and use it within your worksheet.

 

 8. Edit and Debug Lambda Functions:

   To edit or debug lambda functions, use the formula bar. Select the cell with the lambda function, and you'll see the function in the formula bar. Make changes as needed.