LAMBDA Function

Excel has a lot of useful functions: FILTER, SUMIFS, VLOOKUP, XLOOKUP, SUM, SUBTOTAL and more. All in, Excel has about 500 functions, so it’s likely there’s one that returns the result you need. But now and again you may find yourself where there isn’t a built-in function that returns the value you are trying to compute. Or the result you need requires an extremely complex formula with tons of nested functions. Historically, the solution was to create our own user-defined function using VBA. And we can still do that. But now we have another option. We can create a custom function from an Excel formula that uses the new—and much easier to implement—LAMBDA function. To illustrate the LAMBDA function, we’ll create a custom function that does something that isn’t built-in to Excel: computing the fiscal quarter of any date.

Illustration

For this example, we’ll use a fiscal year-end of June 30, but the formula could easily be modified for other year-ends as desired. Let’s say we have a bunch of transactions with various transaction dates. To keep things simple, Figure 1 shows just the date column.

Figure 1

We’d like to write a formula that computes the fiscal quarter for each transaction date. Since Excel doesn’t have a built-in function that computes fiscal quarters, let’s begin our journey by writing a formula that uses built-in functions. There are many ways to write a formula such as this. One way is by using the MONTH and CHOOSE functions.

MONTH

By way of background, the MONTH function returns the month number of the specified date. For example, we could write the following formula into C8:

=MONTH(B8)

We could fill the formula down and bam (Figure 2).

Figure 2

As we can see, a January date returns 1, an April date returns 4, and so on. The MONTH function returns the month number of the specified date. But that isn’t exactly what we want. We need to somehow convert that month number into the corresponding fiscal quarter. This is where the CHOOSE function comes in handy.

CHOOSE

The CHOOSE function looks at the value of the first argument (index_num) and returns the corresponding value argument. It looks a bit like this:

=CHOOSE(index_num, value1, value2, value3, …)

So, if the index_num argument is 1, the function returns the value1 argument. When the index_num is 2, it returns value2, and so on. Let’s see how we can use the CHOOSE function to return a quarter number based on a month number. To make this easier to visualize, let’s momentarily assume we are on a calendar year.

Calendar Year

If we were on a calendar year, we know that January, February and March dates are Q1.

• So, for months 1, 2 and 3 we want the formula to return 1.
• When the month numbers are 4, 5 and 6, we want to return quarter 2.
• Months of 7, 8 and 9 should return 3.
• And months 10, 11, and 12 should return 4.

We can do this with the following formula:

=CHOOSE(MONTH(B8),1,1,1,2,2,2,3,3,3,4,4,4)

The index_num argument is the month number computed by the MONTH function. Based on the month number, the CHOOSE function will return the  corresponding value from the 12 remaining arguments.

• That is, month numbers 1, 2 and 3 return 1, 1 and 1.
• Month numbers 4, 5 and 6 return 2, 2 and 2.
• Month numbers 7, 8 and 9 return 3, 3 and 3.
• And month numbers 10, 11 and 12 return 4, 4 and 4.

We can fill the formula down to confirm, as seen in Figure 3.

Figure 3

So far, so good; now let’s tackle the fiscal year conversion.

Fiscal Year

Since our organization is a June 30 year-end, we know that July, August and September dates are Quarter 1 while January, February and March dates are Quarter 3. And so on. If we think about them in month-number order, 1-12, we know that month numbers 1, 2 and 3 should return 3, 3 and 3. Month numbers 4, 5 and 6 should return 4, 4 and 4. Month numbers 7, 8 and 9 should return 1, 1 and 1. And months 10, 11 and 12 should return 2, 2 and 2. So, we simply update the formula as follows:

=CHOOSE(MONTH(B8),3,3,3,4,4,4,1,1,1,2,2,2)

We fill the formula down and bam, Figure 4.

Figure 4

And we did it! Yay! We wrote a formula that returns the correct fiscal quarter. Now, let’s say that we have many areas in the workbook that require that same conversion, from dates to fiscal quarters. Or let’s say that other people must  perform this calculation often, as well. And, maybe they aren’t as comfortable combining the CHOOSE and MONTH functions like this. Rather than write the formula that uses CHOOSE and MONTH over and over, we can use our formula to create a custom function. That is, we can create a custom function such as FISCALQ that performs this conversion for us. That way, anytime we, or another user, needs to perform this conversion, a simple formula such as the following will return the desired result:

=FISCALQ(B8)

This makes it easier to understand what the formula does and makes it easier to implement in our workbook. We can simply convert our existing formula into a custom function with the help of the LAMBDA function. Note: Depending on your version of Excel, you may or may not have access to the LAMDBA function.

LAMBDA

The LAMBDA function enables us to create a custom function that we can use in our formulas. Although we could use the LAMBDA function in any formula in any cell, the way we create the function name is by using it with the Name Manager. We start by copying our working formula and then selecting the Formulas > Name Manager command. We click New and then paste the formula into the Refers to field. We provide a function name such as FISCALQ in the Name field. At this point, we should have something that looks like what we see in Figure 5:

Figure 5

Now, this is where the LAMDBA function comes into play. The LAMBDA function basically looks like this:

=LAMBDA(parameter, calculation)

Parameter is basically the name of our input value (function argument) for example, the date we are trying to convert. We can pretty much name it whatever we want (avoid spaces and funky characters). In our case we’ll call it CalDate. The calculation is an expression that computes the desired result. In our case, it will be a slightly modified version of our CHOOSE/MONTH formula. We basically need to wrap the LAMBDA function around the existing formula, and then substitute the B8 date reference with the new parameter name (CalDate) like this:

=LAMBDA(CalDate,CHOOSE(MONTH(CalDate),3,3,3,4,4,4,1,1,1,2,2,2))

If we look at that formula for a moment, we’ll see that the LAMBDA function accepts as its input a variable named CalDate. As such, CalDate is the FISCALQ function’s argument. LAMBDA takes the CalDate input value and uses it in the calculation, which in this case is the CHOOSE/MONTH formula. You’ll notice that previously, the MONTH function operated directly on a cell reference (B8). We replaced that direct cell reference with CalDate. So, LAMBDA is expecting an input value of CalDate, and then it provides that value to the MONTH function. After updating the formula, the New Name dialog should look a bit like Figure 6:

Figure 6

We click OK to save the new function. Once done, we can use that function in an ordinary formula. In our case, we can write the following formula into cell C8:

=FISCALQ(B8)

The reference to B8 is passed into the LAMBDA function as CalDate. The expression then uses the MONTH function to compute the month number of the CalDate parameter. The CHOOSE function does its thing and returns the corresponding quarter number. When we fill the formula down, bam, Figure 7.

Figure 7

So, why would we go through the effort to convert the CHOOSE/MONTH formula into a custom FISCALQ function? There are a couple of advantages. For example, when reviewing the formula, it is easier to understand that the formula is computing the fiscal quarter. Once set up, it is also easier to use that function in other formulas throughout the workbook. Also, tucking the logic of the fiscal year conversion into a custom function means that it is less likely that another user will accidentally “break” the formula. Plus, the custom function is easily transferred to other workbooks by simply copying any formula cell with the FISCALQ function and pasting it into the other workbook. Excel will automatically create the function name and related formula in the new workbook. I hope this helps you get started with the LAMBDA function. And remember, Excel rules!  

124-CPE Training Pass

If you are looking for practical CPE that will help you improve efficiency, check out our CPE Pass. It is an all-Excel training pass that offers 124 CPE.