*Figure 1 LAMBDA Function Syntax*

The LAMBDA function in Spread.NET v15 is used to create a custom reusable function with a friendly name using formula syntax. Then, call that function from cell formulas in any worksheet like a native function.

LAMBDA functions are portable across workbooks using simple copy and paste and don't require any programming to write back-end code in C# or VB to implement the function, like a custom function or visual function requires.

A LAMBDA function must be defined using a workbook-scope custom name, which becomes the function's name. NOTE: periods may not be used as part of the name. Spread.NET v15 also supports 7 New LAMBDA Helper functions, which are useful for creating advanced LAMBDA functions.

You can find the LAMBDA function examples discussed in this article in this workbook.

The LAMBDA function is currently only supported in Microsoft Excel 365 Office Insider Beta builds, so loading this LAMBDA function Examples XLSX workbook will show #NAME? errors when it is loaded into versions of Excel that don't yet support the LAMBDA function. You can download the free 30-day trial version of Spread.NET v15 and use the Spread Designer tool (installed with the Windows Forms controls) to load this workbook. After installing, the Spread Designer tool is available in the Start menu under GrapeCity:

This blog will be divided into three major sections and 12 subsections, as follows:

### LAMBDA Function Basics

Syntactically, the LAMBDA function is very similar to the LET Function (see blog). The difference is that when using the LAMBDA function, you do not specify values for the local names used in the calculation, as those values are specified when the function is invoked:

**=LAMBDA([parameter1, parameter2, ...,] calculation)**

#### Lambda Syntax

*parameter1*, *parameter2*, ... (optional): Arguments to the LAMBDA function. A LAMBDA can have up to 253 arguments, but no arguments are required if *calculation* does not need any.

*calculation* (required): The expression to calculate using *parameter1*, *parameter2*, etc.

There are some other key differences between LAMBDA and LET, which will be covered in more detail in the section about Advanced LAMBDA Functions.

A LAMBDA function can be defined in a cell formula for testing purposes. Still, the LAMBDA function must be invoked directly in the cell formula using parenthesis – '(' and ')' – to enclose the list of parameters, or it will return a #CALC! error:

**=LAMBDA(temp,(5/9)*(temp-32))(104)**

Entering the above formula in a cell will calculate the LAMBDA using the value 104 for the *temp* argument.

### Creating a LAMBDA Function

Create a new LAMBDA function following these steps:

- Test the formula
- Ensure that the formula for your calculation works correctly by testing it first in a cell. If the calculation is more complex, it might be helpful to create a more straightforward expression in a cell that calculates the result you want using some other cells as input values, with direct cell references to those cells representing the arguments for your function. This approach enables using all the formula debug tools in Excel to make your formula work correctly.

- Create the LAMBDA function in a cell
- When you have a working formula for your calculation, convert it into a LAMBDA function by replacing the input cell references with the appropriate argument names and test your new LAMBDA function in a cell, passing the arguments directly in the formula. You can try a few ideas to ensure that your new LAMBDA function is working correctly.

- Add the LAMBDA function to Name Manager
- Copy your LAMBDA function formula, then open Name Manager and type a meaningful name for your LAMBDA function, and optionally a short helpful description to show when the user types the formula, then paste in the formula for the LAMBDA function. You can define a LAMBDA function name to be scoped for a worksheet, but it will only be available to cells in that worksheet; it's generally recommended to use workbook scope.

### LAMBDA Function Examples

This blog will cover only some of the example LAMBDA functions in the above-linked workbook. The first example converts a Fahrenheit temperature to Celsius:

*Figure 2 LAMBDA Function ToCelsius Example*

The example ToCelsius requires only one argument, temp, and uses a simple expression to convert the value from Fahrenheit to Celsius:

**LAMBDA Function ToCelsius Example**

=LAMBDA(temp, (5/9)*(temp-32))

Using a bit of simple algebra, this function calculating Celsius from Fahrenheit can be solved for Fahrenheit to calculate the inverse function ToFahrenheit:

*Figure 3 LAMBDA Function ToFahrenheit Example*

**LAMBDA Function ToFahrenheit Example**

=LAMBDA(temp, (9/5)*temp+32)

### LAMBDA Helper Functions

To help with creating rich and advanced LAMBDA functions, Spread.NET has added support for seven new helper functions. These new functions provide essential support for implementing more advanced LAMBDA functions.

Most of these functions (except MAKEARRAY) are critical for implementing a LAMBDA function requiring a logical loop. There is no way to implement a logical loop using functional formula syntax. These functions are:

#### 1. BYCOL

