Formula reference

All 97 spreadsheet functions, the A1 reference rules, operator precedence, the seven error values, and how a recalculation actually happens.

All documentation — Modelling the world

Product

The TaleShard app — overview

Start here

Boards and the canvas

Boards and the canvas

The node builder

Modelling the world

Variables, logic and commands

Playing and styling

Voice and audio

Working together

Getting it out

Project settings

Project settings

On this page

Two dialects, one registry
References
How a formula “follows the cell”
Operators
The 97 functions
Math — 27
Logic — 18
Text — 21
Date — 14
Vector3 — 5
Lookup — 6
Conditional aggregates — 6
The seven error values
How a recalculation happens
The entity dialect
Limits
The editor

The formula language of a spreadsheet, and of a field whose value source is a formula.

Two dialects, one registry

Where	Leaves it understands
A spreadsheet cell	`A1`, `$A$1`, ranges, whole columns and rows
A field’s Value Source → Formula	Dotted paths — `hp`, `mainWeaponStats.hp`, `weapon.damage`, `party.level`

Same operators, same 97 functions, different leaves. The entity dialect has no A1 at all, which is what lets a field be called stat2.

References

A1 only. There is no R1C1, no named range and no named-column reference — a bare name that is not a function or TRUE/FALSE is #NAME?.

Form	Example	Notes
A cell	`A1`, `b12`	Case-insensitive
Absolute markers	`$A$1`, `$A1`, `A$1`	Ignored when resolving. They matter only when a formula is filled or copied.
A range	`A1:B3`
A whole column	`B:B`, `B:D`	Expanded against the live row count, so a new row is included automatically
A whole row	`1:1`, `2:5`	Expanded across the live column count
A string	`"hello"`	Double quotes. No escapes.
A number	`42`, `3.14`, `.5`
An error	`#REF!`	Accepted, so a stored broken formula round-trips

A range used where a single value is expected is #VALUE!.

How a formula “follows the cell”

Two separate mechanisms, and neither is a live pointer.

Structural changes rewrite the stored text. Inserting, deleting or moving a row or a column maps each reference through the stable identity of the thing it pointed at, and writes the corrected formula back. A reference whose target was deleted becomes #REF!, and a range with either end dead collapses to #REF!.

Filling and copying shift relative references. =A1+B1 filled down one row becomes =A2+B2. $A$1 does not move; A$1 shifts its column only. Shifting above row 1 or left of column A gives #REF!; shifting past the current extent of the grid is allowed, and an off-grid reference simply reads as blank.

Operators

Tightest binding first.

#	Operators	Associativity	Behaviour
1	unary `-`	right	Negates a number; negates each component of a vector
2	`*` `/` `%`	left	By zero → `#DIV/0!`. `vector * scalar` and `vector / scalar` work.
3	`+` `-`	left	See below
4	`&`	left	Always concatenation, whatever the column holds
5	`=` `<>` `<` `>` `<=` `>=`	left	→ boolean

- is operand-driven: date − date gives days, date − number gives a date, number − date is #VALUE!, vector − vector gives a vector.

Warning

+ is HOST-driven, and this surprises people. What + means depends on the column’s type, not on the values:

The column is	`=A1+A1` with `A1 = 5`
Text	`"55"` — it concatenates, always
Vector3	Requires two vectors
Anything else	`10`

A free-form column is operand-typed instead: vector + vector, date arithmetic, otherwise numeric — and a non-numeric text operand is #VALUE!.

& is the unambiguous escape hatch. It concatenates everywhere, in every column, with no host to consult.

Comparison. Numbers, dates, booleans and blanks compare as one numeric class. Text compares lexically and case-insensitively. A blank compared against text is treated as "". Across classes, = is false, <> is true, and an ordering is #VALUE!.

Coercion.

