LET

LET function is used to assign names to calculation results. You can also use variable names to define intermediate calculations, values, or names within the parenthesis "()" of the LET function. You need to define name and value pairs associated with the function and a calculation that uses them all. By using this function, you don't have to remember what a specific range or cell reference refers to, or what the calculation is supposed to do, or even copy pasting the same expression all over again.

Performance Improvement Using LET Function

LET function improves the calculation performance by eliminating redundant recalculation of the intermediate values defined in the variables. The below example shows the performance improvement by using LET function to calculate a dynamic array value and then repeatedly refers that array in a formula. Here, both spreadsheet controls are initialized with the same list of 500 names and addresses, and both use the same formula in cell N2 to return a sorted list of unique states in a dynamic array: =SORT(UNIQUE(Table1[state])) The spreadsheet controls use the formula in cells O2:O48 to calculate the number of rows in the table and return a string value based on the number of rows in nested IF functions. The left spreadsheet uses the LET function to optimize this calculation and reuse the dynamic array result inside the IF functions: =LET(cities,ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,""))),IF(cities<10,"Less than 10.",IF(cities<20,"Between 10 and 19.",IF(cities<30,"Between 20 and 29","30 or more.")))) Whereas, the right spreadsheet does not use the LET function, and instead repeats the expression for cities inside the IF function: =IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))<10,"Less than 10.",IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))<20,"Between 10 and 19.",IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))<30,"Between 20 and 29","30 or more."))) When the Recalculate menu item is activated to recalculate the spreadsheets, special code is used to disable CalculationOnDemand in the CalculationEngine to force all cells to recalculate, and the results are shown in the TitleInfo across the top of each spreadsheet control. The left spreadsheet using the LET function calculates 2-4 times faster than the right spreadsheet which does not use LET function.

Syntax

LET(name1, value1, [name2…], [value2…], calculation)

Arguments

This function has these arguments:

Remarks

The last argument must be a calculation that returns a result.

Data Types

Returns a Variant type.

Examples

The following sample code show the basic usage with two LET functions.

// Dynamic array - LET function requests dynamic array feature and hence we should enable it from CalcEngine before setting the formula
 fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures |= CalcFeatures.DynamicArray;

// Set Value
for (int i = 0; i < 5; i++)
{
  fpSpread1.AsWorkbook().Worksheets[0].Cells[i, 2, 4, 2].Value = new Random(2).Next(20, 50);
  fpSpread1.AsWorkbook().Worksheets[0].Cells[i, 3, 4, 3].Value = new Random(3).Next(10, 40);
}
// set text for column header cells
fpSpread1.AsWorkbook().ActiveSheet.ColumnHeader.Cells[0, 1].Text = "LET function in cell B1";
fpSpread1.AsWorkbook().ActiveSheet.ColumnHeader.Cells[0, 4].Text = "LET function in cell E1";

// set value in cell Range A1:A5
fpSpread1.AsWorkbook().Worksheets[0].Cells["A1:A5"].Value = 14;
// set formula in cell B1 which will work as dynamic array
fpSpread1.AsWorkbook().Worksheets[0].Cells["B1"].Formula2 = "LET(range, A1:A5, range+1)";

// LET function with two variables "range" and "const"
// "range" is referring to "D1:D5" && "const" is referring to "C1:C5"
fpSpread1.AsWorkbook().Worksheets[0].Cells["E1"].Formula2 = "LET(range, D1:D5, const, C1:C5, range + const)";

// set column width
fpSpread1.AsWorkbook().ActiveSheet.Columns[1].ColumnWidth = 180;
fpSpread1.AsWorkbook().ActiveSheet.Columns[4].ColumnWidth = 180;

The output of above code is shown as below where cell B1 contains the formula "= LET (range, A1: A5, range + 1)" with "range + 1" as the last argument to represent the formula that was actually evaluated. This formula returns 15 as a result. Similarly, cell E1 contains the formula "= LET (range, D1: D5, const, C1: C5, range + const)", which uses "range" and "const" as variables. Here, "range" stands for D1: D5 and "const" stands for C1: C5. This formula returns 61 as a result. The following example considers a use case with some raw sales data which needs to be filtered to show one person and add a dash to any blank cells. This can be achieved by using the LET function to calculate the FILTER function once as shown below:

// Dynamic array - LET function requests dynamic array feature and hence we should enable it from CalcEngine before setting the formula
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures |= CalcFeatures.DynamicArray;
// get the worksheet
IWorksheet worksheet = fpSpread1.AsWorkbook().Worksheets[0];
// set text
worksheet.Cells[0, 0].Text = "LET simplify the complex formula";
// merge cells
worksheet.Cells[0, 0, 0, 3].Merge(true);
worksheet.Cells[0, 0].MergePolicy = MergePolicy.Always;
// set column widths
fpSpread1.AsWorkbook().ActiveSheet.Columns[2].ColumnWidth = 80;
fpSpread1.AsWorkbook().ActiveSheet.Columns[7].ColumnWidth = 100;

