Live estimate cells

From docs/livecells/README.md

Live estimate cells (Wave 1): the workbook kit

Everything the owner needs to build the "Sortia Live Cells (Experimental)" template workbook by hand in about ten minutes, in the order to do it. Sheets exposes no API for named functions, so this workbook is the one piece of Wave 1 that code cannot ship (docs/RELEASE-2026-09-26-CONTRACT.md, row 5.5).

What is in this folder

test/qa-livecells-kit.test.js evaluates the formulas in NAMED-FUNCTIONS.md and both CSVs in Node against the spike's reference implementation, so the kit cannot drift from the measured numbers without a test saying so.

Build order

1. New spreadsheet, named Sortia Live Cells (Experimental), in the Sortia Drive.
2. Data > Named functions > Add new function, three times, from NAMED-FUNCTIONS.md. Paste each formula in one piece.
3. Tab "Setup": the words in SETUP.md. Name the seed cell SEED (Data > Named ranges) with 12345 in it.
4. Tab "Permits": File > Import > Upload example-permits.csv, "Replace current sheet", with "Convert text to numbers, dates and formulas" on. Name E2 as SEED if the Setup tab's SEED is not what ODDS should read (one SEED per workbook is the intent; the CSV's E2 is a label for where it lives).
5. Tab "Budget": the same with example-budget.csv.
6. Tab "Diagnose": the Diagnose table from SETUP.md.
7. Run the check sequence at the end of NAMED-FUNCTIONS.md. The Permits tab must show 0.7899 and 0.5301; the Budget tab 0.7932. Any other number is a mistyped constant.
8. Share: Anyone with the link, Viewer. Users copy it; they never edit it.

After the workbook exists

Set EXP_LIVECELLS_WORKBOOK_URL in app/Code.js to the workbook's copy URL, which is the sheet's URL with /edit replaced by /copy:

https://docs.google.com/spreadsheets/d/<the workbook id>/copy

The Settings section's "Copy the workbook" link is that URL; Drive's copy page opens in a new tab titled "Copy of Sortia Live Cells (Experimental)", which is the title Step 1 tells the person to expect. With the constant empty the section says, in one sentence, that the workbook is coming soon, keeps the toggle, and renders no steps; the steps appear only once the constant is set, so nothing dead ships. Then rebuild (npm run build), test, and the usual push and pin.

The public page

This kit is published at https://www.sortia.io/live-cells, generated by scripts/build-templates.js from the files in this folder (README, SETUP and NAMED-FUNCTIONS rendered from their Markdown, the two CSVs as tables), so the page cannot say something the kit does not. The page's "workbook link" line is derived at build time from whether EXP_LIVECELLS_WORKBOOK_URL is blank, so it flips with the constant on the next npm run build:templates. Edit the kit here, never the generated site/live-cells.html. test/qa-livecells-state.test.js pins the page, the sitemap and llms.txt entries, and the Settings section's blank-url and filled-url states.

Before turning the flag on for anyone but the owner

The strategy makes the import-flow test a gate: three users, one afternoon, unaided import in under two minutes. The protocol, the pass bar and the results table are in docs/spikes/2026-09-23-lambda-estimate-cells/IMPORT-FLOW-TEST.md.

Known open item

The sidebar's odds builder writes ODDS with as many estimate references as the person named (one to ten), while the named function has ten fixed estimate placeholders. See "Open item for the owner" in NAMED-FUNCTIONS.md before sharing the workbook.

From docs/livecells/SETUP.md

Live estimate cells: the setup tab

The words for the first tab of the "Sortia Live Cells (Experimental)" workbook. They are the same words the Settings section shows (app/HubPanel.html, hubExpRender), so a person reading either place reads one story. Keep the two in step: if the Settings copy changes, this tab changes.

The three steps (as Settings words them)

