LET and LAMBDA

About this example

LET and LAMBDA in a Svelte 5 spreadsheet, with the helpers that make a lambda worth writing. LET names a value inside a formula so it is written once and read by name, and a later binding can read an earlier one; LAMBDA is a function written in the sheet, bound by LET and called by name or called where it stands. MAP, BYROW, BYCOL, REDUCE, SCAN and MAKEARRAY put one to work over every cell, every row, every column, a fold, its running total, and an array built from its own indexes. They spill like any other array formula and go into the .xlsx under the prefix Excel stores them with. Enterprise, in @svgrid/enterprise.

The two modern Excel functions that turn a formula into something you can read, and the helpers that make a lambda worth writing.

LET(name, value, ..., calculation) names a value inside the formula, so it is written once and read by name. A later binding can read an earlier one. LAMBDA(parameter, ..., calculation) a function written in the sheet. Bind it with LET and call it by name, or call it where it stands: LAMBDA(x, x * 2)(21). MAP / BYROW / BYCOL / REDUCE / SCAN / MAKEARRAY what a lambda is for: every cell, every row, every column, a fold and its running total, and an array built from its own indexes.

They spill like any other array formula, and they go into the .xlsx under the _xlfn. prefix Excel stores them with, so a file written here opens there with the formulas intact rather than #NAME?.

Try: click F2, G2 and H2 to read the three spilled columns, then B9 for the LET behind the margin. Change a price in B2:B6 and watch every one of them follow.

Imports, features and API used

Imports: @svgrid/enterprise

Frequently asked questions

What does LET buy over repeating the expression?

It is written once and read by name, so the formula says what it means, and the value is worked out once rather than once per mention. A later binding can read an earlier one, which is what lets a long calculation be built in steps inside a single cell.

How is a LAMBDA called?

Three ways. Bind it with LET and call it by name, LET(double, LAMBDA(x, x * 2), double(21)); call it where it stands, LAMBDA(x, x * 2)(21); or hand it to MAP, BYROW, BYCOL, REDUCE, SCAN or MAKEARRAY, which call it for you. A lambda that is never called shows #CALC!, as in Excel.

Do these survive a save to .xlsx?

Yes. Excel stores functions added after the file format was frozen under an _xlfn. prefix, and worksheet-only ones under _xlfn._xlws.; the writer puts the prefixes on and the reader takes them off, so a file written here opens in Excel with the formulas intact rather than #NAME? in every cell.

Related documentation

• Formulas: from =SUM to LAMBDA
• Spreadsheet formulas

Related articles

• A Fill Handle (Drag to Fill) in SvGrid - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.
• Pivot Tables in Svelte - Summarize Data Without a Spreadsheet - Run a cross-tab pivot directly inside your Svelte app using @svgrid/enterprise createPivotModel - no Excel, no server-side aggregation, no stale exports.
• Spreadsheet-Style Cell Range Selection in SvGrid - How to enable drag-to-select cell ranges, read live selection state, and build a status-bar footer that sums and averages the selected values.

Source code (490-sheet-let-lambda.svelte)

