Syntax
Argument
Description
name1
(Required) The first name to assign. Must start with a letter. Cannot be the output of a formula or conflict with range syntax.
name_value1
(Required) The value that is assigned to name1.
calculation_or_name2
(Required) One of the following: A calculation that uses all names within the LET function. This must be the last argument in the LET function.A second name to assign to a second name_value. If a name is specified, name_value2 and calculation_or_name3 become required.
name_value2
(Optional) The value that is assigned to calculation_or_name2.
calculation_or_name3
(Optional) One of the following: A calculation that uses all names within the LET function. The last argument in the LET function must be a calculation.A third name to assign to a third name_value. If a name is specified, name_value3 and calculation_or_name4 become required.
Usage notes
The LET function is meant to make it easier to write more complex formulas, by making it possible to declare and assign values to variables inside a formula. Once a variable is named it can be assigned a static value, or a value based on a calculation. This allows a formula to refer to a variable by name as many times as needed, while the value of the variable is assigned in one place only.
Variables are named and assigned values in pairs (name1/value1, name2/value2, etc). LET can handle up to 126 name/value pairs, But only the first name/value pair is required. The calculation used to return a final result from LET always appears as the last argument to the function.
Benefits
Improved Performance
If you write the same expression multiple times in a formula, Excel calculated that result multiple times. LET allows you to call the expression by name and for Excel to calculate it once.
Easy Reading and Composition
No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.
Traversal
LET function is a very convenient way for traversal. The local variables will act as "the each one".
But please open the dynamic array mode by spread.options.allowDynamicArray = true;.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initStyles(spread);
initSpread(spread);
};
function initSpread(spread) {
spread.setSheetCount(5);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
initSheet2(spread.getSheet(1));
initSheet3(spread.getSheet(2));
initSheet4(spread.getSheet(3));
initSheet5(spread.getSheet(4));
spread.resumeCalcService();
spread.resumePaint();
}
function initStyles(spread) {
var introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 16px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
var introSecStyle = new GC.Spread.Sheets.Style();
introSecStyle.name = 'introSec';
introSecStyle.font = 'normal bold 12px Segoe UI';
introSecStyle.foreColor = "#000";
spread.addNamedStyle(introSecStyle);
var introGrayStyle = new GC.Spread.Sheets.Style();
introGrayStyle.name = 'introGray';
introGrayStyle.font = 'normal bold 12px Segoe UI';
introGrayStyle.foreColor = "gray";
spread.addNamedStyle(introGrayStyle);
var introStyle1 = new GC.Spread.Sheets.Style();
introStyle1.name = 'intro1';
introStyle1.font = 'normal bold 14px Calibri';
introStyle1.hAlign = 0;
introStyle1.vAlign = 1;
introStyle1.foreColor = "#172b4d";
spread.addNamedStyle(introStyle1);
var formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
introStyle1.vAlign = 1;
spread.addNamedStyle(formulaStyle);
var tableHeaderStyle = new GC.Spread.Sheets.Style();
tableHeaderStyle.name = 'tableHeader';
tableHeaderStyle.font = "normal bold 14.7px Calibri";
tableHeaderStyle.hAlign = 1;
tableHeaderStyle.backColor = "#d9e1f2";
spread.addNamedStyle(tableHeaderStyle);
var tableContentStyle = new GC.Spread.Sheets.Style();
tableContentStyle.name = 'tableContent';
tableContentStyle.font = "normal normal 14.7px Calibri";
tableContentStyle.hAlign = 1;
spread.addNamedStyle(tableContentStyle);
var sourceStyle = new GC.Spread.Sheets.Style();
sourceStyle.name = 'source';
sourceStyle.hAlign = 0;
sourceStyle.backColor = "#fce8ce";
spread.addNamedStyle(sourceStyle);
var resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
resultStyle.backColor = "#e2efda";
spread.addNamedStyle(resultStyle);
}
function initSheet1(sheet) {
sheet.name('Use Case');
var table1Source = {
name: 'Support Engineer Employment Length',
data: [
{ engineer: 'Bob', start: new Date(2014,4,25), salary: 2790 },
{ engineer: 'Jim', start: new Date(2019,6,20), salary: 2216 },
{ engineer: 'Kevin', start: new Date(2017,2,1), salary: 2498 },
{ engineer: 'Sarah', start: new Date(2020,6,14), salary: 1989 }
]
};
sheet.addSpan(1, 1, 1, 4);
sheet.setValue(1, 1, table1Source.name);
sheet.getCell(1, 1).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(1, 132);
sheet.setColumnWidth(2, 87);
sheet.setColumnWidth(3, 147);
sheet.setColumnWidth(4, 63);
var table1 = sheet.tables.add('Table1', 2, 1, 5, 4);
table1.style(GC.Spread.Sheets.Tables.TableThemes.medium7);
var table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "engineer", "Support Engineer");
var table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "start", "Start Date");
var table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, null, "Time with Company");
var table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, "salary", "Salary");
table1.autoGenerateColumns(false);
table1.bind([table1Column1, table1Column2, table1Column3, table1Column4], 'data', table1Source);
table1.setColumnDataFormula(2, '=LET(time,(YEAR(TODAY())-YEAR([@[Start Date]])),SWITCH(TRUE,time>5,"5+ years",time>=1,"1-4 years",time=0,"<= 1 Year"))');
var style = new GC.Spread.Sheets.Style();
style.backColor = 'rgb(112,173,71)';
style.foreColor = 'rgb(255,255,255)';
style.font = 'normal bold 14px Calibri';
sheet.setStyle(8, 1, style);
sheet.setValue(8, 1, 'Salary Calculations');
sheet.getCell(8, 2).backColor('rgb(227,239,218)');
sheet.getCell(8, 3).backColor('rgb(227,239,218)');
var lineStyle = GC.Spread.Sheets.LineStyle.dotted;
var lineBorder = new GC.Spread.Sheets.LineBorder('rgb(143,193,104)', lineStyle);
var sheetArea = GC.Spread.Sheets.SheetArea.viewport;
sheet.getRange(8, 1, 1, 3).setBorder(lineBorder, { left: true, right: true, top: true, bottom: true }, sheetArea);
var combo = new GC.Spread.Sheets.CellTypes.ComboBox();
combo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
combo.items([
{ text: 'Min', value: 1 },
{ text: 'Max', value: 2 },
{ text: 'Average', value: 3 }
]);
sheet.setCellType(8, 2, combo, GC.Spread.Sheets.SheetArea.viewport);
sheet.setValue(8, 2, 3);
sheet.setFormula(8, 3, '=LET(data,Table1[Salary],calc,C9,aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate, data))');
}
function initSheet2(sheet) {
sheet.name('#1');
sheet.setValue(1, 1, 'LET basic usage');
sheet.setStyle(1, 1, 'intro');
// Sample pair varible
var formula = '=LET(x,2,x+3)';
sheet.setValue(3, 1, 'Sample pair varible');
sheet.setStyle(3, 1, 'introSec');
sheet.setValue(4, 1, formula);
sheet.setStyle(4, 1, 'formula');
sheet.setValue(5, 1, 'Result');
sheet.setStyle(5, 1, 'result');
sheet.setFormula(5, 2, formula);
// Multiple pair varibles
var formula = '=LET(x,1,y,2,z,3,x+y+z=x*y*z)';
sheet.setValue(7, 1, 'Multiple pair varibles');
sheet.setStyle(7, 1, 'introSec');
sheet.setValue(8, 1, formula);
sheet.setStyle(8, 1, 'formula');
sheet.setValue(9, 1, 'Result');
sheet.setStyle(9, 1, 'result');
sheet.setFormula(9, 2, formula);
// Invoked formula
var formula = '=LET(x,1,y,2,SUM(x,y))';
sheet.setValue(11, 1, 'Invoked formula');
sheet.setStyle(11, 1, 'introSec');
sheet.setValue(12, 1, formula);
sheet.setStyle(12, 1, 'formula');
sheet.setValue(13, 1, 'Result');
sheet.setStyle(13, 1, 'result');
sheet.setFormula(13, 2, formula);
// Dynamic Array
var formula = '=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))';
sheet.setValue(15, 1, 'Dynamic Array');
sheet.setStyle(15, 1, 'introSec');
sheet.setValue(16, 1, formula);
sheet.setStyle(16, 1, 'formula');
sheet.setValue(17, 1, 'Range');
sheet.setStyle(17, 1, 'source');
sheet.setArray(17, 2, [[1,2,3]]);
sheet.setValue(18, 1, 'Result');
sheet.setStyle(18, 1, 'result');
sheet.setFormula(18, 2, formula);
}
function initSheet3(sheet) {
sheet.name('#2');
sheet.setValue(1, 1, 'LET local varible & custom name');
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, 'This sheet includes a custom name "user" which be assigned "Michael" value');
sheet.setStyle(2, 1, 'introGray');
sheet.addCustomName('user', '="Michael"');
sheet.setValue(3, 1, 'user');
sheet.setStyle(3, 1, 'source');
sheet.setFormula(3, 2, '=user');
// Always use let local varible first
var formula = '=LET(user,"Ivy","The actual user is: "&user)';
sheet.setValue(5, 1, 'Always use let local varible first');
sheet.setStyle(5, 1, 'introSec');
sheet.setValue(6, 1, formula);
sheet.setStyle(6, 1, 'formula');
sheet.setValue(7, 1, 'Result');
sheet.setStyle(7, 1, 'result');
sheet.setFormula(7, 2, formula);
// Use custom name if not avalible local varible
var formula = '=LET(user,user,"The actual user is: "&user)';
sheet.setValue(9, 1, 'Use custom name if not avalible local varible');
sheet.setStyle(9, 1, 'introSec');
sheet.setValue(10, 1, formula);
sheet.setStyle(10, 1, 'formula');
sheet.setValue(11, 1, 'Result');
sheet.setStyle(11, 1, 'result');
sheet.setFormula(11, 2, formula);
}
function initSheet4(sheet) {
sheet.name('#3');
sheet.setValue(1, 1, 'LET nested');
sheet.setStyle(1, 1, 'intro');
// Always use the current scope varible
var formula = '=LET(var,"First scope",LET(var,"Second scope",var))';
sheet.setValue(3, 1, 'Always use the current scope varible');
sheet.setStyle(3, 1, 'introSec');
sheet.setValue(4, 1, formula);
sheet.setStyle(4, 1, 'formula');
sheet.setValue(5, 1, 'Result');
sheet.setStyle(5, 1, 'result');
sheet.setFormula(5, 2, formula);
// Use the top scope varible if not found the avalible varible in current scope
var formula = '=LET(var,"First scope",LET(var,var,var&" [from the second scope]"))';
sheet.setValue(7, 1, 'Use the top scope varible if not found the avalible varible in current scope');
sheet.setStyle(7, 1, 'introSec');
sheet.setValue(8, 1, formula);
sheet.setStyle(8, 1, 'formula');
sheet.setValue(9, 1, 'Result');
sheet.setStyle(9, 1, 'result');
sheet.setFormula(9, 2, formula);
}
function initSheet5(sheet) {
sheet.name('#4');
sheet.setValue(1, 1, 'LET simplify the complex formula');
sheet.setStyle(1, 1, 'intro');
// Filter the data to show one person
var formula = '=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),"-",filteredRange))';
sheet.setValue(3, 1, 'Filter the data to show one person');
sheet.setStyle(3, 1, 'introSec');
sheet.setValue(4, 1, formula);
sheet.setStyle(4, 1, 'formula');
var data = [
["Rep", "Region", "Product", "Profit"],
["Amy", "East", "Apple", 1.33 ],
["Fred", "South", "Banana", 0.09],
["Amy", "West", "Mango", 1.85],
["Fred", "North", null, 0.82],
["Fred", "West", "Banana", 1.25],
["Amy", "East", "Apple", 0.72],
["Fred", "North", "Mango", 0.54]
];
sheet.setStyle(5, 1, 'tableHeader');
sheet.setStyle(5, 2, 'tableHeader');
sheet.setStyle(5, 3, 'tableHeader');
sheet.setStyle(5, 4, 'tableHeader');
sheet.setArray(5, 1, data);
sheet.setValue(6, 6, 'Rep');
sheet.setStyle(6, 6, 'source');
sheet.setValue(7, 6, 'Result');
sheet.setStyle(7, 6, 'result');
sheet.setValue(6, 7, 'Fred');
sheet.setFormula(7, 7, formula);
// Generate all dates between May 1, 2020 and May 15, 2020
sheet.setColumnWidth(2, 72);
sheet.setColumnWidth(7, 72);
var formula = '=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))';
var formatter = '[$-en-US]dd-mmm-yy;@';
sheet.setValue(15, 1, 'Generate all dates between May 1, 2020 and May 15, 2020');
sheet.setStyle(15, 1, 'introSec');
sheet.setValue(16, 1, formula);
sheet.setStyle(16, 1, 'formula');
sheet.setValue(17, 1, 'Start');
sheet.setStyle(17, 1, 'tableHeader');
sheet.setValue(18, 1, 'End');
sheet.setStyle(18, 1, 'tableHeader');
sheet.setValue(17, 2, new Date(2020, 4, 1));
sheet.setValue(18, 2, new Date(2020, 4, 15));
sheet.setFormatter(17, 2, formatter);
sheet.setFormatter(18, 2, formatter);
sheet.setValue(17, 6, 'Result');
sheet.setStyle(17, 6, 'result');
sheet.setFormula(17, 7, formula);
for (var i = 0; i < 11; i ++) {
sheet.setFormatter(i + 17, 7, formatter);
}
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
