LET function
PRO
The LET
function in Jspreadsheet Formulas Pro is a powerful tool that lets you perform a calculation within a formula and then use the outcome of that calculation multiple times within the same formula. You can do this by assigning a name to the calculation. This not only simplifies your formulas by reducing repetition, but also improves computational efficiency, especially with large and complex spreadsheets.
Documentation
Allows you to define a calculation inside a formula and use the result of that calculation throughout the formula by referencing the name you give to the calculation.
Category
Logical
Syntax
LET(name1, name_value1, [calculation_or_name2, name_value2], ...)
Parameter | Description |
---|---|
name1 |
The name you want to give to the calculation. Must be a valid cell reference or range name. |
name_value1 |
The value of the first name. |
[calculation_or_nameN] |
Optional: Additional names or expression for more calculations. |
[name_valueN] |
Optional: Value for the nth name. |
Behavior
The LET
function is used to define variables within a formula, simplifying complex formulas. The function takes a series of variables and a calculation that uses these variables. Here's how it handles various data types:
- Empty cells: If a variable is assigned to an empty cell,
LET
treats it as zero in numeric calculations. - Text:
LET
can handle text values. If a variable is assigned to a cell containing text, it treats it as a text string. However, if this variable is used in a numeric calculation, it results in an error. - Booleans:
LET
can handle boolean values. If a variable is assigned to a cell containing a boolean, it treats it as TRUE or FALSE. - Errors: If a variable is assigned to a cell containing an error, the
LET
function propagates this error through to the final result.
Common Errors
Error | Description |
---|---|
#VALUE! | This error occurs if a variable is used in an inappropriate context, such as trying to perform numeric calculations on text strings. |
#CALC! | This error occurs if the LET function attempts to calculate a formula that contains a circular reference. |
Best practices
- Always ensure that all the variables used in the
LET
function calculation are defined within the function itself to avoid the #NAME? error.- Use meaningful names for your variables. This makes your formulas easier to read and understand.
- Be careful when assigning variables to cells that may contain text or errors. If these variables are used in numeric calculations, it can result in errors.
- Avoid creating circular references within your
LET
function to prevent the #CALC! error.
Usage
A few examples using the LET function.
LET(x, A1 + B1) + LET(y, A2 + B2) returns the sum of the values in cells A1 through B2, with the intermediate results stored in the names x and y, respectively
LET(area, PI() * radius ^ 2) returns the area of a circle with radius defined elsewhere in the worksheet
LET(total_sales, SUM(A1:A10)) / LET(num_salespeople, COUNTA(B1:B10)) returns the average sales per salesperson for the range A1:A10, with the intermediate results stored in the names total_sales and num_salespeople, respectively.
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YjIxZmY2NTUzMmQ2NzI4YzYwMzFiZDVmNDY3NjI2MGVhNDQ0YjlmMGMxOTM3OTNjNzNjOGQxMzdjNTU5ODVmY2Y2OGFlODVlMGUyNjgyZDFlODRiODU5OTI1NWVhMjY5N2ZmMGM1MjI3NGEwMDc3NWY2MTlhMDczYTBmYTdlNmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Product",
"Units Sold",
"Price per Unit",
"Revenue Analysis"
],
[
"Laptops",
25,
899,
"=LET(total_revenue, B2*C2, avg_price, (C2+C3+C4)/3, total_revenue/avg_price)"
],
[
"Tablets",
18,
549
],
[
"Phones",
42,
299
],
[
"Total Units",
"=SUM(B2:B4)",
"Avg Price",
"=LET(prices, C2:C4, AVERAGE(prices))"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('YjIxZmY2NTUzMmQ2NzI4YzYwMzFiZDVmNDY3NjI2MGVhNDQ0YjlmMGMxOTM3OTNjNzNjOGQxMzdjNTU5ODVmY2Y2OGFlODVlMGUyNjgyZDFlODRiODU5OTI1NWVhMjY5N2ZmMGM1MjI3NGEwMDc3NWY2MTlhMDczYTBmYTdlNmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Product",
"Units Sold",
"Price per Unit",
"Revenue Analysis"
],
[
"Laptops",
25,
899,
"=LET(total_revenue, B2*C2, avg_price, (C2+C3+C4)/3, total_revenue/avg_price)"
],
[
"Tablets",
18,
549
],
[
"Phones",
42,
299
],
[
"Total Units",
"=SUM(B2:B4)",
"Avg Price",
"=LET(prices, C2:C4, AVERAGE(prices))"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('YjIxZmY2NTUzMmQ2NzI4YzYwMzFiZDVmNDY3NjI2MGVhNDQ0YjlmMGMxOTM3OTNjNzNjOGQxMzdjNTU5ODVmY2Y2OGFlODVlMGUyNjgyZDFlODRiODU5OTI1NWVhMjY5N2ZmMGM1MjI3NGEwMDc3NWY2MTlhMDczYTBmYTdlNmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Product",
"Units Sold",
"Price per Unit",
"Revenue Analysis"
],
[
"Laptops",
25,
899,
"=LET(total_revenue, B2*C2, avg_price, (C2+C3+C4)/3, total_revenue/avg_price)"
],
[
"Tablets",
18,
549
],
[
"Phones",
42,
299
],
[
"Total Units",
"=SUM(B2:B4)",
"Avg Price",
"=LET(prices, C2:C4, AVERAGE(prices))"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YjIxZmY2NTUzMmQ2NzI4YzYwMzFiZDVmNDY3NjI2MGVhNDQ0YjlmMGMxOTM3OTNjNzNjOGQxMzdjNTU5ODVmY2Y2OGFlODVlMGUyNjgyZDFlODRiODU5OTI1NWVhMjY5N2ZmMGM1MjI3NGEwMDc3NWY2MTlhMDczYTBmYTdlNmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Product",
"Units Sold",
"Price per Unit",
"Revenue Analysis"
],
[
"Laptops",
25,
899,
"=LET(total_revenue, B2*C2, avg_price, (C2+C3+C4)/3, total_revenue/avg_price)"
],
[
"Tablets",
18,
549
],
[
"Phones",
42,
299
],
[
"Total Units",
"=SUM(B2:B4)",
"Avg Price",
"=LET(prices, C2:C4, AVERAGE(prices))"
]
]
}]
});
}
}