Excel: Creating Custom Functions with LAMBDA

Excel: Creating Custom Functions with LAMBDA

English | MP4 | AVC 1280×720 | AAC 448KHz 2ch | 1h 55m | 273 MB

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.

Table of Contents

1 Create custom functions in Excel
2 What you should know before starting

Introducing Custom Functions in Excel
3 Explore custom functions in Excel VBA
4 Describe the Excel formula language
5 Describe the goals of formula programming
6 Manage formulas on the Formula Bar

Creating a Custom Function
7 Define a function using LAMBDA
8 Assign a function name to a LAMBDA
9 Define a variable using LET
10 Use a LET statement in a LAMBDA
11 Refer to Excel tables in a LAMBDA

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

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

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

27 Further resources