Names, LET, and LAMBDA

If you’ve ever thought, “Oh, I wish Excel had a way to automate calculating x,” your day has arrived. There will be some nuances and increasing complexity along the way, which is why I’ve chosen a straightforward path for us to follow. We’ll start with simple multiplication to calculate the volume of a box, and then riff on that calculation several ways as we make our way to the LET and LAMBDA functions. I’ll introduce the concept of Names, which are sometimes referred to as range Names. Names can streamline formula writing and make it easier to determine what a formula references and is calculating. The LET and LAMBDA functions share some characteristics with Names. For the benefit of anyone using an older version of Excel, I'll also briefly discuss creating custom worksheet functions with Visual Basic for Applications programming code.

In this chapter, I will cover the following topics:

• PRODUCT, IF...

Technical requirements

The IF, CHOOSE, and PRODUCT functions, as well as Names, work in any version of Excel. The SWITCH function requires Excel 2019 or later, while the LET function requires Excel 2021 or Microsoft 365. The LAMBDA function works in Microsoft 365 and Excel for the web. There are two example workbooks for this chapter: the Chapter 11 - Names, LET, and LAMBDA.xlsx workbook can be opened in any version of Excel, although SWITCH, LET, and LAMBDA will return #NAME? in unsupported versions of Excel. The Chapter 11 - BOX_VOLUME and XBOX_VOLUME.xlsm workbook contains programming code used to create custom worksheet functions and may be blocked by your network or Microsoft Windows. I’ll show you how to resolve a Windows block when we get to that part of this chapter. Both workbooks can be downloaded from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter11.

Simple volume calculations in Excel

Throughout this chapter, we will be computing the volume of a rectangular shape, such as a shipping box by multiplying its length by its width and height. Having a consistent calculation to follow this chapter will make it easier to focus on the Excel features and functions that I’ll be covering along the way. Let’s open the Chapter 11 - Names, LET, and LAMBDA.xlsx example workbook for this chapter and get started.

Multiplication

Anyone new to Microsoft Excel typically starts out with basic computations such as addition, subtraction, multiplication, and division, so let's begin there. Rest assured, we won’t linger long here; we’re mostly establishing a framework that we can build onto:

Figure 11.1 – Contrasting simple multiplication with the PRODUCT function

Cells C3:C5 of the Multiplication worksheet in Figure 11.1 contain the length, width, and height of a hypothetical box in...

Naming worksheet cells

Naming worksheet cells allows you to reference a cell or block of cells by way of a Name that you assign instead of a cell reference. You can use Names and cell references interchangeably in your spreadsheets. Excel offers four ways to assign Names to cells: Name Box, Create from Selection, Define Name, and Name Manager. There are a couple of rules to keep in mind:

• Names must begin with a letter, underscore (_), or backslash (\), so 2024TAX is not a valid Name.
• Names can be as short as a single letter, but you cannot assign the letters C or R as a name. This is because you when type C in the Name Box and press Enter Excel will select the current column, while R and Enter selects the current row.
• Names cannot be the same as any cell reference, such as TAX2024. The last column in an Excel worksheet is XFD, so combining a word with one to three letters and a number together will constitute an invalid name.
• Names cannot contain spaces, so TAX...

Introducing the LET function

The LET function requires at least one Name, which is known as a Variable. Such Names only work within the context of a single cell. This means that you can reuse Variables as much as you like, although it is best to assign Names to input cells that you reference repeatedly. As we have discussed, you can use the New Name dialog box to store a formula in a Name. However, such formulas are often difficult at best to use within formulas that reside in worksheet cells. Conversely, Variables within the LET function can contain text, numbers, or calculations that can then be referenced by Name in the calculation argument. As you’ll see, this can eliminate repetitive calculations in formulas.

Cell G3 of the LET function worksheet contains the =LET(length,C3,width,C4,height,C5,length*width*height) formula and returns 1,000. As shown in Figure 11.13, this is like using Names, but you don’t have to create the Variables in advance – you simply...

Introducing the LAMBDA function

Before the advent of LAMBDA, creating custom worksheet functions in Excel required writing programming code, such as with Visual Basic for Applications in Excel or by using other languages to create add-ins for Excel. You'll be relieved, and perhaps even amazed, to know that, you don’t need any programming experience to use LAMBDA. The ability to write formulas in Excel and an understanding of defining Names are all that you need to create custom worksheet functions with LAMBDA.

Tip

JavaScript-based worksheet functions can be created in Microsoft 365, Excel 2021, and Excel for Web.

LAMBDA functions can get wildly complex as you can create recursive formulas where a LAMBDA function refers to itself more than once, akin to a circular reference, as discussed in Chapter 9, Excel Quirks and Nuances. I do not have space to dive deep into LAMBDA, which means I won’t create any recursive formulas, but I can give you a running start...

Going deeper with LAMBDA functions

At this point, I’ve only covered the tip of the iceberg with LAMBDA, but I do have space to delve a bit further. First, we’ll look at incorporating optional arguments into LAMBDA functions. After that, I’ll detail some of the errors and conflicts that can arise when creating and using LAMBDA functions. Then, I’ll introduce the free Advanced Formula Environment, which offers a programming interface for creating complex LAMBDA functions. We’ll also create an XBOXVOLUME custom worksheet function that allows you to pass a two-column, three-row block of cells to the LAMBDA function, which will convert any measurements in feet into inches before computing the cubic volume of a box.

Optional LAMBDA Parameters

Cell F11 of the LAMBDA function worksheet, as shown in Figure 11.22, contains the =LAMBDA(quantity,price,[tax],(price+IF(ISOMITTED(tax),0,price *tax))*quantity) formula, which has an optional [tax] Parameter...

Custom VBA worksheet functions

VBA is short for Visual Basic for Applications, which is the programming language that you can use in Excel for Windows and Excel for macOS to create macros and custom worksheet functions known as user-defined functions. Over the years, I’ve made a few half-hearted attempts to learn other programming languages, but I never got any traction because I find the ability to program in Excel to be practically limitless, at least for my purposes. With that said, I do write much less code these days, thanks to Power Query, which I will discuss in the next chapter.

Tip

Excel for the web does not support Visual Basic for Applications, which means you cannot even open workbooks that contain macros on that platform.

The Chapter 11 – BOX_VOLUME and XBOX_VOLUME.xlsm workbook already has the programming code installed for two user-defined worksheet functions. You may encounter the security warning shown in Figure 11.28, which means you won’...

Summary

This chapter led you down a path that started with computing the cubic volume of a box with simple multiplication. Then, we layered on more and more complexity as we made our way to the LET and LAMBDA functions. The phrase game-changing often feels trite, but if you write complex formulas in Excel, both LET and LAMBDA empower you to write formulas today that you won’t cringe at when you see them again in the future. I’ve thought “what planet was I on that day?” more than a few times throughout my career when revisiting formulas that span multiple rows in the Formula Bar area.

We made several stops, including visiting functions such as PRODUCT, IF, CHOOSE, and SWITCH. I included these because of similarities in how you can pass information to the functions, or swap information around as stepping-stones toward LET and LAMBDA. From there, we embarked on creating Names, partly to correlate the similarities between the LET and LAMBDA functions, but...