This function can be used to create custom, reusable functions and call them by a desired name. You can create functions for commonly used formulas and effectively add your own functions.

Syntax

LAMBDA([parameter1, parameter2, …,] calculation)

Argument

This function has the following arguments:

Remarks

• If an incorrect number of parameters or more than 253 parameters are provided to a LAMBDA function, it returns a #VALUE! error
• If you call a LAMBDA function from within itself and the call is circular, it returns a #NUM! error
• If you create a LAMBDA function in a cell without also calling it from within the cell, it returns a #CALC! error
• It is not advisable to use a period (.) in a parameter name

Data Types

The concise format returns a text value, whereas the strict format returns an array of the same size and shape as the input.

Examples

The following sample code show the basic usage of LAMBDA function.

IWorkbook workbook = fpSpread1.AsWorkbook();
workbook.Names.Add("HEAD", "LAMBDA(str, IF(str=\"\",\"error: HEAD of empty string\",LEFT(str,1)))");
workbook.Names.Add("TAIL", "LAMBDA(str, IF(str=\"\",\"error: TAIL of empty string\",RIGHT(str,LEN(str)-1)))");
workbook.Names.Add("REVERSE", "LAMBDA(str,IF(str=\"\",\"\",REVERSE(TAIL(str))&HEAD(str)))");
IWorksheet sheet1 = workbook.Worksheets[0];
sheet1.Cells["A1"].Value = 12345;
sheet1.Cells["B1"].Formula2 = "HEAD(A1)";
sheet1.Cells["C1"].Formula2 = "TAIL(A1)";
sheet1.Cells["D1"].Formula2 = "REVERSE(A1)";
sheet1.Cells["B2:D2"].Formula2 = "FORMULATEXT(B1)";
 
sheet1.Cells["F1"].Formula2 = "LAMBDA(x, x+122)(1)";

The output of above code will look like below:

Version Available

This function is available in product version 15 or later.

Lambda Helper Functions

Spread also supports seven new LAMBDA helper functions. These functions help in the authoring of re-usable LAMBDA functions and also serve as stand-alone functions themselves. For more information, refer:

1. BYROW

2. BYCOL

3. ISOMITTED

4. MAKEARRAY

5. MAP

6. REDUCE

7. SCAN