Live estimate cells

Type =EST(28, 10.5) in a cell and it shows 28, an ordinary number. Build an odds formula over your estimate cells and the answer moves when an estimate changes. No run button.

Experimental, retired as a setting off by default unsupported and may change or disappear sheets that already use =EST and =ODDS keep working

This experiment is no longer offered in Settings. Estimates and the chance of a goal now live in the Uncertainty Sheet, which does the same job with a full panel for every result. No template workbook was published, so there is nothing to copy.

What it is

Every other answer Sortia gives you comes from pressing Run. This one does not. An estimate cell holds a number you can read and a spread you cannot see, and an odds cell over a handful of those estimates re-answers itself the moment you change one of them, the way an ordinary formula does. It is the smallest version of the whole product: estimates in, odds out, with no button in between.

It works through three named functions that live in a template workbook and that you import into your own sheet. Google gives add-ons no way to install named functions for you, which is the honest reason this needs a workbook at all rather than arriving with Sortia.

`EST(mean, sd)`

An estimate. The cell shows the mean as a plain number and keeps the spread in the formula for ODDS to read.

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

The odds that your estimates, combined the way you say, meet a target. Ten estimate cells, each holding =EST(mean, sd).

`FT(c)`

The formula text of a cell, so you can see the estimate behind a number.

Read this before you turn it on

It was an experiment, and it is no longer offered as a setting. A sheet that already holds these formulas keeps working. Nothing about your existing runs changes either way, and the run button is exactly where it was.

It is unsupported and it may change or disappear. We would rather say that than let you build a quarter's reporting on it.

It has real limits, and they are not going to surprise you later. English-locale sheets only, because the formulas read decimals out of your own text. Plain numbers as estimate inputs, such as =EST(28, 10.5), never cell references. Up to five live odds formulas on one sheet: each one rebuilds ten thousand runs for every estimate it watches, and past five the sheet stops feeling live, which defeats the point.

Getting it into your own sheet

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

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.

When something looks wrong

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

What you see	What it means	What to do
`#NAME?` in a cell holding `=EST(...)` or `=ODDS(...)`	The named functions are not in this sheet yet.	Do Step 1's import: Data, Named functions, Import, pick your copy of the workbook, Import all. Then type `=EST(` in an empty cell; EST in the autocomplete means it worked.
`#NAME?` in the ODDS cell while the EST cells show numbers	The import brought EST but not ODDS.	Import again and tick every function, or Import all.
`#N/A`, `#VALUE!` or `#REF!` in the ODDS cell	One of the ten estimate cells is not `=EST(number, number)`: it holds a plain number, a cell reference like `=EST(B3, B4)`, a blank, or a number written with an exponent.	Make each estimate cell `=EST(mean, sd)` with two plain decimals, such as `=EST(28, 10.5)`. Sortia's Diagnose lists how many cells are not plain numbers.
`#ERROR!` in the ODDS cell	The combining LAMBDA is doing heavy work per run, or the formula was edited into a shape Sheets caps.	Use the odds builder's Sum, Average, Min or Max. Keep the LAMBDA to arithmetic over its ten parameters.
Wrong number of arguments	ODDS takes a LAMBDA, exactly ten estimate cells, the test in quotes, and a target, and this one was typed by hand with a different count.	Let the odds builder write the formula. It always passes ten cells: if you name fewer, it repeats your first cell into the spare slots and combines only the cells you named, and it says so when it does. The examples in the workbook show the shape.
The odds do not move when you change an estimate	The ODDS formula does not point at that cell, or the cell holds a plain number rather than `=EST(...)`.	Read the ODDS formula: the ten cells after the LAMBDA are the ones it watches.
The sheet feels slow after an edit (more than about two seconds)	More than five odds formulas on this sheet. Each one rebuilds ten vectors of ten thousand runs.	Keep to five odds cells per sheet, one per question. Sortia's Diagnose counts them for you.
`=EST(28, 10.5)` is refused, or numbers show a comma where you expect a point	The sheet's locale uses a comma as the decimal separator.	This experiment is for English-locale sheets: File, Settings, Locale.
Two people see different odds from the same sheet	They are on different seeds.	Same seed, same odds. Check the SEED cell in both copies.

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.

Ten permits: the odds the last one arrives by the deadline

One of the two models the workbook opens with. The formulas are shown as text here; in the sheet each one is a live cell, and changing any estimate moves the odds.

Permit	Estimate (days)	Setting	Value	Question	Odds	Reference (seed 12345)
Permit 1	`=EST(28, 10.5)`	SEED (name this cell SEED)	12345	Odds the last permit arrives by day 49	`=ODDS(LAMBDA(p, q, r, w, x, y, z, aa, bb, cc, MAX(p, q, r, w, x, y, z, aa, bb, cc)), B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, "<=", E3)`	0.7899
Permit 2	`=EST(28, 10.5)`	Deadline (day)	49	Odds the ten days added up minus permit 7 reach 250 (the unsimulation check)	`=ODDS(LAMBDA(p, q, r, w, x, y, z, aa, bb, cc, (p + q + r + w + x + y + z + aa + bb + cc) - z), B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, ">=", E4)`	0.5301
Permit 3	`=EST(28, 10.5)`	Total target	250
Permit 4	`=EST(28, 10.5)`
Permit 5	`=EST(28, 10.5)`
Permit 6	`=EST(28, 10.5)`
Permit 7	`=EST(28, 10.5)`
Permit 8	`=EST(28, 10.5)`
Permit 9	`=EST(28, 10.5)`
Permit 10	`=EST(28, 10.5)`

A ten-line budget: the odds the project stays under the number

One of the two models the workbook opens with. The formulas are shown as text here; in the sheet each one is a live cell, and changing any estimate moves the odds.

Line item	Estimate (thousands)	Setting	Value	Question	Odds	Reference (seed 12345)
Design	`=EST(30, 6)`	SEED (name this cell SEED)	12345	Will the project stay under budget?	`=ODDS(LAMBDA(a, b, c, d, e, f, g, h, i, j, SUM(a, b, c, d, e, f, g, h, i, j)), B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, "<=", E3)`	0.7932
Permits	`=EST(12, 4)`	Budget (thousands)	270
Site preparation	`=EST(25, 8)`
Foundation	`=EST(40, 9)`
Framing	`=EST(45, 10)`
Roofing	`=EST(20, 5)`
Electrical	`=EST(18, 5)`
Plumbing	`=EST(16, 5)`
Finishes	`=EST(35, 12)`
Landscaping	`=EST(10, 4)`