To	Rules
Number	blank → 0 · boolean → 1/0 · date → its timestamp · numeric text parses · anything else `#VALUE!`
Text	boolean → `TRUE`/`FALSE` · date → ISO · vector → `x, y, z` · blank → `""` · an error → its code
Boolean	0 is false · `TRUE`/`FALSE` case-insensitively · blank → false · anything else `#VALUE!`

Worked: =1+2*3 is 7 · =(1+2)*3 is 9 · =2*-3 is −6 · ="5"+3 is 8 · =TRUE+1 is 2 · =A1+5 with a blank A1 is 5 · =1 & 2 is "12".

The 97 functions

Names are matched case-insensitively. [x] is optional; … repeats.

Math — 27

Function	Signature	Notes
`SUM`	`SUM(number1, [number2], …)`	Text and blanks ignored
`AVERAGE`	`AVERAGE(number1, [number2], …)`	No numbers → `#DIV/0!`
`MIN` · `MAX`	`MIN(number1, [number2], …)`	Empty → 0
`PRODUCT`	`PRODUCT(number1, [number2], …)`	Empty → 0
`MEDIAN`	`MEDIAN(number1, [number2], …)`	Empty → `#VALUE!`
`COUNT`	`COUNT(value1, [value2], …)`	Numeric values only
`COUNTA`	`COUNTA(value1, [value2], …)`	Non-empty values
`COUNTBLANK`	`COUNTBLANK(range)`
`ROUND`	`ROUND(number, digits)`
`ROUNDUP`	`ROUNDUP(number, [digits])`	Away from zero
`ROUNDDOWN`	`ROUNDDOWN(number, [digits])`	Toward zero
`TRUNC`	`TRUNC(number, [digits])`
`ABS`	`ABS(number)`
`INT`	`INT(number)`	Floor
`SIGN`	`SIGN(number)`	−1, 0 or 1
`MOD`	`MOD(number, divisor)`	Divisor 0 → `#DIV/0!`
`POWER`	`POWER(base, exponent)`
`SQRT`	`SQRT(number)`	Negative → `#VALUE!`
`EXP`	`EXP(number)`
`LN`	`LN(number)`
`LOG`	`LOG(number, [base])`	Base defaults to 10
`PI`	`PI()`
`CEILING`	`CEILING(number, [significance])`	Significance defaults to 1
`FLOOR`	`FLOOR(number, [significance])`
`RAND`	`RAND()`	`[0, 1)`
`RANDBETWEEN`	`RANDBETWEEN(low, high)`	Inclusive; `low > high` → `#VALUE!`

Logic — 18

Function	Signature	Notes
`IF`	`IF(condition, value_if_true, [value_if_false])`	With no else branch it returns the condition itself
`IFS`	`IFS(condition1, value1, [condition2], [value2], …)`	No match → `#N/A`
`SWITCH`	`SWITCH(expression, case1, value1, …, [default])`	A trailing odd argument is the default
`AND` · `OR`	`AND(logical1, [logical2], …)`	Blanks skipped; no booleans at all → `#VALUE!`
`NOT`	`NOT(logical)`
`XOR`	`XOR(logical1, [logical2], …)`	True when an odd number are true
`ISBLANK` · `ISNUMBER` · `ISTEXT` · `ISLOGICAL`	`ISBLANK(value)`
`ISERROR`	`ISERROR(value)`	Any error
`ISNA`	`ISNA(value)`	`#N/A` only
`IFERROR`	`IFERROR(value, value_if_error)`
`IFNA`	`IFNA(value, value_if_na)`	Traps `#N/A` only
`TRUE` · `FALSE`	`TRUE()`
`NA`	`NA()`	Returns `#N/A`

Text — 21

