LAMBDA Intro

Prerequisites
LAMBDA requires an Excel version that supports LAMBDA. At the time of this writing, that means Excel in an Office 365 subscription or Office for the web.

This course uses Microsoft's free add-in Advanced Formula Environment. To get this add-in, use Excel's Home tab and select Add-ins icon. Search for and select Excel labs. The Advanced Formula Environment is one of Excel Labs' options. 

What is LAMBDA?

Simply put, 

LAMBDA is an Excel function that creates Excel functions from Excel functions. 

While that definition is simple, it sounds confusing. To clarify, allow me to describe functions in a way you many not be familiar with. We will start very basic so I can associate terms that, I believe, will make understanding LAMBDA easier.

An Excel function performs an operation over arguments (also called parameters). An example function is SUM(). SUM()'s operation is totaling values. Values are the arguments we provide it. Arguments can be literals (numbers like 1, 2, 3, or text like "A", "Orange", "This is a literal string"), or range references (A1:C1, or named ranges). 

As stated earlier, LAMBDA() is an Excel function but, initially, LAMBDA has no operation and no arguments defined. We must define both. I call the LAMBDA's operation definition and argument definitions our LAMBDA's specification. This specification is what goes inside the LAMBDAs parenthesis:

LAMBDA(Specification)

As alluded to, the specification consists of an Operation definition and the operation's Arguments definition. The arguments definition goes first. We can place as many arguments as needed first with each argument separated by a comma. What ever is after the last comma is the operation definition. 

LAMBDA(Argument1 definition, Argument2 definition, ..., Operation definition)

Specification

Our LAMBDA's complete specification is it's arguments definition followed by a comma and its operation definition like so:

Income, Expenses, Income - Expenses

This, placed inside the LAMBDA function's parenthesis defines our new function:

LAMBDA(Income, Expenses, Income - Expenses)

Arguments definition

Our operation requires two values: Income and Expenses. Our arguments' definition is simply each value separated by a comma. Thus, our LAMBDA's argument definition is:

Income, Expenses

Operation definition

The operation definition is an Excel formula. The formula can be, and often is, quite complex. It can be as long a 8192 characters. But it can also be as short as just a handful of characters. However long the formula is, what is in the formula determines how many arguments we need and which are required versus which are optional. To illustrate we will create a simple formula that calculates profit. Profit is income minus expenses. If income was placed in cell A1 and expenses in A2, we could calculate profit with this formula: 

= $A$1 - $A$2

As written, that formula ONLY works where income is in cell A1 and expenses is in A2. But functions like SUM() accept arguments from anywhere. So let us remove the location specific information from this formula and rewrite it as: 

= Income - Expenses

This, without the leading equals sign, defines our LAMBDA's operation. We must now define LAMBDA's arguments from that formula.

Using our LAMBDA

What comes next is, perhaps, what confuses LAMBDA beginners most. We have a LAMBDA with parentheses. But unlike all Excel functions, what is inside the parentheses is the a new function's specifications, not the new function's arguments. We need another set of parentheses to hold the new function's arguments. To use it we must provide its arguments. Like all Excel functions, the arguments go in parentheses after the function. If our profit's value is in A1 and our expenses' value is in A2 we could call our function like so:

LAMBDA Advantages

Readability: Documenting formulas

We just created our own function. While it is simple, it does something no formula can do. It conveys purpose. When we look at a normal formula, we see how something is being calculated. When we look at a function like Profit, we understand why. We also know what A1 and A2 are because Excel's IntelliSense tells us they better be Income and Expenses.

Reusability: Write once. Use many.

Never re-invent the wheel is an old saying. Named LAMBDAs help us capture our formulas and load them into other workbooks unchanged so we never have to write our formulas again. Just load and go! This is especially important for complex formulas.

Simplicity: Making the complex simple

Named LAMBDAs can remove the complexity of formulas using several functions, operators and interim results by packaging them into one function that explains it all.

Functionality: Making the impossible possible

Without LAMBDA, there are some common calculations that dynamic arrays cannot perform. With LAMBDA, they can.

LAMBDA Limitations

Prior to LAMBDA, we could write what are called User Defined Functions (UDFs) with VBA. For many UDFs, we don't need VBA. But VBA can still do things LAMBDA cannot. For example, VBA can access Excel's object model and Windows' APIs. LAMBDA cannot, thus, LAMBDA cannot pop up user forms or control other applications like VBA can. 

Summary

Creating functions for others requires more thought and effort. The standards guide us through the process of making our functions highly usable by others and thus, successful.