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

• 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">
  /**
   * 480. 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.
• Per-cell custom borders (KPI) - Editable KPI scorecard. spreadsheetLayout paints spreadsheet-style per-edge custom borders via an absolute-positioned overlay (no border-collapse conflicts). Edit any quarter or target - the borders re-derive: green double = beat target, blue solid = hit, amber dotted = near miss, red dashed = bad miss; row champion gets a colored full frame.
• Cell merging (spreadsheet shell) - A real invoice rendered on an Excel-style shell: A / B / C / D / E column letters across the top, row numbers down the left. Brand band, bill-from / bill-to address blocks, meta block, line items, totals, notes, signatures - all assembled from MergeSpec + CellBorderSpec. Editable Qty / Rate / addresses / notes; totals recompute live.
• HyperFormula integration - Full HyperFormula engine wired into the grid as a peer-optional dep. Editable spreadsheet with A1-style cell refs, dozens of formulas across math (SUM / SUMIF), lookup (VLOOKUP / INDEX-MATCH), text (CONCAT / UPPER), date (TODAY / DATEDIF), logical (IF nests), financial (PMT / IRR / NPV), statistical (AVERAGE / MAX / RANK).