Engineering, statistics, and the locale you type in

Four function families the engine gained at once, worked through a real example of each: a bearing from ATAN2 and DEGREES, a register mask in binary and hex with BITAND, an orchard queried through Excel's criteria-block grammar with DSUM, DCOUNT, DAVERAGE and DGET, and NORM.DIST with its inverse, a confidence interval and the chi-squared and t tails. The picker switches how numbers and formulas are SPELLED: German shows 1.234,5 and =ROUND(A1/3; 2) while the document still stores 1234.5 and a comma, so the file opens anywhere. Column H is drawn as checkboxes whose ticks an ordinary COUNTIF counts. (requires @svgrid/enterprise)

A live, editable Svelte 5 data grid example from the SvGrid gallery (Spreadsheet). See the SvGrid documentation for the full API.

About this example

Trigonometry, engineering, database and statistical-distribution functions in a Svelte 5 spreadsheet, with a worked example of each: a bearing from ATAN2 and DEGREES, a register mask in binary and hex with BITAND, an orchard queried through Excel's criteria-block grammar with DSUM, DCOUNT, DAVERAGE and DGET, and NORM.DIST with its inverse, a confidence interval and the chi-squared and t tails. A locale picker switches how numbers and formulas are spelled while the document keeps the invariant spelling, and a column of checkbox cells is counted by an ordinary COUNTIF. Enterprise, in @svgrid/enterprise.

Three things the spreadsheet gained at once, on one sheet.

FUNCTIONS. Four families the engine had none of: trigonometry, engineering, database and the statistical distributions. The blocks below work a real example of each - a bearing from two offsets, a register mask in three bases, an orchard queried through Excel's criteria-block grammar, and a normal distribution with its inverse and a confidence interval.

THE LOCALE. The picker at the top switches how numbers and formulas are SPELLED. Choose German and the formula bar reads =ROUND(A1/3; 2) and a cell shows 1,5; type 3,25 into a cell and it is the number. What the document stores never changes: it is always =ROUND(A1/3, 2) and 1.5, which is why a file written here opens anywhere.

CONTROLS. Column H is drawn as checkboxes, and the count beside it is an ordinary COUNTIF(H4:H9, TRUE). A control is a rendering, never a second source of truth: the tick is in the cell, so the formula sees it, undo undoes it, and the file carries a plain boolean.

Try: switch the locale and click into B12 to watch the separator change while the value does not. Tick a box in H and watch H11 count it. Select A4:A9 and use Data > Group to fold the orchard away.

Imports, features and API used

Imports: @svgrid/enterprise, @svgrid/enterprise/sheet

Frequently asked questions

Why does ATAN2 take its arguments the other way round?

Because Excel does. Excel's ATAN2 is ATAN2(x_num, y_num), the opposite order from every C-family atan2, so =ATAN2(-1, 1) is three quarters of pi. Getting it wrong leaves the symmetric cases looking right and flips the rest, which is why it is worth knowing before a model depends on it.

What is the criteria block the database functions read?

Excel's advanced-filter grammar. The block's first row is header names matched against the database's own; every row under it is one alternative; the conditions across a row are ANDed and the rows are ORed. A blank criteria cell places no condition, so a block that is nothing but headers matches every record. DGET wants exactly one match and reports #VALUE! for none and #NUM! for more.

Does switching locale change what the file contains?

No, and that is the point. The document always stores the invariant spelling: =SUM(1.5, A1) and 1234.5, whatever the locale. The culture is put on when a formula is shown for editing and taken off when one is typed, so a file written under a German locale opens under an English one and means the same thing.

Can a formula see a checkbox cell?

Yes, because the tick is the cell. A checkbox is a rendering, not a second source of truth: it is ticked because its cell reads TRUE, so =COUNTIF(H4:H9, TRUE) counts the ticks, undo undoes one and the file carries an ordinary boolean. Excel's own cell checkbox works the same way.

Which old statistical names still work?

