Excel: Creating Custom Functions with LAMBDA

1h 55mAdvanced2022-11-29

Authors

Curt Frye

President of Technology and Society, Incorporated

Course details

Discover how to create your own custom Excel functions with the LAMBDA function. Instructor Curt Frye begins with a brief history of custom functions in Excel, then explains the structure of the Excel formula language. Next, Curt shows you how to create a LAMBDA and define it as a custom function in Excel’s Name Manager. He also highlights how to manage loops, recursion, and conditional steps. Plus, Curt covers how to use LAMBDA as an input to other new worksheet functions.

Skills covered

Software Design PatternsSpreadsheetsMicrosoft ExcelBusiness Software and ToolsMicrosoftSoftware DevelopmentDeep Dive (X:Y)

Concepts

Introduction

Create custom functions in Excel
What you should know before starting

Introducing Custom Functions in Excel

Explore custom functions in Excel VBA
Describe the Excel formula language
Describe the goals of formula programming
Manage formulas on the Formula Bar

Creating a Custom Function

Define a function using LAMBDA
Assign a function name to a LAMBDA
Define a variable using LET
Use a LET statement in a LAMBDA
Refer to Excel tables in a LAMBDA

Adding Logic to a Custom Function

Create logical branches using IF and IFS
Select a value using CHOOSE
Select a value or display a default value using SWITCH

Creating Custom Functions for Specific Solutions

Scenario - Calculate Economic Order Quantity
Scenario - Calculate quality of service
Scenario - Calculate process capacity given a batch size
Scenario - Clean up imported text

Using LAMBDA within Other Functions

Update values using MAP
Summarize values using REDUCE
Calculate intermediate values using SCAN
Generate an array of values using MAKEARRAY
Apply a LAMBDA to an array by column using BYCOL
Apply a LAMBDA to an array by row using BYROW
Manage LAMBDA output
Troubleshoot LAMBDA output

Conclusion

Further resources