07.4 · Formula Engine

Built-in Functions Reference

70+ functions, grouped by category. All names are case-insensitive. Arguments are numeric unless noted.

Mathematical

Function	Purpose
`ABS(x)`	Absolute value.
`ROUND(x, d)`	Round to `d` decimals.
`ROUNDUP(x, d)`	Round away from zero.
`ROUNDDOWN(x, d)`	Round toward zero.
`CEILING(x, significance)`	Round up to nearest multiple.
`FLOOR(x, significance)`	Round down to nearest multiple.
`TRUNC(x)`	Truncate to integer.
`INT(x)`	Integer part.
`MOD(x, y)`	Modulo (also `x % y`).
`POWER(x, y)`	`x^y`.
`SQRT(x)`	Square root.
`EXP(x)`	e^x.
`LN(x)`	Natural log.
`LOG(x)`	Same as LN.
`LOG10(x)`	Base-10 log.

Trigonometric

Function	Purpose
`SIN`, `COS`, `TAN`	Radians in.
`ASIN`, `ACOS`, `ATAN`	Radians out.
`ATAN2(y, x)`	Quadrant-correct arctangent.
`RADIANS(deg)`	Degrees → radians.
`DEGREES(rad)`	Radians → degrees.
`PI()`	3.14159…

Statistical (Variadic)

Accept any number of arguments OR a range (L1:L20).

Function	Purpose
`SUM(…)`	Total.
`AVERAGE(…)` / `AVG(…)`	Mean.
`MIN(…)`, `MAX(…)`	Extremes.
`COUNT(…)`	Count of non-null.
`PRODUCT(…)`	Multiplication.

Logical

Function	Purpose
`IF(cond, true_val, false_val)`	Lazy — unused branch is not evaluated.
`AND(a, b)`, `OR(a, b)`	Short-circuit.
`NOT(x)`	Logical not.
`TRUE()`, `FALSE()`	Constants.
`IFERROR(expr, fallback)`	Lazy — returns fallback on error.
`ISERROR(expr)`	Returns 1 if expr errors, else 0.

Estimating Domain

Function	Purpose
`MARKUP(cost, pct)`	`cost × (1 + pct/100)`
`DISCOUNT(cost, pct)`	`cost × (1 - pct/100)`
`ESCALATE(val, rate, periods)`	Compound escalation: `val × (1 + rate/100)^periods`
`VAT(cost, rate)`	Add tax.
`EXCVAT(inc_cost, rate)`	Back-calc ex-tax.

Unit Conversion

Function	Converts
`M2TOFT2(m2)`	m² → ft²
`FT2TOM2(ft2)`	ft² → m²
`MTOFT(m)`	m → ft
`FTTOM(ft)`	ft → m
`KGTOLB(kg)`	kg → lb
`LBTOKG(lb)`	lb → kg

Date / Time

Return a numeric representation (Excel-style serial):

Function	Purpose
`NOW()`	Current date + time.
`TODAY()`	Current date.
`YEAR(d)`	Year from serial date.
`MONTH(d)`	Month.
`DAY(d)`	Day.

Custom Column Aggregates

Function	Purpose
`SUMCC("Col")` / `SUMCC("Col", start, end)`	Sum column, optionally over line range.
`COUNTCC(…)`	Count non-null.
`AVGCC(…)`	Average.
`MINCC(…)` / `MAXCC(…)`	Extremes.
`SUMIFCC("Col", "Match")`	Conditional sum.
`COUNTIFCC(…)`	Conditional count.
`AVGIFCC(…)`	Conditional average.

See 07.8 for detail.

Side-Effect Functions

Function	Purpose
`SET(#var, value)`	Create / update a parametric. Returns the value. Triggers `OnVariableChanged` which cascades to dependent formulas.
`RAND()`	Random `[0,1)`.
`RANDBETWEEN(min, max)`	Random integer in range.

Examples

= ROUND(L5 * 1.15, 2)
= IF(#quantity > 100, DISCOUNT(L5, 5), L5)
= SUM(L1:L20) + SUMCC("Waste")
= ESCALATE(#baseline, 3.5, #year_offset)
= M2TOFT2(#floor_area)
= SET(#total_m2, SUMCC("Area"))    // store a rolling total in a define