Function	Signature	Notes
`CONCAT` · `CONCATENATE`	`CONCAT(text1, [text2], …)`	Identical
`TEXTJOIN`	`TEXTJOIN(delimiter, ignore_empty, text1, …)`
`LEN`	`LEN(text)`
`LEFT` · `RIGHT`	`LEFT(text, [count])`	Count defaults to 1
`MID`	`MID(text, start, count)`	1-based start
`FIND`	`FIND(find_text, within_text, [start])`	Case-sensitive. Not found → `#VALUE!`
`SEARCH`	`SEARCH(find_text, within_text, [start])`	Case-insensitive
`REPLACE`	`REPLACE(old_text, start, count, new_text)`	By position
`SUBSTITUTE`	`SUBSTITUTE(text, old_text, new_text)`	All occurrences
`UPPER` · `LOWER`	`UPPER(text)`
`PROPER`	`PROPER(text)`	Title-cases each word
`TRIM`	`TRIM(text)`	Collapses runs of whitespace
`REPT`	`REPT(text, count)`	Over 32,767 characters → `#VALUE!`
`CHAR`	`CHAR(number)`	1–65535
`CODE`	`CODE(text)`	The first character’s code point
`EXACT`	`EXACT(text1, text2)`	Case-sensitive equality
`VALUE`	`VALUE(text)`	Parses to a number
`TEXT`	`TEXT(value, format)`	A minimal formatter — `0.00`-style decimals and `000` zero-padding only. Any other format falls through to plain text.

Date — 14

Dates are timestamps, and every field is extracted in UTC.

Function	Signature	Notes
`TODAY`	`TODAY()`	Start of day
`NOW`	`NOW()`
`DATE`	`DATE(year, month, day)`
`DATEVALUE`	`DATEVALUE(date_text)`
`YEAR` · `MONTH` · `DAY`	`YEAR(date)`	Month is 1–12
`DAYS`	`DAYS(end_date, start_date)`	Whole days
`EDATE`	`EDATE(date, months)`	Same day, clamped to the month’s length
`EOMONTH`	`EOMONTH(date, months)`	Last day of the shifted month
`WEEKDAY`	`WEEKDAY(date, [type])`	1 (default) Sun=1 · 2 Mon=1 · 3 Mon=0
`HOUR` · `MINUTE` · `SECOND`	`HOUR(date)`	UTC

Vector3 — 5

Function	Signature	Notes
`VEC`	`VEC(x, y, z)`
`DOT`	`DOT(vector1, vector2)`	→ number
`CROSS`	`CROSS(vector1, vector2)`	→ vector
`LENGTH`	`LENGTH(vector)`	Magnitude
`NORMALIZE`	`NORMALIZE(vector)`	Zero length → `#DIV/0!`

Lookup — 6

Function	Signature	Notes
`INDEX`	`INDEX(array, row_num, [col_num])`	1-based. Out of bounds → `#REF!`. On a single-row range, one index means the column.
`MATCH`	`MATCH(lookup_value, array, [match_type])`	0 exact · 1 (default) largest ≤ · −1 smallest ≥. None → `#N/A`
`VLOOKUP`	`VLOOKUP(lookup_value, table, col_index, [range_lookup])`	`range_lookup` defaults to TRUE — approximate. Pass `FALSE` for an exact match.
`HLOOKUP`	`HLOOKUP(lookup_value, table, row_index, [range_lookup])`	The first row instead of the first column
`XLOOKUP`	`XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])`	Exact; no hit → the fallback, else `#N/A`
`CHOOSE`	`CHOOSE(index, value1, [value2], …)`	1-based. A range argument → `#VALUE!`

Conditional aggregates — 6

Function	Signature	Notes
`SUMIF`	`SUMIF(range, criteria, [sum_range])`
`COUNTIF`	`COUNTIF(range, criteria)`
`AVERAGEIF`	`AVERAGEIF(range, criteria, [average_range])`	No matches → `#DIV/0!`
`SUMIFS`	`SUMIFS(sum_range, criteria_range1, criteria1, …)`	Criteria combine with AND
`COUNTIFS`	`COUNTIFS(criteria_range1, criteria1, …)`
`AVERAGEIFS`	`AVERAGEIFS(average_range, criteria_range1, criteria1, …)`

Criteria syntax. A leading <=, >=, <>, =, < or > is parsed off a text criterion. A numeric term compares numerically. * and ? wildcards work with = and <> against text, and text ordering is case-insensitive.

