The language

The formula language, its references, operators, results and errors.

A formula field works a value or a small chart out of other columns, per row. It is a real, small language. A formula only reads. It never changes a cell, and when something is wrong it shows a quiet error instead of a wrong answer.

Writing a formula

A formula is an expression. A leading = is optional (it is stripped). It names columns by name, in square brackets, so reordering columns keeps the formula working. Renaming a column that a formula names means updating the formula to the new name.

=AVG([Weekly check-ins.Mood WHERE Engineer IS SELF])

Try it

This runs the real engine. Type a formula and watch the result draw, the same way a cell would.

=
Result

The sample row is Wk 1 to Wk 6 = 3, 5, 4, 8, 6, 9.

References

A reference names cells, inside [...]. There are four forms.

[Field]

this row
[Column name]

One cell in the current row, as a number. Unknown column name gives an error.

[A .. B]

this rowseries
[First column .. Last column]

A range of columns in this row, read left to right as a series. The direction does not matter: [Wk 3 .. Wk 1] reads the same three columns. Blank cells drop out of the series.

[Table.Column]

another tableseries
[Table name.Column name]

A whole column in another table, as a series. The table is found by name within the same artifact. Works only where the formula has an artifact to reach into (a table cell or a card), not everywhere.

[Table.Column WHERE Col IS SELF]

another tableper row
[Table.Column WHERE Link column IS SELF]

The same, but only the rows in that table whose link column points at this row. This is how a Team row reads just its own check-ins.

Column and table names are matched case-insensitively and ignoring surrounding spaces. Multi-word names work as written: [Weekly check-ins.Shipped WHERE Engineer IS SELF].

Operators

The arithmetic operators are +, -, *, /, with the usual precedence (* and / before + and -), left to right, and parentheses to group. A leading - is unary negation.

Blanks are gaps, not zeros

A blank or non-numeric cell reads as nothing, not zero. It drops out of a range, out of an average, out of a chart's data. This keeps an empty week from drawing a false dip or dragging an average down.

What a formula resolves to

Every formula resolves to one of three results.

  • A value (a number), shown as a number.
  • A chart, drawn inline.
  • An error, shown quietly with the reason on hover.

A problem never breaks the table. It becomes an error in that one cell, with the reason on hover.

Where formulas can reach

The same expression sees different things depending on where it runs.

In a table cellfull
Sees this row ([Field], ranges) and other tables ([Table.Column], with WHERE ... IS SELF).
On a dashboard cardcolumns only
Has no "this row", so it reads whole columns from tables by name. [Field] and ranges are not available. WHERE ... IS SELF has no self to match.

Errors you can hit

Unknown functionNOPE(...)
There is no function called that.
Unknown column[Missing]
There is no column named that in this row.
Unknown table or column[Nope.x]
No such table in this artifact, or no such column in it.
Reading a formula[Table.AFormula]
A formula cannot read another formula.
A range where a number is neededBULLET([A .. B])
A scalar slot was given a range.
A bare range as the result[A .. B]
A formula must end in a value or a chart.
Reaching another table where you can't[Table.Col]
This formula has no table to reach.
Syntax=SUM(
Unbalanced brackets, a stray character, a bad number, an empty formula.

Next, the functions a formula can call.