*Figure 4 BYCOL Function Examples*

=BYCOL(*array*, *lambda(column)*)*array* (required): Array or range to separate by column.*lambda(column)* (required): LAMBDA with 1 argument, *column*, called once for each column of values in an array.

BYCOL applies a LAMBDA to each column in an *array* and returns a 1 row by COLUMNS(*array*) columns array of results. This is useful for implementing logic that requires looping through the columns in the array and returning a value for each column, calculated by the LAMBDA.

The first example references the data values {1, 2, 3; 4, 5, 6} in B22:D23 and returns a row array of the maximum value in each column ({4, 5, 6}):

**BYCOL Function Example 1**

=BYCOL(B22:D23, LAMBDA(array, MAX(array)))

The second example references the data values {1, 2, 3; 4, 5, 6} in B29:D30 and returns a row array of the sum of the squared values in each column ({17, 29, 45}):

**BYCOL Function Example 2**<

=BYCOL(B29:D30,LAMBDA(array,SUMSQ(array)))

#### 2. BYROW

*Figure 5 BYROW Function Examples*

=BYROW(*array*, *lambda(row)*)*array* (required): Array or range to separate by row*lambda(row)* (required): LAMBDA with 1 argument, *row*, called once for each row of values in array.

BYROW applies a LAMBDA to each row in *array* and returns a ROWS(*array*) row by 1 column array of results. This is useful for implementing logic that requires looping through the rows in the array and returning a value for each row, calculated by the LAMBDA.

The first example references the data values {1, 2, 3; 4, 5, 6} in B22:D23 and returns a column array of the maximum value in each row ({3; 6}):

**BYROW Function Example 1**

=BYROW(B22:D23, LAMBDA(array, MAX(array)))

The second example references the data values {1, 2, 3; 4, 5, 6} in B30:D31 and returns a column array of the sum of the squared values in each row ({14; 77}):

**BYROW Function Example 2**

=BYROW(B30:D31, LAMBDA(array, SUMSQ(array)))

#### 3. ISOMITTED

*Figure 6 ISOMITTED Function Example*

=ISOMITTED(*argument*)*argument* (required): LAMBDA argument to check whether omitted

ISOMITTED returns True if the specified LAMBDA *argument* is omitted. This is useful for implementing a LAMBDA that accepts optional arguments, like the *initial value* argument for REDUCE and SCAN.

The example shows how to return a friendly error string if a required argument is missing:

**ISOMITTED Function Example**

=LAMBDA(x, y, IF(ISOMITTED(y), "Missing second argument", x+y))(1,)

Note that since the LAMBDA is used directly in a cell, it must be invoked inline directly after it is defined, or else it will return the #CALC! error.

#### 4. MAKEARRAY(*rows*, *columns*, *lambda(row, column)*)

*Figure 7 MAKEARRAY Function Example*

=MAKEARRAY(*rows*, *columns*, *lambda(row, column)*)*rows* (required): Number of rows in the array.*columns* (required): Number of columns in the array.*lambda(row, column)* (required): LAMBDA with 2 arguments, *row* and *column*, specifying the row and column indexes of the array cell.

MAKEARRAY returns a calculated array of the specified size by applying a LAMBDA. This is useful for generating a new array of the specified size and calculating the values using a LAMBDA with the row and column index.

The example references the values in C48 and E48 (10 and 5) for *rows* and *columns*, respectively, and generates a random array of values from the strings "Red", "Blue", and "Green" (this value will vary each calculation cycle randomly):

**MAKEARRAY Function Example**

=MAKEARRAY(C48, E48, LAMBDA(row, col, CHOOSE(RANDBETWEEN(1,3), "Red", "Blue", "Green")))

#### 5. MAP Function

*Figure 8 MAP Function Example*

