# Formula and function reference

> Generated by `npm run gen:ai-pack` (scripts/ai-pack.ts) — do not edit. From the function registry (`src/engine/functions/registry.ts`).

This page lists what the function registry holds for each function. The rest of the formula language — names,
dimensional filters, offsets, positions, `@`, ranges, null handling, self-reference, roll-ups and split-down — is in
the guide's part 03 ([`../guide/03-formulas-and-totals.md`](../guide/03-formulas-and-totals.md)).

## Names

- A name may hold any character except the reserved ones: `;` `/` `[` `]` `{` `}`.
- A name the bare token cannot carry (a space, an operator character) is written in brackets: `[Operating Cost]`.
- Reserved for sequential-dimension boundary elements, as a dimension or series name: `BEFORE`, `AFTER` (any case).
- Function names are matched in any case.

## Functions

Each function has one example, evaluated by a test on this model (`src/engine/functions/function-examples.ts`):

- `Market`, categorical: `All_Market` over `North` and `South` (layers `Top_Market`, `Region`).
- `Time`, sequential: the years `2025`, `2026`, `2027` under `All_Time` (layer `Year`), no boundary elements.
- `Period`, sequential: the months `2030-01` to `2030-06` (layer `Month`) under the quarters `2030-Q1`, `2030-Q2` (layer `Quarter`), with `BEFORE` and `AFTER`.
- `Sales` by `Market` and `Time`: North 100, 120, 150; South 80, 90, 60 (2025–2027).
- `Flows` by `Time`: −100, 30, 80. `Bonus` by `Time`: 10, empty, 20.

An example is a calculated series of that formula over the dimensions it names, read at one element of each.

### aggregation

#### `AVG(values)`

Mean of non-null entries; empty input returns null.

- Arguments: 1. Also spelled: `AVERAGE`.
  - `values` — any-shape: A set of values, usually a filtered series; empty entries are skipped.
- Returns: a scalar.
- Example: `AVG(Bonus[Year/])` gives `15`.

#### `AVGA(values)`

Mean of a vector or matrix, counting an empty entry as zero. AVG skips empty entries; AVGA includes them. An error entry makes the result an error, as it does for every aggregate.

- Arguments: 1.
  - `values` — any-shape: A set of values, usually a filtered series; an empty entry counts as 0.
- Returns: a scalar.
- Example: `AVGA(Bonus[Year/])` gives `10`.

#### `COUNT(argument)`

Count of elements. Form 1 (dimensional): COUNT(dim-expr) returns element count. Form 2 (aggregation): COUNT(series-vector) returns non-null count.

- Arguments: 1.
  - `argument` — any-shape: A dimensional expression, such as `Year/` (its number of elements), or a series over a set, such as `Bonus[Year/]` (its number of values that are not empty).
- Returns: a scalar.
- Example: `COUNT(Bonus[Year/])` gives `2`.

#### `MAX(values, [val2])`

Maximum. Single-arg form aggregates a vector; two-arg form is scalar element-wise max.

- Arguments: 1–2.
  - `values` — any-shape: The set the one-argument form reduces, or the first scalar of the two-argument form.
  - `val2` — any-shape, optional: Second scalar; supplying it selects the two-argument element-wise form.
- Returns: with 1 argument: a scalar; with 2 arguments: the shape of `values` or `val2`.
- Example: `MAX(Sales[Year/])`, by Market, at North, gives `150`.

#### `MAXA(values)`

Maximum of a vector or matrix, counting an empty entry as zero. Unlike MAX, there is no two-argument scalar form: including empty values is only meaningful over a set.

- Arguments: 1.
  - `values` — any-shape: A set of values, usually a filtered series; an empty entry counts as 0.
- Returns: a scalar.
- Example: `MAXA(-Bonus[Year/])` gives `0`.

#### `MEDIAN(values)`

Median of a scalar, vector, or matrix (full flatten); null entries skipped; all-null → null.

- Arguments: 1.
  - `values` — any-shape: A set of values, usually a filtered series; empty entries are skipped.
- Returns: a scalar.
- Example: `MEDIAN(Sales[Year/])`, by Market, at North, gives `120`.

