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, aa, a1)
Note that including the "a1" 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.
Sean Lawyer