=MAP(*array1*, _lambda_or_array<#>, ..._)*array1* (required): Array of values to map.

_lambda_or_array<#>_ (required): Next array of values to map, or a LAMBDA with one argument for each array.

MAP returns an array by mapping each value in the specified array(s) to a new value by applying a LAMBDA with one argument for each array specified. To clarify, this function must specify at least one array in *array1.*

Still, it can also specify one or more subsequent array arguments, with the last argument specifying the LAMBDA with the number of arguments equal to the number of arrays defined.

The LAMBDA is called for each element of *array1* and passes the element's value in *array1* and the respective values of the corresponding element in each other array. This function maps the elements of one or more arrays into a new array the same size as *array1* by applying the LAMBDA.

The example references the data values {1, 2, 3; 4, 5, 6} in B19:D20 and returns an array of values in which each value in the original array greater than 4 is squared ({1, 2, 3; 4; 25; 36}):

**MAP Function Example**

=MAP(B19:D20, LAMBDA(a, IF(a>4, a*a, a*1)

*Note that including the "a*1" coerces the value to integer-type and prevents a #CALC! error.

#### 6. REDUCE Function

*Figure 9 REDUCE Function Example*

=REDUCE([*initial value*], *array*, *lambda(accumulator, value)*)*initial value* (optional): Initial value for accumulator.*array* (required): Array to reduce to a single value.*lambda(accumulator, value)* (required): LAMBDA with 2 arguments,*accumulator* and *value*, called once for each element in the *array*.

The REDUCE function reduces *array* to a single accumulated value by applying a LAMBDA to each value and returning the total value in *accumulator*. This is useful for implementing a LAMBDA that needs to loop through the array elements and apply some LAMBDA to each element in *array*.

The *accumulator* argument for the LAMBDA is initialized with *initial value* if specified (or with the value 0 if not). In cases where the LAMBDA performs a multiplicative operation (e.g., *accumulator* = *accumulator* * *value*), the *initial value* of 1 should be specified.

The LAMBDA is called for each respective value in *array* going left to right, top to bottom, and the *accumulator* value result from the previous LAMBDA call is passed to each successive call to the LAMBDA. The final result in *accumulator* is returned from REDUCE.

The example references the data values {1, 2, 3; 4, 5, 6} in B23:D24 and returns the sum of the squared values (91):

**REDUCE Function Example**

=REDUCE(, B23:D24, LAMBDA(a, b, a+b^2))

#### 7. SCAN Function

*Figure 10 SCAN Function Example*

=SCAN([*initial value*], *array*, *lambda(accumulator, value)*)*initial value* (optional): Initial value for accumulator *array* (required): Array to scan.*lambda(accumulator, value)* (required): LAMBDA with 2 arguments,*accumulator* and *value*, called once for each element in *array*

The SCAN function operates like REDUCE, except instead of returning only the final value in *accumulator*, SCAN returns the array of intermediate values calculated by REDUCE.

Each element in the returned array is the intermediate value returned by the LAMBDA in *accumulator* for that array element in *array*. SCAN will return all the values calculated by REDUCE in each LAMBDA call in a single array the same size as *array*.

The first example references the data values {1, 2, 3; 4, 5, 6} in B22:D23 and returns a list of factorial values ({1, 2, 6; 24, 120, 720}<):

**SCAN Example 1**

=SCAN(1, B22:D23, LAMBDA(a, b, a*b))

The second example references the data values {"a", "b", "c"; "d", "e", "f"}in B30:D31 and returns each respective value concatenated with the previous value ({"a", "ab", "abc"; "abcd", "abcde", "abcdef"}):

**SCAN Example 2**

=SCAN("", B30:D31, LAMBDA(a, b, a&b))

### Advanced LAMBDA Functions

LAMBDA functions are implemented using the helper functions and/or recursion to calculate the value or values to return. A LAMBDA function can recursively call itself using the name assigned to the Function in Name Manager as part of the calculation, as long as it doesn't result in an endless loop.

A LAMBDA function can call other LAMBDA functions as part of its calculation, and some of those can be implemented using recursion. You can also define a LAMBDA function that expects a LAMBDA function argument, like the helper functions described above.

#### LET Function and LAMBDA

It is important to note that the LET function does NOT support defining a local name that uses a recursive LAMBDA to specify the name – you should avoid using it to implement a recursive LAMBDA.

#### ReplaceChars Function

*Figure 11 REPLACECHARS Function Example*

**REPLACECHARS Function**

```
=LAMBDA(str, chars, sub
IF(chars="",
str,
ReplaceChars(
SUBSTITUTE(str, LEFT(chars), sub),
MID(chars,2,LEN(chars) - 1),
sub
)
)
)
```

The REPLACECHARS function is a recursive LAMBDA function that takes three arguments *str* (the string in which to replace characters), *chars* (the characters to be replaced), and *sub* (the string to replace the characters with).

The terminating condition for the recursion is when *chars* are empty, in which case *str* is returned since there is nothing more to replace.

Otherwise, the function calls itself recursively with *str* being the return value of SUBSTITUTE, which replaces all occurrences of the first character in *chars* with *sub*, and *chars* being the return value of MID, which strips the first character from *chars* and passing *sub* through each level of recursion.

### Reverse Function

*Figure 12 REVERSE Function Example*

The REVERSE function is another recursive LAMBDA function that uses two helper functions: HEAD and TAIL:

**HEAD Function**

```
=LAMBDA(str,
IF(str="",
"",
LEFT(str, 1)
)
)
```

The HEAD function takes a single argument, *str,* and returns either the empty string "" if *str* is empty, or if not returns the left-most character of *str*.

**TAIL Function**

```
=LAMBDA(str,
IF(str="",
"",
RIGHT(str, LEN(str) - 1)
)
)
```

The TAIL function takes single argument *str* and returns either the empty string "" if *str* is empty, or if not, uses RIGHT and LEN to strip the first character of *str*. Note that this use of RIGHT and LEN is functionally equivalent to MID and LEN in the REPLACECHARS function example above.

**REVERSE Function**

```
=LAMBDA(str,
IF(LEN(str)<2,
str,
REVERSE(TAIL(str)) & HEAD(str)
)
)
```

The REVERSE function takes single argument *str* and checks whether the length is less than 2, in which case str is returned as there is nothing to reverse (this is the terminating condition).

Otherwise, the function calls itself recursively with *str* being TAIL(*str*) (stripping the first character), which is then concatenated with HEAD(*str*) (the first character of *str*).

### Calendar Function

*Figure 13 CALENDAR Function Example*

The CALENDAR function is implemented using a helper LAMBDA function VCAT, which concatenates two arrays into one vertical array by stacking the first on top of the second:

**VCAT Function**

```
=LAMBDA(top, bot,
LET(width, MIN(COLUMNS(top), COLUMNS(bot)),
topH, ROWS(top),
MAKEARRAY(ROWS(bot)+topH, width,
LAMBDA(i, j,
IF(i < topH,
INDEX(top, i, j),
INDEX(bot, i-topH, j)
)
)
)
)
)
```

This helper LAMBDA function does not use recursion, so using the LET function isn't a problem, which makes this function relatively easy to understand. The arguments *top* and *bot* specify the two arrays to concatenate, then the *width* is set to the smaller of the column counts of *top* and *bot.*

*Toph* is set to the number of rows on *top. MAKEARRAY* is used to generate a new array with *rows* equal to the sum of the row counts of *top* and *bot*, and *columns* equal to the *width.*

The values for the first *topH* rows coming from the *top* and the values of the remaining rows coming from *bot*, using INDEX to return the value from the appropriate array in each case.

The CALENDAR function also uses these names months and days to lookup month and day names for generating the calendar:

**Months**

={"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}

**Days**

={"Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun"}

The CALENDAR function makes use of VCAT and also uses the LET function, as it does not use any recursion:

**CALENDAR Function**

```
=LAMBDA(serial, mark,
LET(daysInMonth, EOMONTH(serial, 0)-EOMONTH(serial, -1),
foMonth, DATE(YEAR(serial), MONTH(serial), 1),
dayPadding, WEEKDAY(foMonth, 2)-1,
calendarRows, ROUNDUP((dayPadding+daysInMonth)/7, 0),
body, MAKEARRAY(calendarRows, 7,
LAMBDA(i, j,
LET(seqNum, ((i-1)*7+j)-dayPadding,
IFS(seqNum<=0, "",
seqNum=DAY(serial), IF(mark, "X", seqNum),
seqNum<=daysInMonth, seqNum,
TRUE, ""
)
)
)
),
vcat(
MAKEARRAY(1, 7,
LAMBDA(i, j,
IF(j=1,
INDEX(months, MONTH(serial)),
IF(j=2,
YEAR(serial),
""
)
)
)
),
MAKEARRAY(calendarRows+1, 7,
LAMBDA(i, j,
IF(i=1,
INDEX(days, j, 1),
INDEX(body, i-1, j)
)
)
)
)
)
)
```

The CALENDAR function takes two arguments, *serial* and *mark,* which specify the serial date for the calendar month to generate, and a boolean flag to indicate whether to mark that date with "X" (*mark* = true) or not (in that case, the day number is displayed as for all other days).

To generate the calendar, first, some local variables are defined and calculated for daysInMonth (number of days in the month), foMonth (first day of the month), dayPaddding (how many days of padding to include in the first row for the previous month), and calendarRows (number of rows required to display the month).

The *body* is defined and calculated using MAKEARRAY and another LET function to create the calendar body's array of day number values (or place the "X" in the cell for *serial* if the *mark* is true).

Finally, VCAT is used to combine two generated arrays, the first forming the heading for the calendar showing the month and year in the first two cells, and the second forming the calendar body showing the day names in the first row under the calendar heading row, and each row of the body below that row.