#### `MIN(values, [val2])`

Minimum. Single-arg form aggregates a vector; two-arg form is scalar element-wise min.

- Arguments: 1–2.
  - `values` — any-shape: The set the one-argument form reduces, or the first scalar of the two-argument form.
  - `val2` — any-shape, optional: Second scalar; supplying it selects the two-argument element-wise form.
- Returns: with 1 argument: a scalar; with 2 arguments: the shape of `values` or `val2`.
- Example: `MIN(Sales[Year/])`, by Market, at South, gives `60`.

#### `MINA(values)`

Minimum of a vector or matrix, counting an empty entry as zero. Unlike MIN, there is no two-argument scalar form: including empty values is only meaningful over a set.

- Arguments: 1.
  - `values` — any-shape: A set of values, usually a filtered series; an empty entry counts as 0.
- Returns: a scalar.
- Example: `MINA(Bonus[Year/])` gives `0`.

#### `PERCENTILE(values, p)`

p-th percentile with linear interpolation; PERCENTILE(x, 0.5) ≡ MEDIAN(x).

- Arguments: 2.
  - `values` — any-shape: A set of values, usually a filtered series; empty entries are skipped.
  - `p` — scalar: Quantile in [0, 1].
- Returns: a scalar.
- Example: `PERCENTILE(Sales[Year/], 0.25)`, by Market, at North, gives `110`.

#### `RANK(value, input, [order])`