// Filter the data to show one person
// create formula
string formula = "LET(filterCriteria,H7,filteredRange,FILTER(B7:E14,B7:B14=filterCriteria),IF(ISBLANK(filteredRange),\" - \",filteredRange))";
// set data in cells
worksheet.Cells[3, 1].Text = "Filter the data to show one person";
// set formula in cells
worksheet.Cells[4, 1].Text = formula;
// merge cells
worksheet.Cells[3, 1, 3, 4].Merge(true);
worksheet.Cells[3, 1].MergePolicy = MergePolicy.Always;
worksheet.Cells[4, 1, 4, 12].Merge(true);
worksheet.Cells[4, 1].MergePolicy = MergePolicy.Always;

// Add Data to cells
worksheet.Cells[5, 1].Text = "Rep";
worksheet.Cells[5, 2].Text = "Region";
worksheet.Cells[5, 3].Text = "Product";
worksheet.Cells[5, 4].Text = "Profit";

worksheet.Cells[6, 1].Text = "Amy";
worksheet.Cells[6, 2].Text = "East";
worksheet.Cells[6, 3].Text = "Apple";
worksheet.Cells[6, 4].Value = 1.33;

worksheet.Cells[7, 1].Text = "Fred";
worksheet.Cells[7, 2].Text = "South";
worksheet.Cells[7, 3].Text = "Banana";
worksheet.Cells[7, 4].Value = 0.09;

worksheet.Cells[8, 1].Text = "Amy";
worksheet.Cells[8, 2].Text = "West";
worksheet.Cells[8, 3].Text = "Mango";
worksheet.Cells[8, 4].Value = 1.85;

worksheet.Cells[9, 1].Text = "Fred";
worksheet.Cells[9, 2].Text = "West";
worksheet.Cells[9, 3].Text = "";
worksheet.Cells[9, 4].Value = 0.82;

worksheet.Cells[10, 1].Text = "Fred";
worksheet.Cells[10, 2].Text = "West";
worksheet.Cells[10, 3].Text = "Banana";
worksheet.Cells[10, 4].Value = 1.25;

worksheet.Cells[11, 1].Text = "Amy";
worksheet.Cells[11, 2].Text = "East";
worksheet.Cells[11, 3].Text = "Apple";
worksheet.Cells[11, 4].Value = 0.72;


worksheet.Cells[12, 1].Text = "Rep";
worksheet.Cells[12, 2].Text = "Region";
worksheet.Cells[12, 3].Text = "Product";
worksheet.Cells[12, 4].Value = 0.72;

worksheet.Cells[13, 1].Text = "Fred";
worksheet.Cells[13, 2].Text = "North";
worksheet.Cells[13, 3].Text = "Mango";
worksheet.Cells[13, 4].Value = 0.54;

worksheet.Cells[6, 6].Text = "Rep";
worksheet.Cells[7, 6].Text = "Result";
worksheet.Cells[6, 7].Text = "Fred";
// set cell styling properties
fpSpread1.ActiveSheet.Cells[7, 6].BackColor = System.Drawing.Color.LightGreen;
fpSpread1.ActiveSheet.Cells[7, 6].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
fpSpread1.ActiveSheet.Cells[5, 1, 5, 4].BackColor = System.Drawing.Color.LightBlue;
fpSpread1.ActiveSheet.Cells[5, 1, 5, 4].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;

// Add dynamic array formula to cell
worksheet.Cells[7, 7].Formula2 = formula;

// Generate all weekday dates between 1st April and 15th April
// create formula
string formula1 = "LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))";
// create formatter 
string formatter = "[$-en-US]dd-mmm-yy;@";
// Add text to cell
worksheet.Cells[15, 1].Text = "Generate all weekday dates between April 1, 2020 and April 15, 2020";
// set cell styling properties
worksheet.Cells[15, 1, 15, 7].Merge(true);
worksheet.Cells[15, 1].MergePolicy = MergePolicy.Always;
// add formula to cell
worksheet.Cells[16, 1].Text = formula1;
// set cell styling properties
worksheet.Cells[16, 1, 16, 8].Merge(true);
worksheet.Cells[16, 1].MergePolicy = MergePolicy.Always;
// Add text to cell
worksheet.Cells[17, 1].Text = "Start";
worksheet.Cells[18, 1].Text = "End";
worksheet.Cells[17, 2].Text = new DateTime(2020, 4, 1).ToString();
worksheet.Cells[18, 2].Text = new DateTime(2020, 4, 15).ToString();
// set cell styling properties
fpSpread1.ActiveSheet.Cells[17, 1, 18, 1].BackColor = System.Drawing.Color.LightBlue;
fpSpread1.ActiveSheet.Cells[17, 1, 18, 1].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
// set formatter for cells
worksheet.Cells[17, 2].NumberFormat = formatter;
worksheet.Cells[18, 2].NumberFormat = formatter;
// Add text to cell
worksheet.Cells[17, 6].Text = "Result";
// set cell styling properties
fpSpread1.ActiveSheet.Cells[17, 6].BackColor = System.Drawing.Color.LightGreen;
fpSpread1.ActiveSheet.Cells[17, 6].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
// Add dynamic array formula to cell
worksheet.Cells[17, 7].Formula2 = formula1;
// set cell formatter
for (var i = 0; i < 11; i++)
{
    worksheet.Cells[i + 17, 7].NumberFormat = formatter;
}

The output of above code will look like below:

Version Available

This function is available in Spread for Windows Forms 14.0 or later.