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

0. Introduction

01 - Create custom functions in Excel
02 - What you should know before starting

1. Introducing Custom Functions in Excel

03 - Explore custom functions in Excel VBA
04 - Describe the Excel formula language
05 - Describe the goals of formula programming
06 - Manage formulas on the Formula Bar

2. Creating a Custom Function

07 - Define a function using LAMBDA
08 - Assign a function name to a LAMBDA
09 - Define a variable using LET
10 - Use a LET statement in a LAMBDA
11 - Refer to Excel tables in a LAMBDA

3. Adding Logic to a Custom Function

12 - Create logical branches using IF and IFS
13 - Select a value using CHOOSE
14 - Select a value or display a default value using SWITCH

4. Creating Custom Functions for Specific Solutions

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

5. Using LAMBDA within Other Functions

19 - Update values using MAP
20 - Summarize values using REDUCE
21 - Calculate intermediate values using SCAN
22 - Generate an array of values using MAKEARRAY
23 - Apply a LAMBDA to an array by column using BYCOL
24 - Apply a LAMBDA to an array by row using BYROW
25 - Manage LAMBDA output
26 - Troubleshoot LAMBDA output

Conclusion

27 - Further resources