THE LAMBDA FUNCTION

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

Kubicle Function Rating:

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!