Skip to content

Instantly share code, notes, and snippets.

@amdevine

amdevine/excel-lambda-let.md

Last active August 9, 2022 14:04
Show Gist options
  • Save amdevine/403cdb75af2dec79422d6c9f9c367ca2 to your computer and use it in GitHub Desktop.
Save amdevine/403cdb75af2dec79422d6c9f9c367ca2 to your computer and use it in GitHub Desktop.
Download ZIP
Excel: Custom functions with LAMBDA and LET
Raw
excel-lambda-let.md

Define custom Excel functions with LAMBDA() and LET()

LAMBDA functions

https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67

LAMBDA can be used to write custom functions with inputs that you specify. LAMBDA functions can be saved in the Name Manager and then called from any cell in the workbook.

Might be good to define an Excel template with built-in LAMBDA functions?

e.g. Define the following in the Name Manager:

Name: TOCELSIUS
Scope: Workbook
Comment: Convert a Fahrenheit temperature to Celsius
Refers to: =LAMBDA(temp, (5/9) * (temp-32))

LET function

https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999

The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function.

LET can be used in conjunction with LAMBDA to make fairly complex custom functions.

e.g. Define the following in the Name Manager:

Name: SUMOFSQUARES
Scope: Workbook
Comments: Return the sum of two squared numbers
Refers to: =LAMBDA(x, y, LET(xx, x^2, yy, y^2, xx+yy))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment