Formula and function reference

Use these expressions in a calculated property's formula editor. Function names stay in uppercase English in both interface languages. These formulas use Boldo references, not spreadsheet cells.

Insert references in examples

In this page, @Cost, @Allocation and similar names are readable placeholders, not text references you can paste unchanged.

1. Type the function or expression around the required reference.
2. Type @ at the reference's position, then select the actual property and, if relevant, the relationship and direction.
3. Finish the expression and wait for validation of its result type.
4. Save and compare the result against known source values.

For example, ROUND(@Annual cost / 12, 2) uses a local numerical property inserted with @. To sum related costs, select the cost property through a relationship and use SUM(@Related cost) instead.

Selecting Number of relationships already inserts COUNT(...). To count distinct assets, replace that function name with COUNT_DISTINCT; keep the inserted relationship reference.

Syntax and operators

Use a decimal point (12.5), commas between arguments and double quotes for text ("production"). Boolean literals are true and false. Inside quoted text, escape a quote with \" and a backslash with \\.

Arithmetic operators are +, -, * and /. Comparisons are =, !=, <, <=, > and >=. Logical operators are AND, OR and NOT; write a AND b, not AND(a, b).

From lowest to highest precedence: OR, AND, NOT, comparison, addition/subtraction, multiplication/division, unary minus. Use parentheses to make the intended grouping explicit. Write (@Cost > 0) AND (@Cost < 100), not 0 < @Cost < 100.

There is no % or ^ operator: use MOD or POW. ==, a leading spreadsheet = and arbitrary cell names are not formula syntax. Text is not implicitly converted to a number: "12" + 3 is invalid.

A formula accepts at most 2,000 characters, 50 references and 64 syntax-nesting levels. Calculated dependencies are separately limited to ten levels and cannot form cycles.

Numerical functions

Function	Meaning and example
ROUND(n) or ROUND(n, digits)	Round to 0 decimal places by default. ROUND(12.345, 2) → 12.35; ROUND(-1.5) → -2.
CEIL(n)	Round toward positive infinity: CEIL(-1.2) → -1.
FLOOR(n)	Round toward negative infinity: FLOOR(-1.2) → -2.
ABS(n)	Absolute value: ABS(-8) → 8.
POW(base, exponent)	Power: POW(2, 3) → 8. An unusable or infinite result becomes blank.
MOD(dividend, divisor)	Remainder: MOD(7, 3) → 1; MOD(-7, 3) → -1. A zero divisor gives blank.
MIN(a, b, ...), MAX(a, b, ...)	Minimum/maximum of at least two compatible numbers or dates. Missing arguments' values are ignored.

ROUND truncates its digits argument to an integer and limits it to -15 through 15. Negative digits round to tens, hundreds and so on. Half values round away from zero.

Currency and Percentage results are rounded to two decimal places. Score results are rounded to a whole number and bounded between zero and their configured maximum. No automatic currency or percentage-unit conversion occurs.

Conditions, text and selections

Function	Meaning and limit
IF(condition, yes, no)	Boolean condition and compatible branch types. A missing condition returns blank.
COALESCE(a, b, ...)	First nonmissing value, with at least two compatible arguments. Zero, false and empty text are values.
ISNULL(value)	True when the value is absent.
CONCAT(a, b, ...)	Join at least two scalar values as text; numbers and booleans are converted to text. A missing value makes the result blank.
UPPER(text), LOWER(text)	Convert letter case.
LIST(...)	Build a multiple-choice selection from keys or selections; remove duplicates while preserving first occurrence. LIST() is empty.
CONTAINS(selection, "key")	Test membership of an exact literal choice key in a multiple-choice selection. This is not a substring search.

For a List result, IF(@Critical, "critical", "normal") requires critical and normal among its allowed choice keys. For List multiple, use LIST("critical", "review"). CONTAINS(@Tags, "critical") returns a Boolean.

A literal key compared with a List property or used in CONTAINS must belong to that source property’s choices. When copying a List or List multiple source into a calculated result, every possible source choice must also exist in the result’s choices, even if currently unselected.

Both branches of a multiple-choice IF must be selections: IF(@Critical, LIST("review"), LIST()). Use COALESCE(@Tags, LIST()) when an absent selection should become empty. LIST itself returns blank if an argument is missing; an empty selection and an absent value are different.

Date functions

DATE("2026-01-31") creates a fixed date. Its sole argument must be a quoted YYYY-MM-DD literal representing an actual date. DATE(2026, 1, 31) and DATE("2026-02-30") are invalid.

YEAR(date), MONTH(date) and DAY(date) extract the year, month from 1 to 12, and day of month. For example, MONTH(DATE("2026-01-31")) returns 1.

DATEDIFF(start, end, "days") counts calendar days from start to end. Units are exactly "days", "months" or "years"; the latter two count complete months or years. Reversing the dates can produce a negative result.

DATEADD(date, amount, "months") offsets a date using the same three units. Amount is truncated to a whole number. Month/year offsets adjust to the end of the target month when necessary: January 31 plus one month in 2026 gives February 28.

TODAY() and NOW() are unavailable. Calculations react to source writes and cannot promise an automatically advancing current date. Compare date properties or a fixed date instead.

Relationships and aggregates

A local property supplies one value. A property selected through a relationship supplies a series, one entry per relationship. Use an aggregate to turn that series into one result. These aggregates are available for calculated asset properties, not calculated relationship properties.

Function	Accepted argument and result
SUM(series)	Numerical series; ignore missing values; empty series → 0.
AVG(series)	Numerical series; ignore missing values; no available values → blank.
MIN(series), MAX(series)	Numerical or date series; ignore missing values; no available values → blank.
COUNT(relationship)	Number of relationships, including parallel links.
COUNT(expression)	Number of nonmissing results from an expression over one relationship path. Zero and false count.
COUNT_DISTINCT(relationship)	Number of distinct related assets. Accepts the relationship-count reference, not a property or arbitrary expression.

SUM(1, 2) is invalid; use 1 + 2. MIN(3) is invalid because the one-argument form expects a series; MIN(3, 8) is valid.

Two parallel links from A to B give COUNT = 2 and COUNT_DISTINCT = 1. If B's cost is 100, summing its cost through those links gives 200. For weighted allocation, use a relationship property: SUM(@Related cost * @Allocation / 100), with both references from the same relationship path and Allocation expressed from 0 to 100.

COUNT(@Related cost > 100) counts false as well as true results. To count only matching entries, use SUM(IF(@Related cost > 100, 1, 0)).

You cannot combine series from different relationship paths inside one row-by-row expression. Aggregate them separately before combining their totals. Multiple-choice selections are not numerical relationship series.

Missing values

Missing input usually propagates as a blank result. Division by zero and nonfinite numerical results also produce blank. COALESCE(@Cost, 0) explicitly substitutes zero, while ISNULL(@Cost) tests absence. There is no null literal to type in a formula.

A comparison involving a missing value returns blank, including = and !=. In logical expressions, false AND missing gives false, and true OR missing gives true. Other combinations can remain unknown. Use ISNULL or COALESCE when your business rule requires an explicit answer.

CONCAT does not silently skip missing parts. CONCAT("Cost: ", COALESCE(@Cost, 0)) provides a fallback, whereas CONCAT("Cost: ", @Cost) is blank when Cost is absent. These deliberate choices make an incomplete source distinguishable from an actual zero.