<script lang="ts">
  /**
   * 490. LET and LAMBDA
   * -------------------
   * The two modern Excel functions that turn a formula into something you
   * can read, and the helpers that make a lambda worth writing.
   *
   *   LET(name, value, ..., calculation)
   *       names a value inside the formula, so it is written once and read
   *       by name. A later binding can read an earlier one.
   *   LAMBDA(parameter, ..., calculation)
   *       a function written in the sheet. Bind it with LET and call it by
   *       name, or call it where it stands: LAMBDA(x, x * 2)(21).
   *   MAP / BYROW / BYCOL / REDUCE / SCAN / MAKEARRAY
   *       what a lambda is for: every cell, every row, every column, a fold
   *       and its running total, and an array built from its own indexes.
   *
   * They spill like any other array formula, and they go into the .xlsx
   * under the `_xlfn.` prefix Excel stores them with, so a file written
   * here opens there with the formulas intact rather than #NAME?.
   *
   * Try: click F2, G2 and H2 to read the three spilled columns, then B9
   * for the LET behind the margin. Change a price in B2:B6 and watch every
   * one of them follow.
   */
  import { SvSheet, createWorkbook, createSheetDocument } from '@svgrid/enterprise'

  const rows: string[][] = [
    ['Product', 'Price', 'Cost', 'Sold', '', 'Margin %', 'Revenue', 'Running'],
    ['Licence', '1200', '300', '42', '', '=LET(margin, LAMBDA(p, c, (p - c) / p), MAP(B2:B6, C2:C6, margin))', '=BYROW(B2:B6 * D2:D6, LAMBDA(r, SUM(r)))', '=SCAN(0, B2:B6 * D2:D6, LAMBDA(acc, v, acc + v))'],
    ['Support', '480', '120', '85', '', '', '', ''],
    ['Training', '950', '410', '17', '', '', '', ''],
    ['Hosting', '260', '95', '130', '', '', '', ''],
    ['Add-ons', '140', '60', '64', '', '', '', ''],
    ['', '', '', '', '', '', '', ''],
    ['Written once, read by name', '', '', '', '', '', '', ''],
    ['Gross margin %', '=LET(revenue, SUMPRODUCT(B2:B6, D2:D6), cost, SUMPRODUCT(C2:C6, D2:D6), (revenue - cost) / revenue)', '', '', '', '', '', ''],
    ['Total revenue, folded', '=REDUCE(0, B2:B6 * D2:D6, LAMBDA(acc, v, acc + v))', '', '', '', '', '', ''],
    ['Called where it stands', '=LAMBDA(x, x * 2)(21)', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', ''],
    ['Built from its own indexes', '', '', '', '', '', '', ''],
    ['3 x 4 times table', '=MAKEARRAY(3, 4, LAMBDA(r, c, r * c))', '', '', '', '', '', ''],
  ]

  const wb = createWorkbook([{ name: 'Model', cells: rows }])
  const doc = createSheetDocument({ workbook: wb })
  const sheet = doc.get('Model')
  const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
  sheet.formats.set([[0, 0, 0, 7]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
  for (const r of [7, 12]) {
    sheet.formats.set([[r, 0, r, 7]], { bold: true, color: '#0f172a', fill: '#f1f5f9' }, at)
  }
  sheet.formats.set([[1, 1, 5, 2]], { numFmt: '$#,##0' }, at)
  // The three spilled columns beside the block, and the two totals under it.
  sheet.formats.set([[1, 5, 5, 5]], { numFmt: '0.0%' }, at)
  sheet.formats.set([[1, 6, 5, 7]], { numFmt: '#,##0' }, at)
  sheet.formats.set([[8, 1, 8, 1]], { numFmt: '0.0%' }, at)
  sheet.formats.set([[9, 1, 9, 1]], { numFmt: '#,##0' }, at)
  sheet.widths.A = 210
  sheet.widths.B = 120
  sheet.freeze = { rows: 1, cols: 1 }
</script>

<SvSheet document={doc} height="100%" rows={24} columns={9} />

View this example on GitHub

More Spreadsheet examples

• Spreadsheet + Ribbon bar - The whole Excel surface as one component, <SvSheet workbook={wb} />: a six-tab ribbon (Home, Insert, Formulas, Data, Review, View), the Name Box and fx bar, sheet tabs and the Sum / Average / Count status bar. Format cells, merge them, comment on them, validate what goes in, colour them by rule, filter the region, protect the sheet; every button is the same call as its shortcut, so Ctrl+B and the Bold button cannot drift. A two-sheet P&L with the formats travelling in the document.
• Spreadsheet + formulas - Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection.
• Blank sheet - just type - An empty Excel-style sheet on a plain <SvGrid>: column-letter headers (A..Z), a built-in 1..N row gutter, a name box + formula bar with a browsable function picker, gridlines, range selection and a fill handle. A real HyperFormula engine underneath: type a literal or a formula like =SUM(B2:D2) / =IF(...) and every dependent cell recalculates live. Drag a row or column border to resize; right-click for Cut / Copy / Paste / Clear.
• Excel keyboard shortcuts - The muscle memory a spreadsheet user arrives with: Ctrl+Arrow jumps to the edge of the data region (a run-boundary search, so it hops gaps rather than running to the end), Ctrl+Shift+Arrow extends there, Ctrl+A takes the current region then the sheet, Ctrl+Space / Shift+Space take the column or row, Ctrl+D / Ctrl+R fill, Ctrl+; stamps the date. One enableSheet() call. Ctrl+Z undoes a whole fill in one press, not one per cell.
• Formula bar + cell formats - The Excel cell experience: a formula bar showing the RAW text behind the active cell (the grid shows 1,234.50, the bar shows =B2*C2) with function autocomplete and signature hints, a Name Box that jumps to an address, and number formats that live on the CELL rather than the column. Ctrl+Shift+4 for currency, Ctrl+B to bold. The format store keys on row id, so sorting does not leave formatting behind on the old index.