Skip to main content

THE LAMBDA FUNCTION

Available in Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web

Kubicle Function Rating:
5-stars-png-no-background-4 - Abram's Painting

This function is a powerful one, but it’s power is entirely related to the user’s imagination!

SYNTAX:

=LAMBDA (parameter, …, calculation)

ARGUMENTS:

  • Parameter = An input for the function
  • Calculation = The calculation to perform as the result of the function.

DESCRIPTION:

The LAMBDA function is used to create custom, reusable functions and label them by names defined by the workbook user.  The LAMBDA function is available for use only within a single workbook (i.e. they cannot be used across multiple excel workbook files).

 

EXAMPLES:

1 – Create a reusable function which converts Fahrenheit to Celsius

2 – Create a function to calculate compound annual growth rate

3 – Create a function to calculate number of days until the end of quarter 

 

HOW TO CREATE YOUR FIRST LAMBDA FUNCTION:

  1. Define your first LAMBDA function using Name Manager:
    Formulas > Name Manager and click on New (macOS: Formulas > Define Name).

    1. Define your formula using the following: 
      • Name: FtoC
      • Refers to: =LAMBDA(x,((x-32)*(5/9))
      • Comments: Add anything that might be helpful!
    2. SAVE
    3. In your workbook you can now call your FtoC function by entering ‘=FtoC(‘Parameter’)’ and it will return the Celsius equivalent of the cell number value

 

BONUS:

If you want to overcome the pitfall of not having your LAMBDA functions available in all workbooks by default, build all your LAMBDA functions in a blank template workbook file and start all new workbooks using this template!

Leave a Reply