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;.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
const spreadRef = ref(null);
let initSpread = function (spread) {
spreadRef.value = spread;
spread.options.allowDynamicArray = true;
initStyles(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();
}
let initStyles = function (spread) {
let introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 16px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
let introSecStyle = new GC.Spread.Sheets.Style();
introSecStyle.name = 'introSec';
introSecStyle.font = 'normal bold 12px Segoe UI';
introSecStyle.foreColor = "#000";
spread.addNamedStyle(introSecStyle);
let introGrayStyle = new GC.Spread.Sheets.Style();
introGrayStyle.name = 'introGray';
introGrayStyle.font = 'normal bold 12px Segoe UI';
introGrayStyle.foreColor = "gray";
spread.addNamedStyle(introGrayStyle);
let 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);
let formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
introStyle1.vAlign = 1;
spread.addNamedStyle(formulaStyle);
let 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);
let tableContentStyle = new GC.Spread.Sheets.Style();
tableContentStyle.name = 'tableContent';
tableContentStyle.font = "normal normal 14.7px Calibri";
tableContentStyle.hAlign = 1;
spread.addNamedStyle(tableContentStyle);
let sourceStyle = new GC.Spread.Sheets.Style();
sourceStyle.name = 'source';
sourceStyle.hAlign = 0;
sourceStyle.backColor = "#fce8ce";
spread.addNamedStyle(sourceStyle);
let resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
resultStyle.backColor = "#e2efda";
spread.addNamedStyle(resultStyle);
}
let initSheet1 = function (sheet) {
sheet.name('Use Case');
let 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);
let table1 = sheet.tables.add('Table1', 2, 1, 5, 4);
table1.style(GC.Spread.Sheets.Tables.TableThemes.medium7);
let table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "engineer", "Support Engineer");
let table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "start", "Start Date");
let table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, null, "Time with Company");
let 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"))');
let 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)');
let lineStyle = GC.Spread.Sheets.LineStyle.dotted;
let lineBorder = new GC.Spread.Sheets.LineBorder('rgb(143,193,104)', lineStyle);
let sheetArea = GC.Spread.Sheets.SheetArea.viewport;
sheet.getRange(8, 1, 1, 3).setBorder(lineBorder, { left: true, right: true, top: true, bottom: true }, sheetArea);
let 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))');
}
let initSheet2 = function (sheet) {
sheet.name('#1');
sheet.setValue(1, 1, 'LET basic usage');
sheet.setStyle(1, 1, 'intro');
// Sample pair varible
let 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
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
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
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);
}
let initSheet3 = function (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
let 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
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);
}
let initSheet4 = function (sheet) {
sheet.name('#3');
sheet.setValue(1, 1, 'LET nested');
sheet.setStyle(1, 1, 'intro');
// Always use the current scope varible
let 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
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);
}
let initSheet5 = function (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
let 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');
let 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);
formula = '=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))';
let 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 (let i = 0; i < 11; i ++) {
sheet.setFormatter(i + 17, 7, formatter);
}
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