1-based rank of `value` within `input`. Ties share the lowest rank and the next rank is skipped (competition ranking, as Excel's RANK.EQ): RANK(2, [3, 3, 2, 1]) = 3.

- Arguments: 2–3.
  - `value` — scalar: Target whose rank within `input` is returned.
  - `input` — any-shape: Vector or matrix scoring scope.
  - `order` — literal-only, optional: The bare word ASC or DESC, case-insensitive (default DESC). Written WITHOUT quotes — the tokenizer rejects `"` outright, so RANK(a, b, "ASC") is not a formula (ISSUE-041).
- Returns: a scalar.
- Example: `RANK(Sales, Sales[Region/], DESC)`, by Market and Time, at South, 2025, gives `2`.

#### `SUM(values)`

Sum of a vector or matrix; empty input returns null.

- Arguments: 1.
  - `values` — any-shape: A set of values, usually a filtered series such as `Sales[Year/]`; empty entries add nothing.
- Returns: a scalar.
- Example: `SUM(Sales[Year/])`, by Market, at North, gives `370`.

### arithmetic

#### `ABS(x)`

Absolute value. Auto-vectorises element-wise.

- Arguments: 1.
  - `x` — any-shape: A number, or a set of numbers (each one's absolute value).
- Returns: the shape of `x`.
- Example: `ABS(Sales[South] - Sales[North])`, by Time, at 2025, gives `20`.

#### `CEIL(x)`

Round up to integer. Auto-vectorises.

- Arguments: 1.
  - `x` — any-shape: A number, or a set of numbers (each rounded up).
- Returns: the shape of `x`.
- Example: `CEIL(Sales / 7)`, by Market and Time, at North, 2025, gives `15`.

#### `EXP(x)`

Natural exponentiation. Auto-vectorises.

- Arguments: 1.
  - `x` — any-shape: The power of e, or a set of powers.
- Returns: the shape of `x`.
- Example: `EXP(1)` gives `2.718281828459045`.

#### `FLOOR(x)`

Round down to integer. Auto-vectorises.

- Arguments: 1.
  - `x` — any-shape: A number, or a set of numbers (each rounded down).
- Returns: the shape of `x`.
- Example: `FLOOR(Sales / 7)`, by Market and Time, at North, 2025, gives `14`.

#### `LN(x)`

Natural logarithm; fails when input ≤ 0. Auto-vectorises.

- Arguments: 1.
  - `x` — any-shape: A number greater than 0, or a set of such numbers.
- Returns: the shape of `x`.
- Example: `LN(Sales / 100)`, by Market and Time, at North, 2026, gives `0.1823215567939546`.

#### `MOD(a, b)`

Modulo, with the sign of the divisor as Excel's MOD (MOD(-3, 2) = 1); fails when divisor is 0. Auto-vectorises.

- Arguments: 2.
  - `a` — any-shape: The dividend, or a set of dividends.
  - `b` — any-shape: The divisor, not 0; the result takes its sign.
- Returns: the shape of `a` or `b`.
- Example: `MOD(Sales, 7)`, by Market and Time, at North, 2025, gives `2`.

#### `POWER(base, exponent)`

Exponentiation. Auto-vectorises.

- Arguments: 2.
  - `base` — any-shape: The number raised, or a set of numbers.
  - `exponent` — any-shape: The power to raise it to, or a set of powers.
- Returns: the shape of `base` or `exponent`.
- Example: `POWER(Sales / 100, 2)`, by Market and Time, at North, 2026, gives `1.44`.

#### `ROUND(value, [places])`

Round to nearest, half-away-from-zero (Excel-matching). Auto-vectorises over the first arg.

- Arguments: 1–2.
  - `value` — any-shape: Number or vector/matrix to round.
  - `places` — scalar, optional: Decimal places. Positive: places after the point; 0 (default): nearest integer; negative: powers of 10. A non-integer count truncates toward zero (Excel parity).
- Returns: the shape of `value`.
- Example: `ROUND(Sales[South] / Sales[North], 2)`, by Time, at 2026, gives `0.75`.

#### `SQRT(x)`

Square root; fails when input < 0. Auto-vectorises.

- Arguments: 1.
  - `x` — any-shape: A number 0 or more, or a set of such numbers.
- Returns: the shape of `x`.
- Example: `SQRT(Sales)`, by Market and Time, at North, 2025, gives `10`.

### dim-navigation

#### `DimLevel(dimExpr)`

1-based layer depth of the current coord within the named dimension.

- Arguments: 1.
  - `dimExpr` — dim-ref: Element-or-set whose layer-depth is returned (a bare dimension resolves to its current coordinate).
- Returns: a scalar.
- Example: `DimLevel(Market)`, by Market, at North, gives `2`.

#### `DimPosition(dimExpr, [subTreeRoot])`

Ordinal position of the resolved element — globally within its layer, or within `subTreeRoot`'s subtree. The general functional form of @-notation: `Month@Quarter` is `DimPosition(Month[0], Quarter[0])`, the month's place in its quarter; `Year@` is `DimPosition(Year[0])`, its place in the whole layer (the horizon). A boundary element counts: on a dimension with `BEFORE`, `Month@` is 2 in the first month.

- Arguments: 1–2.
  - `dimExpr` — dim-expr: Element-or-set whose ordinal position is returned (a bare dimension resolves to its current coordinate).
  - `subTreeRoot` — element-ref, optional: Ancestor element scoping the position to its subtree; omitted → layer-global.
- Returns: a scalar.
- Example: `DimPosition(Region[0], Top_Market[0])`, by Market, at South, gives `2`.

#### `ISAFTER(dimension)`

Returns 1 if the current coordinate of the named dimension is its closing (AFTER) boundary, else 0.

- Arguments: 1.
  - `dimension` — dim-ref: Sequential dimension whose AFTER-boundary the current coord is tested against.
- Returns: a scalar.
- Example: `ISAFTER(Period)`, by Period, at AFTER, gives `1`.

#### `ISBEFORE(dimension)`

Returns 1 if the current coordinate of the named dimension is its opening (BEFORE) boundary, else 0.

- Arguments: 1.
  - `dimension` — dim-ref: Sequential dimension whose BEFORE-boundary the current coord is tested against.
- Returns: a scalar.
- Example: `ISBEFORE(Period)`, by Period, at BEFORE, gives `1`.

### logical

#### `AND(left, right)`

Logical AND. When `left` is one value and false (0), `right` is not evaluated.

- Arguments: 2.
  - `left` — scalar: The first condition; a value other than 0 is true.
  - `right` — scalar: The second condition; not evaluated when `left` is one value and false.
- Returns: a scalar.
- Example: `AND(Sales > 50, Sales < 100)`, by Market and Time, at South, 2026, gives `1`.

#### `IF(condition, then, [else])`

Conditional. Returns `then` when `condition` is truthy, `else` otherwise (null if omitted). When `condition` is one value (a cell's own formula), only the branch it chooses is evaluated; over a set, both are, and each element takes its own branch. Either way the other branch's failure never reaches the result: in the first year `IF(Year@ <> 1, Sales / Sales[Year[-1]] - 1)` is empty, while `Sales / Sales[Year[-1]]` alone fails (an empty divisor is a division by zero).

- Arguments: 2–3.
  - `condition` — scalar: Truthy gate.
  - `then` — any-shape: Value when condition is truthy.
  - `else` — any-shape, optional: Value when condition is falsy. Defaults to null when omitted.
- Returns: the shape of `then` or `else`.
- Example: `IF(Year@ <> 1, Sales / Sales[Year[-1]] - 1)`, by Market and Time, at North, 2026, gives `0.2`.

#### `NOT(operand)`

Logical NOT.

- Arguments: 1.
  - `operand` — scalar: The condition to negate; a value other than 0 is true.
- Returns: a scalar.
- Example: `NOT(Sales > 100)`, by Market and Time, at North, 2025, gives `1`.

#### `OR(left, right)`

Logical OR. When `left` is one value and true, `right` is not evaluated.

- Arguments: 2.
  - `left` — scalar: The first condition; a value other than 0 is true.
  - `right` — scalar: The second condition; not evaluated when `left` is one value and true.
- Returns: a scalar.
- Example: `OR(Sales > 140, Sales < 70)`, by Market and Time, at South, 2027, gives `1`.

### lookup

#### `DDB(cost, salvage, life, period, [factor])`

Double-declining-balance depreciation for a given period.

- Arguments: 4–5.
  - `cost` — scalar: The asset's initial cost.
  - `salvage` — scalar: Its value at the end of its life.
  - `life` — scalar: The number of periods it is depreciated over; not 0.
  - `period` — scalar: The period whose depreciation is returned (1 is the first).
  - `factor` — scalar, optional: Default 2 (double-declining).
- Returns: a scalar.
- Example: `DDB(1000, 100, 5, 1)` gives `400`.

#### `FV(rate, nper, pmt, [pv], [type])`

Future value of an annuity.

- Arguments: 3–5.
  - `rate` — scalar: The interest rate per period.
  - `nper` — scalar: The number of periods.
  - `pmt` — scalar: The payment each period (negative for money paid out).
  - `pv` — scalar, optional: The present value; 0 when omitted.
  - `type` — scalar, optional: When payments fall: 0 (omitted) at the end of each period, 1 at its start.
- Returns: a scalar.
- Example: `FV(0.05, 3, -100)` gives `315.25`.

#### `IRR(cashflows, [guess])`

Internal rate of return per period.

- Arguments: 1–2.
  - `cashflows` — vector: Cash-flow vector.
  - `guess` — scalar, optional: Initial estimate (default 0.1).
- Returns: a scalar.
- Example: `IRR(Flows[Year/])` gives `0.05691785736085261`.

#### `NPV(rate, cashflows)`

Net present value at the given per-period rate.

- Arguments: 2.
  - `rate` — scalar: The discount rate per period; the first cash flow is discounted by one period.
  - `cashflows` — vector: Sequential vector; first element discounted by one period (Excel).
- Returns: a scalar.
- Example: `NPV(0.1, Flows[Year/])` gives `-6.010518407212636`.

#### `PMT(rate, nper, pv, [fv], [type])`

Periodic payment on an annuity.

- Arguments: 3–5.
  - `rate` — scalar: The interest rate per period.
  - `nper` — scalar: The number of periods; not 0.
  - `pv` — scalar: The present value (the amount borrowed).
  - `fv` — scalar, optional: The value left after the last payment; 0 when omitted.
  - `type` — scalar, optional: When payments fall: 0 (omitted) at the end of each period, 1 at its start.
- Returns: a scalar.
- Example: `PMT(0.05, 3, 1000)` gives `-367.2085646312448`.

#### `PV(rate, nper, pmt, [fv], [type])`

Present value of an annuity.

- Arguments: 3–5.
  - `rate` — scalar: The interest rate per period.
  - `nper` — scalar: The number of periods.
  - `pmt` — scalar: The payment each period (negative for money paid out).
  - `fv` — scalar, optional: The value left after the last payment; 0 when omitted.
  - `type` — scalar, optional: When payments fall: 0 (omitted) at the end of each period, 1 at its start.
- Returns: a scalar.
- Example: `PV(0.05, 3, -100)` gives `272.324802937048`.

#### `RATE(nper, pmt, pv, [fv], [type], [guess])`

Per-period interest rate of an annuity. Newton-Raphson with bisection fallback.

- Arguments: 3–6.
  - `nper` — scalar: The number of periods; not 0.
  - `pmt` — scalar: The payment each period (negative for money paid out).
  - `pv` — scalar: The present value.
  - `fv` — scalar, optional: Default 0.
  - `type` — scalar, optional: 0 = end-of-period (default), 1 = start.
  - `guess` — scalar, optional: Initial estimate (default 0.1).
- Returns: a scalar.
- Example: `RATE(12, -100, 1000)` gives `0.029228540769133612`.

#### `SLN(cost, salvage, life)`

Straight-line depreciation per period.

- Arguments: 3.
  - `cost` — scalar: The asset's initial cost.
  - `salvage` — scalar: Its value at the end of its life.
  - `life` — scalar: The number of periods it is depreciated over; not 0.
- Returns: a scalar.
- Example: `SLN(1000, 100, 9)` gives `100`.

#### `SYD(cost, salvage, life, period)`

Sum-of-years digits depreciation for a given period.

- Arguments: 4.
  - `cost` — scalar: The asset's initial cost.
  - `salvage` — scalar: Its value at the end of its life.
  - `life` — scalar: The number of periods it is depreciated over; not 0.
  - `period` — scalar: The period whose depreciation is returned (1 is the first).
- Returns: a scalar.
- Example: `SYD(1000, 100, 5, 1)` gives `300`.

### type

#### `IFERROR(expr, fallback)`

Returns `fallback` when `expr` has an error value, otherwise `expr` itself. The error-path counterpart of Nz, which keeps its job for empty cells. `fallback` is evaluated only when `expr` holds an error value.

- Arguments: 2.
  - `expr` — any-shape: The value returned when it holds no error value.
  - `fallback` — any-shape: The value returned when `expr` holds an error value; evaluated only then.
- Returns: the shape of `expr` or `fallback`.
- Example: `IFERROR(Sales / Sales[Year[-1]], 1)`, by Market and Time, at North, 2025, gives `1`.

#### `ISEMPTY(expr)`

Returns 1 for a cell that holds no figure — empty, or not in range — and 0 for a value. Over a cell with an error value the result is an error too: asking whether something broken is empty has no tidy answer. Use ISERROR to detect an error.

- Arguments: 1.
  - `expr` — any-shape: The value tested: 1 when it is empty, 0 when it holds a figure.
- Returns: a scalar.
- Example: `ISEMPTY(Sales[Year[-1]])`, by Market and Time, at North, 2025, gives `1`.

#### `ISERROR(expr)`

Returns 1 for a cell with an error value and 0 for every other kind. One of only two functions that inspect a value rather than compute from it, which is why an error does not propagate through it. Any error, not one particular value (ISERROR, not ISNA).

- Arguments: 1.
  - `expr` — any-shape: The value tested: 1 when it holds an error value, else 0.
- Returns: a scalar.
- Example: `ISERROR(Sales / Sales[Year[-1]])`, by Market and Time, at North, 2025, gives `1`.

#### `Nz(expr, fallback)`

Null-substitution. Returns `expr` when non-null, otherwise `fallback`. `fallback` is evaluated only when `expr` is empty.

- Arguments: 2.
  - `expr` — any-shape: The value returned when it is not empty.
  - `fallback` — any-shape: The value returned when `expr` is empty; evaluated only then.
- Returns: the shape of `expr` or `fallback`.
- Example: `Nz(Sales[Year[-1]], 0)`, by Market and Time, at North, 2025, gives `0`.