Formula language

How DreamSheets formulas work — references by name, operators, error values, dates, and how the language compares to Excel.

DreamSheets formulas look and feel like Excel, with one deep difference: there are no cell addresses. Everything is referenced by name — tables, columns, tiles — so a formula reads like a sentence: SUM(Sales.Amount), @Price * @Qty, Revenue - Costs.

Formulas can live in four places:

Calculated columns — a formula that runs for every row of a table (@Price * @Qty).
Summary cells — pinned footer rows on a table (AVERAGE(Amount)).
Tiles — single-value KPI cards (SUM(Sales.Amount)).
Inline formulas — one-off values computed anywhere a formula editor appears.

A leading = is accepted out of spreadsheet habit, but it's optional.

References

You write	It means
`Amount`	The whole `Amount` column of the current table (used in aggregates: `SUM(Amount)`)
`@Amount`	The current row's cell of `Amount` — the only way to say "this row"
`Sales.Amount`	The `Amount` column of another table named `Sales`
`TaxRate`	The value of a tile named `TaxRate` (tables and tiles share one namespace)
`Amount:3`	Row 3 of the `Amount` column (the row index must be a plain number)
`Amount[ROW() - 1]`	A computed row index — brackets accept any expression
`Sales[2, 3]`	The cell at row 2, column 3 of `Sales` (same as `CELLBYINDEX(Sales, 2, 3)`)

The @ is the whole distinction, and it means the same thing everywhere: a name on its own is the column, `@name` is this row's cell. So per-row arithmetic needs it —

@Price * @Qty          one row's price times one row's quantity
Price * Qty            #TYPE! — two whole columns, not two values
@Amount / SUM(Amount)  this row's share of the total

@ needs a row to read, so it belongs in a calculated column or a single cell's formula. A tile or a summary cell has no row: there, a bare name is the only spelling, and the editor offers it in place of an @ you start typing.

Names with spaces or symbols

A table, column, or tile whose name isn't a plain identifier is written in single quotes — the same idea as Excel's 'My Sheet'!A1:

SUM('Q1 Sales'.'Unit Price')
@'Unit Price' * 2
'Grand Total' * 1.1

The rule is absolute and position-independent: '…' is always a name, "…" is always text. There is nothing to disambiguate, ever. When you rename a table, column, or tile, DreamSheets rewrites every formula that references it automatically.

Prefer identifier-style names (UnitPrice, not Unit Price ($)) — they read better in formulas. Use the description field for the pretty label.

Named values (a table of assumptions)

A column of tiles is not the only way to keep named constants. Right-click any data column, choose Name values…, and pick the column holding the names — every row then becomes usable by name exactly like a tile:

Name	Value
Tax Rate	0.08
Discount	0.10

@Subtotal * (1 + 'Tax Rate')

Scripts see them in the same tiles["Tax Rate"] map. The names are read from the data every time formulas recalculate, so editing either column updates the references immediately. Retyping a name carries its references with it, exactly as renaming a tile does — unless the change is ambiguous (the old name still exists on another row, or two rows swapped names), in which case the formula is left as written and shows #NAME! rather than being pointed somewhere you didn't ask for.

Both columns become a different kind of column while they're bound: their cells hold names and values, so neither can take a formula or be converted to a calculated column. Remove names in either column's right-click menu turns them back into ordinary columns.

A tile or table of the same name keeps it, and within the name column the first row with a given name wins. Any name that loses — or isn't text — is marked red in the grid with the reason on hover. Named values are limited to tables of 100 rows or fewer; past that the table names nothing at all, rather than naming a subset that would change every time you sorted it.

Literals

Numbers: 42, 2.5, .75, and scientific notation 1e5, 1.5e-3, 2E+10.
Text: double quotes only — "East". Escapes: \", \n, \t, \\.
Booleans: TRUE / FALSE (case-insensitive).
There is no percent operator: write 0.05, not 5%. Use a percent display format to show it as 5%.

Comments

// starts a comment that runs to the end of the line. Formulas can span multiple lines, so a long one can be laid out and annotated:

// commission: 8% base, 12% once the quota is cleared
IF(@Sales > 50000,
   @Sales * 0.12,   // above quota
   @Sales * 0.08)   // below

Comments are ignored entirely — they don't affect the value, they're skipped when a rename rewrites references, and they're dropped when a formula is exported to Excel (Excel has no equivalent).