All of them, and three disagree with their modern partners in ways that are silent. CHIDIST is the right tail where CHISQ.DIST is the left; TDIST takes a tail count where T.DIST takes a flag; and TINV is two-tailed, so it matches T.INV.2T rather than T.INV. FDIST, FINV, CHIINV and NORMSDIST are likewise the right-tailed or cumulative-only forms.

Related documentation

Related articles

Source code (499-sheet-engineering-stats.svelte)

<script lang="ts">
  /**
   * 499. Engineering, statistics, and the locale you type in
   * --------------------------------------------------------
   * Three things the spreadsheet gained at once, on one sheet.
   *
   * FUNCTIONS. Four families the engine had none of: trigonometry,
   * engineering, database and the statistical distributions. The blocks
   * below work a real example of each - a bearing from two offsets, a
   * register mask in three bases, an orchard queried through Excel's
   * criteria-block grammar, and a normal distribution with its inverse
   * and a confidence interval.
   *
   * THE LOCALE. The picker at the top switches how numbers and formulas
   * are SPELLED. Choose German and the formula bar reads `=ROUND(A1/3; 2)`
   * and a cell shows `1,5`; type `3,25` into a cell and it is the number.
   * What the document stores never changes: it is always `=ROUND(A1/3, 2)`
   * and `1.5`, which is why a file written here opens anywhere.
   *
   * CONTROLS. Column H is drawn as checkboxes, and the count beside it is
   * an ordinary `COUNTIF(H4:H9, TRUE)`. A control is a rendering, never a
   * second source of truth: the tick is in the cell, so the formula sees
   * it, undo undoes it, and the file carries a plain boolean.
   *
   * Try: switch the locale and click into B12 to watch the separator
   * change while the value does not. Tick a box in H and watch H11 count
   * it. Select A4:A9 and use Data > Group to fold the orchard away.
   */
  import { SvSheet, createWorkbook, createSheetDocument } from '@svgrid/enterprise'
  import { newCellTypeId } from '@svgrid/enterprise/sheet'

  const LOCALES = [
    { id: 'en-US', label: 'English (1,234.5 and a comma)' },
    { id: 'de-DE', label: 'Deutsch (1.234,5 and a semicolon)' },
    { id: 'fr-FR', label: 'Français (1 234,5 and a semicolon)' },
  ]
  let locale = $state('en-US')

  const cells: string[][] = [
    ['Tree', 'Height', 'Yield', 'Profit', '', 'Measure', 'Value', 'Picked'],
    ['', '', '', '', '', '', '', ''],
    ['The orchard, queried the way Excel queries one', '', '', '', '', 'Trigonometry', '', ''],
    ['Apple', '18', '14', '105', '', 'Bearing, degrees', '=DEGREES(ATAN2(3, 4))', 'FALSE'],
    ['Pear', '12', '10', '96', '', 'Hypotenuse', '=SQRT(3^2 + 4^2)', 'FALSE'],
    ['Cherry', '13', '9', '105', '', 'Sine of 30 deg', '=SIN(RADIANS(30))', 'FALSE'],
    ['Apple', '14', '10', '75', '', 'Engineering', '', 'FALSE'],
    ['Pear', '9', '8', '76.8', '', 'Mask as binary', '=DEC2BIN(202, 10)', 'FALSE'],
    ['Apple', '8', '6', '45', '', 'Mask as hex', '=DEC2HEX(202)', 'FALSE'],
    ['', '', '', '', '', 'Bits kept', '=BITAND(202, 60)', ''],
    ['Tree', 'Height', '', '', '', 'Picked so far', '=COUNTIF(H4:H9, TRUE)', ''],
    ['Apple', '>10', '', '', '', 'Metres in a mile', '=CONVERT(1, "mi", "m")', ''],
    ['', '', '', '', '', 'Distributions', '', ''],
    ['Apples over ten', '=DSUM(A1:D9, "Profit", A11:B12)', 'DSUM', '', '', 'P(x < 42)', '=NORM.DIST(42, 40, 1.5, TRUE)', ''],
    ['How many', '=DCOUNT(A1:D9, "Profit", A11:B12)', 'DCOUNT', '', '', 'The x behind it', '=NORM.INV(G14, 40, 1.5)', ''],
    ['Their average', '=DAVERAGE(A1:D9, "Profit", A11:B12)', 'DAVERAGE', '', '', '95% interval', '=CONFIDENCE.NORM(0.05, 2.5, 50)', ''],
    ['The one cherry', '=DGET(A1:D9, "Profit", A18:A19)', 'DGET', '', '', 'Chi-squared tail', '=CHISQ.DIST.RT(18.307, 10)', ''],
    ['Tree', '', '', '', '', 'Student t, two tails', '=T.DIST.2T(1.96, 60)', ''],
    ['Cherry', '', '', '', '', 'Correlation', '=CORREL(B4:B9, C4:C9)', ''],
  ]

  const wb = createWorkbook([{ name: 'Sheet1', cells }])
  const doc = createSheetDocument({ workbook: wb })
  const sheet = doc.get('Sheet1')
  const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }

  // A fill and a font colour the DOCUMENT sets are used as they stand on
  // a dark theme, the way Excel uses them, so they go on in PAIRS. One
  // without the other is what a dark theme catches out: a dark colour
  // alone leaves the text on the sheet's own dark background, and a light
  // fill alone leaves the theme's light text on a light band.
  const HEADING = { bold: true, fill: '#e2e8f0', color: '#0f172a' } as const
  const CRITERIA = { bold: true, fill: '#f1f5f9', color: '#0f172a' } as const
  // Two tables sit side by side with an empty column E between them, so a
  // heading is styled over ITS OWN block rather than across the row: row 7
  // carries a section label on the right and orchard data on the left, and
  // banding the whole row put a heading behind "Apple 14 10 75".
  sheet.formats.set([[0, 0, 0, 3]], { ...HEADING }, at)
  sheet.formats.set([[0, 5, 0, 7]], { ...HEADING }, at)
  sheet.formats.set([[2, 0, 2, 3]], { ...HEADING }, at)
  for (const r of [2, 6, 12]) sheet.formats.set([[r, 5, r, 7]], { ...HEADING }, at)
  sheet.formats.set([[10, 0, 10, 1]], { ...CRITERIA }, at)
  sheet.formats.set([[17, 0, 17, 0]], { ...CRITERIA }, at)
  sheet.formats.set([[13, 1, 16, 1]], { numFmt: '#,##0.00' }, at)
  sheet.formats.set([[13, 6, 15, 6]], { numFmt: '0.0000' }, at)
  sheet.formats.set([[16, 6, 18, 6]], { numFmt: '0.0000' }, at)
  sheet.formats.set([[3, 6, 5, 6]], { numFmt: '0.0000' }, at)
  sheet.formats.set([[11, 6, 11, 6]], { numFmt: '#,##0.000' }, at)
  sheet.formats.set([[13, 2, 16, 2]], { color: '#64748b' }, at)
  sheet.widths.A = 170
  sheet.widths.F = 170
  sheet.widths.G = 150

  // Column H, the rows of the orchard, drawn as checkboxes. The cells
  // still hold TRUE and FALSE, which is what H11 counts.
  sheet.cellTypes = [{ id: newCellTypeId(), kind: 'checkbox', rects: [[3, 7, 8, 7]] }]
</script>

<div class="wrap">
  <label class="picker">
    <span>How numbers and formulas are spelled</span>
    <select bind:value={locale}>
      {#each LOCALES as option (option.id)}
        <option value={option.id}>{option.label}</option>
      {/each}
    </select>
  </label>
  <div class="sheet">
    <SvSheet document={doc} height="100%" rows={22} columns={9} localization={{ locale }} />
  </div>
</div>

<style>
  .wrap { display: flex; flex-direction: column; height: 100%; min-height: 0; gap: 10px; }
  .picker { display: flex; align-items: center; gap: 10px; font-size: 13px; }
  .picker select { font: inherit; padding: 4px 8px; }
  .sheet { flex: 1; min-height: 0; }
</style>

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.