The seven error values

Errors propagate through arguments and operators.

Code	Produced by
`#VALUE!`	A type mismatch anywhere — non-numeric text in arithmetic, a range used as a single value, `REPT` over the length cap, `MEDIAN` of nothing, `AND`/`OR` with no booleans, a `select` result naming no existing option
`#DIV/0!`	Dividing or taking a modulo by zero · `NORMALIZE` of a zero vector · an average with no numbers
`#REF!`	A reference whose target was deleted, or shifted off the grid by a fill · lookup coordinates outside the array · a `VLOOKUP` column index outside the table
`#NAME?`	An unknown function · a bare name that is not `TRUE`/`FALSE` · an unknown entity field
`#N/A`	`NA()` · `IFS`/`SWITCH` with no match · a lookup that found nothing
`#CYCLE!`	A dependency cycle, a self-reference included
`#ERROR!`	A syntax error — the formula could not be parsed

How a recalculation happens

A real dependency graph, not a re-run of the sheet.

Every formula cell is collected.
A precedent → dependent graph is built and topologically sorted.
The dirty set is evaluated in order.

A change recomputes only what is reachable from it.

A cycle is isolated, not fatal. Every cell inside a cycle — and any self-reference — is marked #CYCLE!; the rest of the sheet still computes, and cells depending on a cyclic one inherit the error the normal way. There is no iterative-calculation mode and no iteration cap: a cycle is an error, full stop.

A whole-column reference makes its formula depend on every cell in the live span, so appending a row re-expands it.

Which column types can host a formula: number, text, boolean, date, vector3, and select when it is single-valued. A free-form column always can.

The entity dialect

Same operators, same functions, paths instead of A1 — except TODAY, NOW, RAND and RANDBETWEEN, which an entity formula refuses: a value that changes on every read would hand a game a different value on every sync. The field says X() changes on every read … Use an authored field instead.

Form	Means
`hp`	A field on this item
`mainWeaponStats.hp`	A trait field, whose name contains dots
`weapon.damage`	Traverse a reference field, then read a field on what it points at
`party.level`	A multi-reference fans out into a range

Field matching is case-insensitive and uses the longest flat prefix, so a trait field wins over a partial traversal.

Because a multi-reference becomes a range, SUM(party.level) works through the ordinary aggregators. A bare multi-reference used as a single value is #VALUE!.

Authored in a field’s Value Source, which offers Manual, Formula and AI. The formula box shows = v1 + v2 as its placeholder and the hint Type a field code for suggestions · traverse with weapon.damage · SUM(party.level), replaced by Syntax error in formula. when it will not parse.

Warnings you may see beside a computed value: an empty reference, a missing item, an unknown field, or a field that cannot be traversed.

Limits

Limit	Value
Rows, columns, formula length	None
Formula nesting in a spreadsheet	No cap — evaluation is a sweep over a sorted graph, not recursion
Iterative calculation	None. A cycle is `#CYCLE!`
Entity formula → formula nesting	32, then `#REF!` and Formula nesting exceeds 32
Entity path traversal	8 hops, then `#REF!` and Reference path exceeds 8 hops
Entity per-path work	100,000 nodes, then `#REF!`
`REPT` result	32,767 characters
`CHAR` code point	1–65535

The editor

The formula bar reads Enter a value or =formula, or Select a cell when nothing is selected.

Autocomplete offers all 97 names alphabetically, filtered by prefix, showing each one’s parameter list and a one-line summary.
Signature help shows the current call’s signature with the active argument in bold.
Point mode colours each distinct reference and highlights the matching cells in the same colour — eight colours, then it cycles. References are collected only in formula mode, so a literal that happens to contain something A1-shaped paints nothing.
Enter commits. Escape closes the completion popup first, and only then reverts.

Conditional formatting supports a custom-formula rule: an arbitrary boolean formula written for the column’s first data row, whose relative references shift down per row, optionally painting the whole row.