Operators

Precedence	Operators	Notes
1 (lowest)	`=` `<>` `<` `>` `<=` `>=`	Comparisons
2	`+` `-` `&`	`&` is text concatenation
3	`*` `/`
3.5	unary `-` `+`
4 (highest)	`^`	Power, left-associative

Two things worth knowing, both deliberate:

^ chains left-to-right like Excel: 2^3^2 is (2^3)^2 = 64.
Unary minus binds looser than `^`, following standard math rather than Excel: -2^2 is -(2^2) = -4. (Excel says 4.) Write (-2)^2 if you mean 4. Formulas imported from Excel are adjusted automatically so their values don't change.

Errors are values

A formula never "crashes" — errors are ordinary values that flow through calculations, exactly like Excel. The kinds you'll see:

Error	Meaning
`#PARSE!`	The formula text couldn't be parsed
`#NAME!`	Unknown function, column, table, or tile
`#TYPE!`	A value couldn't be converted (e.g. `AND("hello")`)
`#EVAL!`	Evaluation failed (covers Excel's `#NUM!` cases, e.g. `IRR` not converging)
`#REF!`	A broken reference — deleted column, out-of-range row
`#CYCLE!`	A circular reference
`#DIV/0!`	Division by zero
`#N/A`	Intentionally missing value

Hovering an error cell shows the detailed message. Guard against expected errors with IFERROR(value, fallback) or test explicitly with ISERROR / ISNA / ISBLANK:

IF(ISBLANK(@Cost), 0, @Price / @Cost)
IFERROR(LOOKUP(@SKU, Products.SKU, Products.Price), 0)

Dates are serial numbers

A date is stored as a number of days since 1970-01-01, with time-of-day as the fractional part. The display format is what makes it look like a date. This is Excel's model, and it means date arithmetic just works:

@Date + 30          → thirty days later
@End - @Start       → number of days between two dates

One consequence, inherited knowingly from Excel: a formula that returns a date shows a bare serial number until its column is formatted as a date. DreamSheets auto-formats a calculated column as a date when the formula's outermost call is DATE, TODAY, NOW, EDATE, or EOMONTH — but @Date + 30 (the outermost operation is arithmetic) needs the date format set by hand.

Truthiness and coercion

Blank cells are 0 in arithmetic and "" in text contexts.
AND / OR / NOT are strict like Excel: text is an error (AND("hello") → #TYPE!). Numbers are truthy when non-zero.
IF's condition is looser: non-empty text also counts as true. The two rules differ on purpose.
COUNT counts only real numbers — no coercion, matching Excel.
MOD's sign follows the divisor (MOD(-3, 2) is 1) and INT floors (INT(-2.5) is -3) — Excel semantics, not programming-language semantics.

Custom functions

Define your own functions once and call them anywhere, like built-ins:

Markup(@Amount)        where Markup(amt) = amt * 1.2

Two ways to create one:

The function manager — define a name, parameters, and a body. The body is validated live as you type.
Extract from a formula — from any formula editor, extract the current formula into a function. Every reference it uses (@Amount, TaxRate, Rates.Rate) becomes a parameter, and the original formula is rewritten as a call.

Custom functions can't shadow built-in names, and a custom-function body can't call a plugin-provided function — keep plugin calls at the column level (see Building a plugin).

Excel compatibility

The formula language deliberately implements Excel's semantics — it exists to run imported workbooks. See the function reference for the full list. A few Excel names are intentionally absent:

VLOOKUP — use LOOKUP(value, lookup_col, return_col, [default]), which is clearer and doesn't depend on column positions. Imported workbooks translate VLOOKUP and XLOOKUP automatically.
INDEX / MATCH — use LOOKUP, or CELLBYINDEX for positional access.
Array/spill formulas. Plain SUMPRODUCT(range1, range2) is supported — what's absent is array arithmetic inside it, the SUMPRODUCT((cond) * vals) idiom. Use SUMIFS for conditional sums, or a query table for anything more complex.
FIND / SEARCH / TEXT / VALUE, WORKDAY / NETWORKDAYS / YEARFRAC / WEEKNUM.

When a formula can't say it, a query table can: full SQL (joins, grouping, window functions) over your tables. That's the escape hatch — see Query tables.