Step 1: Copy the template workbook. The EST and ODDS formulas live in this workbook. Copy the workbook. It opens in a new tab called "Copy of Sortia Live Cells (Experimental)". Come back here when it is done. Then in your own sheet: Data, then Named functions, then Import, pick that copy, Import. (Sheets does not let add-ons install these for you. This is the way.) Quick check: type =EST( in any empty cell. If EST appears in the autocomplete, the import worked.

Step 2: Try the example. The workbook has a worked model: ten cost estimates, one odds formula answering "do we stay under budget?" Change any estimate and watch the odds move.

Step 3: Build your own. Type =EST(28, 10.5) (your mean, your spread) in any cells, then use the odds builder to write the =ODDS(...) formula for you.

The seed line

Same seed, same odds: send the sheet to a colleague and they will get your numbers exactly.

The cell next to this line holds 12345 and is named SEED. Change it and every odds cell re-rolls. Put it back and every odds cell comes back to the same number.

The click count

Counted from the guide's own steps, one click per named action. Typing is not a click.

To the magic moment (an odds cell that moves when you change an estimate), inside the copied workbook:

Then type a new number into any estimate. 5 clicks to the magic moment, against the bar of 7 the strategy sets.

To live odds in your own sheet (Step 1's import, then Step 3):

11 clicks to the named functions being in your own sheet. Steps 9 to 11 are the file picker's own; confirm them on the first live run and correct this table if the picker asks for more or fewer. After that, the odds builder is: click the destination cell, name the estimate cells, pick how to combine them and what they must be, type the target, press Write my formula.

Diagnose: what you see, what to do

The same three checks the Settings section's Diagnose button runs, written as symptoms.

If none of these matches, the experiment is unsupported and may change: say what you saw through the Sortia feedback link, and go back to the run button, which is unchanged.

From docs/livecells/NAMED-FUNCTIONS.md

Live estimate cells: the named-function definitions

The three named functions the "Sortia Live Cells (Experimental)" workbook carries, exactly as the Phase 0 spike measured them (docs/spikes/2026-09-23-lambda-estimate-cells/: verdict.md, benchmark.md, spike2-script.js, ref2-node.js). Sheets has no API for named functions, so these are typed once, by hand, into Data > Named functions > Add new function in the template workbook, and every user imports them from there.

The three anchors these definitions must reproduce, at seed 12345 with 10,000 trials over ten =EST(28, 10.5) cells:

• odds that the largest of the ten is at most 49: 0.7899
• odds that the sum of the ten minus the seventh is at least 250 (the unsimulation check): 0.5301

test/qa-livecells-kit.test.js reads the formulas below out of this file, evaluates the same hash and inverse normal in Node (the spike's ref2-node.js), and fails if either anchor drifts. Change a constant here and the test says so.

Before you type anything: the rules the spike found

1. Keep the hash and NORMINV in array arithmetic. Each estimate's 10,000-trial vector is built by one ARRAYFORMULA of array arithmetic; the MAP LAMBDA only combines ten scalars per trial. Shapes that do the scalar work inside the LAMBDA hit a calculation cap near 50,000 scalar evaluations per formula and show #ERROR! (benchmark.md, question 1).
2. No name may look like a cell reference. LET names, LAMBDA parameters and argument placeholders such as v1, m1, ref1 or mu1 are silently rewritten by Sheets and the cell goes blank with no error (benchmark.md, the trap). Every name below has no trailing digit. This is why the ten estimate placeholders are refa to refj, not ref1 to ref10.
3. Literal numeric arguments only. =EST(28, 10.5) is read back out of the formula text with a regular expression. =EST(B3, B4) cannot be read that way and is out of scope for this experiment. Plain decimals: 1e3 is not read either.
4. The dialog's formula field is a rich editor. It altered a typed regular expression in the spike (probe P9). Paste each formula in one piece, then read it back with =FT(cell) on a test cell before trusting it.
5. The seed lives in a named cell called SEED, default 12345. A named range may not travel with a named-function import (strategy audit, 15.6 item 4), so ODDS reads IFERROR(SEED, 12345): a sheet with no SEED cell runs at 12345, a sheet that names one uses it. Verify this on the first live import; if IFERROR does not catch the missing name, the fallback is to tell users to name any cell SEED (Data > Named ranges).

EST(mean, sd)

The estimate. The cell shows its mean as an ordinary number; the spread rides in the formula text, where ODDS reads it back.

Named functions dialog:

Formula definition:

=mean

Argument descriptions (the second page of the dialog):

Measured: =EST(28, 10.5) reads as 28; an sd-only edit that leaves the shown value unchanged still recomputes every ODDS cell that reads it, because the dependency is on the formula text (benchmark.md, question 2). The Apps Script custom-function spelling of the same thing costs about 1.8 s per edit and is the one to avoid.

ODDS(fn, ref1..ref10, op, target)

The odds. For each of the ten estimate cells it reads the formula text (FORMULATEXT sees through a named-function parameter: probes P2 and P8), pulls the mean and sd out with REGEXEXTRACT, rebuilds that estimate's 10,000-trial vector from the same (trial index, estimate id) pair, combines the ten vectors trial by trial with the caller's LAMBDA, and returns the share of trials where the result meets op target.

Because the same (trial index, estimate id) pair gives the same draw in every ODDS cell, "the total minus the seventh" subtracts exactly the draws that were added. That is what the 0.5301 anchor checks.

Named functions dialog:

The placeholders refa to refj are the ref1 to ref10 of the design; the letters are there because a placeholder with a trailing digit parses as a cell reference (rule 2 above).

Formula definition:

=ARRAYFORMULA(LET(n, 10000, s, IFERROR(SEED, 12345), t, target, i, SEQUENCE(n),
  g, LAMBDA(txt, k, LET(
       m, VALUE(REGEXEXTRACT(txt, "\(\s*(-?[0-9.]+)\s*,")),
       sd, VALUE(REGEXEXTRACT(txt, ",\s*(-?[0-9.]+)\s*\)")),
       a, MOD(i*2499997 + k*1800451 + s*2299603, 7450589),
       b, MOD(a*a + 7450581, 7450589),
       c, MOD(b*a + 4658, 7450589),
       NORMINV((c + 0.5)/7450589, m, sd))),
  ea, g(FORMULATEXT(refa), 1), eb, g(FORMULATEXT(refb), 2), ec, g(FORMULATEXT(refc), 3),
  ed, g(FORMULATEXT(refd), 4), ee, g(FORMULATEXT(refe), 5), ef, g(FORMULATEXT(reff), 6),
  eg, g(FORMULATEXT(refg), 7), eh, g(FORMULATEXT(refh), 8), ei, g(FORMULATEXT(refi), 9),
  ej, g(FORMULATEXT(refj), 10),
  res, MAP(ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, fn),
  COUNTIF(res, op & t)/n))

This is the benchmark's question 2 consumer (benchmark.md, "The consumer formula, named spelling") with three substitutions and nothing else: the ten $A$1 to $A$10 references become the ten placeholders, the $D$1 seed cell becomes the SEED named cell with its 12345 default, and the hand-written LAMBDA(p, q, ..., MAX(...)) becomes the fn parameter, which MAP calls with the ten scalars of each trial. The hash constants (2499997, 1800451, 2299603, 7450589, 7450581, 4658, the +0.5), the trial count (10,000) and the two regular expressions are as measured.

Argument descriptions:

Measured (benchmark.md, question 2, named spelling): build 0.5 s; an estimate edit settles in about 0.9 s; a seed edit 0.9 s; a target edit 0.6 s. One ODDS cell is inside the 2 s "feels live" budget; five are; ten are not (question 3), which is where the five-per-sheet limit in Settings and Diagnose comes from.

Open item for the owner, found while writing this kit. The sidebar's odds builder (app/HubPanel.html, hubExpFormula) writes an ODDS call with as many estimate references as the person named, one to ten, and a LAMBDA with the same number of parameters. A named function with thirteen placeholders needs all thirteen every time. Before the workbook is shared, confirm in Sheets what a call with fewer arguments does; if it is refused, either the builder has to pad the call to ten references (pointing the spare ones at any EST cell and ignoring them in the LAMBDA is not sound, since MAP still needs ten arrays) or the workbook needs ODDS1 to ODDS10 variants, or the Settings copy has to say "exactly ten estimate cells". The permits and budget examples in this folder use ten on purpose.

FT(c)

The helper. Shows the formula text of a cell, so a person can see the stored spec behind an ordinary-looking number (=FT(A1) shows =EST(28, 10.5)) and so the import can be checked without the odds machinery. Probe P8 proved a named-function parameter keeps its reference.

Named functions dialog:

Formula definition:

=FORMULATEXT(c)

Argument descriptions:

The SEED cell

On the setup tab of the workbook, one cell holds 12345 and is named SEED (Data > Named ranges). The setup tab says, in one line: "Same seed, same odds: send the sheet to a colleague and they will get your numbers exactly." Changing the number re-rolls every vector in every ODDS cell at once; the benchmark measured that edit at 0.9 s for one ODDS cell.

Checking the typed definitions

After typing the three functions into the template workbook:

1. In an empty tab, put =EST(28, 10.5) in A1 to A10. Each shows 28.
2. Put =FT(A1) in C1. It shows =EST(28, 10.5). If it shows anything else, the EST formula was altered by the editor.
3. Put in F1: =ODDS(LAMBDA(p, q, r, w, x, y, z, aa, bb, cc, MAX(p, q, r, w, x, y, z, aa, bb, cc)), A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, "<=", 49). With no SEED cell, or SEED at 12345, it shows 0.7899.
4. Put in F2: =ODDS(LAMBDA(p, q, r, w, x, y, z, aa, bb, cc, (p + q + r + w + x + y + z + aa + bb + cc) - z), A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, ">=", 250). It shows 0.5301.
5. Change A1 to =EST(28.1, 10.5). F1 shows 0.7892. Back to 28: 0.7899 again.

Any other number means a constant was mistyped. The full expected sequence for the A1 edits is in benchmark.md (0.7892, 0.7881, 0.7879 for means 28.1 to 28.3).