Formulas

Formula Reference

Complete reference for the formula DSL — operands, conditions, functions, group aggregates, summary formulas, and scope rules.

This page is the exhaustive reference for the formula DSL. For a guided introduction with worked examples, see Formula Columns. For the logical-row vs physical-row mental model, see Row Model.

Entry points

Every formula column callback receives { row, fx }:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ qty
: number; price
: number }>()
  .column
("qty", { accessor
: "qty" })
  .column
("price", { accessor
: "price" })
  .column
("total", {
    formula
: ({ row
, fx
 }) => {
      return row
.ref
("qty").mul
(row
.ref
("price"));
    },
  })
  .build
();

Parameter	Type	Description
`row`	`FormulaRowContext`	References other columns and group aggregates
`fx`	`FormulaFunctions`	Spreadsheet functions (ROUND, IF, MIN, MAX, …)

`row` — row context

`row.ref(columnId)`

References the value of another column in the same row. Returns a FormulaOperand.

The column ID is type-checked. Referencing a non-existent column or a column declared after the current one is a TypeScript error.

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ units
: number; rate
: number }>()
  .column
("units", { accessor
: "units" })
  .column
("rate", { accessor
: "rate" })
  .column
("cost", {
    formula
: ({ row
 }) => row
.ref
("units").mul
(row
.ref
("rate")),
  })
  .build
();

Primitive literals

String, number, and boolean primitives can be passed directly into formula operations. They are embedded as Excel literals automatically.

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ amount
: number }>()
  .column
("amount", { accessor
: "amount" })
  .column
("withVat", {
    formula
: ({ row
 }) => row
.ref
("amount").mul
(1.2),
  })
  .build
();

`row.if(condition, whenTrue, whenFalse)`

Inline IF — equivalent to fx.if(). Returns a FormulaOperand. Available directly on row for convenience.

`row.group(groupId)`

References a group of columns declared with .group(). Returns a FormulaGroupContext. See group aggregates below.

`FormulaOperand` — arithmetic and comparison

A FormulaOperand is returned by row.ref() and most fx.* functions. It exposes arithmetic operators, comparison operators, and unary functions chainably.

Arithmetic

Method	Excel equivalent	Example
`.add(right)`	`A + B`	`row.ref("a").add(row.ref("b"))`
`.sub(right)`	`A - B`	`row.ref("a").sub(1)`
`.mul(right)`	`A * B`	`row.ref("qty").mul(row.ref("price"))`
`.div(right)`	`A / B`	`row.ref("total").div(row.ref("count"))`

right can be a FormulaOperand, a FormulaExpr, or a primitive (string | number | boolean). Primitives are automatically wrapped in a literal.

Unary

Method	Excel equivalent	Example
`.abs()`	`ABS(A)`	`row.ref("delta").abs()`
`.round(n?)`	`ROUND(A, n)`	`row.ref("rate").round(4)`

n defaults to 0.

Comparison — returns `FormulaCondition`

Method	Excel equivalent	Example
`.eq(right)`	`A = B`	`row.ref("status").eq("active")`
`.neq(right)`	`A <> B`	`row.ref("code").neq(0)`
`.gt(right)`	`A > B`	`row.ref("score").gt(90)`
`.gte(right)`	`A >= B`	`row.ref("mrr").gte(1000)`
`.lt(right)`	`A < B`	`row.ref("age").lt(30)`
`.lte(right)`	`A <= B`	`row.ref("risk").lte(0.5)`

`FormulaCondition` — boolean logic

FormulaCondition is returned by comparison operators. It can be passed directly to fx.if(), or chained.

Chaining

Method	Excel equivalent	Example
`.and(right)`	`AND(A, B)`	`row.ref("a").gt(0).and(row.ref("b").lt(100))`
`.or(right)`	`OR(A, B)`	`row.ref("tier").eq("A").or(row.ref("mrr").gte(5000))`
`.not()`	`NOT(A)`	`row.ref("active").eq(true).not()`

Conditions can be chained multiple times:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ score
: number; tier
: string; active
: boolean }>()
  .column
("score", { accessor
: "score" })
  .column
("tier", { accessor
: "tier" })
  .column
("active", { accessor
: "active" })
  .column
("eligible", {
    formula
: ({ row
, fx
 }) =>
      fx
.if
(
        row
.ref
("active").eq
(true).and
(row
.ref
("score").gte
(80)).and
(row
.ref
("tier").eq
("premium")),
        "YES",
        "NO",
      ),
  })
  .build
();

`fx` — formula functions

`fx.if(condition, whenTrue, whenFalse)`

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ revenue
: number; target
: number }>()
  .column
("revenue", { accessor
: "revenue" })
  .column
("target", { accessor
: "target" })
  .column
("status", {
    formula
: ({ row
, fx
 }) =>
      fx
.if
(row
.ref
("revenue").gte
(row
.ref
("target")), "ON TRACK", "BEHIND"),
  })
  .build
();

Nested if calls create IF(…, …, IF(…)) chains:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ score
: number }>()
  .column
("score", { accessor
: "score" })
  .column
("grade", {
    formula
: ({ row
, fx
 }) =>
      fx
.if
(
        row
.ref
("score").gte
(90),
        "A",
        fx
.if
(row
.ref
("score").gte
(75), "B", fx
.if
(row
.ref
("score").gte
(60), "C", "F")),
      ),
  })
  .build
();

`fx.round(value, decimals?)`

Wraps a value in ROUND(value, n). decimals defaults to 0.

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ amount
: number; rate
: number }>()
  .column
("amount", { accessor
: "amount" })
  .column
("rate", { accessor
: "rate" })
  .column
("result", {
    formula
: ({ row
, fx
 }) => fx
.round
(row
.ref
("amount").mul
(row
.ref
("rate")), 2),
  })
  .build
();

`fx.abs(value)`

Wraps a value in ABS(value).

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ baseline
: number; current
: number }>()
  .column
("baseline", { accessor
: "baseline" })
  .column
("current", { accessor
: "current" })
  .column
("deviation", {
    formula
: ({ row
, fx
 }) => fx
.abs
(row
.ref
("current").sub
(row
.ref
("baseline"))),
  })
  .build
();

`fx.min(...values)` and `fx.max(...values)`

Accept any number of operands, literals, or conditions.

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ bid
: number; ask
: number; mid
: number }>()
  .column
("bid", { accessor
: "bid" })
  .column
("ask", { accessor
: "ask" })
  .column
("mid", { accessor
: "mid" })
  .column
("spread", {
    formula
: ({ row
, fx
 }) => fx
.max
(row
.ref
("ask").sub
(row
.ref
("bid")), 0),
  })
  .build
();

`fx.and(...conditions)` and `fx.or(...conditions)`

Variadic AND / OR — accept multiple FormulaCondition or FormulaExpr values:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ a
: number; b
: number; c
: number }>()
  .column
("a", { accessor
: "a" })
  .column
("b", { accessor
: "b" })
  .column
("c", { accessor
: "c" })
  .column
("allPositive", {
    formula
: ({ row
, fx
 }) =>
      fx
.if
(fx
.and
(row
.ref
("a").gt
(0), row
.ref
("b").gt
(0), row
.ref
("c").gt
(0)), "YES", "NO"),
  })
  .build
();

`fx.not(condition)`

Wraps a condition in NOT(…):

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

createExcelSchema
<{ blocked
: number }>()
  .column
("blocked", { accessor
: "blocked" })
  .column
("allowed", {
    formula
: ({ row
, fx
 }) => fx
.if
(fx
.not
(row
.ref
("blocked").eq
(1)), "ALLOWED", "BLOCKED"),
  })
  .build
();

Group aggregates

When columns are declared inside a .group(), a formula column declared after the group can aggregate all of its columns using row.group(groupId):

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

type SalesRow
 = { q1
: number; q2
: number; q3
: number; q4
: number };

const schema
 = createExcelSchema
<SalesRow
>()
  .group
("quarters", (g
) => {
    g
.column
("q1", { header
: "Q1", accessor
: "q1" });
    g
.column
("q2", { header
: "Q2", accessor
: "q2" });
    g
.column
("q3", { header
: "Q3", accessor
: "q3" });
    g
.column
("q4", { header
: "Q4", accessor
: "q4" });
  })
  .column
("annual", {
    header
: "Annual",
    formula
: ({ row
 }) => row
.group
("quarters").sum
(),
    style
: { font
: { bold
: true } },
  })
  .column
("avgQuarter", {
    header
: "Avg / Quarter",
    formula
: ({ row
, fx
 }) => fx
.round
(row
.group
("quarters").average
(), 0),
  })
  .build
();

Available group aggregates

Method	Excel output
`.sum()`	Sum of group columns in this row
`.average()`	Average of group columns in this row
`.min()`	Minimum of group columns in this row
`.max()`	Maximum of group columns in this row
`.count()`	Count of non-empty group columns

Group aggregates work identically in report mode and excel-table mode — only the emitted cell reference style differs.

Summary formulas

Summary rows have their own formula context ({ column, fx }) — see Summary Formulas for the full reference.

Scope rules and mode output

Scope rules (predecessor ordering, group scope, runtime errors) and how mode affects the emitted formula string (A1 vs structured references) are covered in Scope and Modes.

Type reference

Type	Description
`FormulaOperand`	Arithmetic result, supports `.add/sub/mul/div`, comparisons, `.abs/.round`
`FormulaCondition`	Boolean result, supports `.and/.or/.not`, passable to `fx.if`
`FormulaValue`	`string \| number \| boolean \| FormulaOperand \| FormulaExpr` — accepted everywhere a value is expected
`FormulaConditionValue`	`FormulaCondition \| FormulaExpr` — accepted in boolean positions
`FormulaRowContext`	`{ ref, group, literal, if }` — the `row` parameter
`FormulaFunctions`	`{ if, round, abs, min, max, and, or, not }` — the `fx` parameter
`FormulaGroupContext`	`{ sum, average, min, max, count }` — returned by `row.group()`

Row Model

Understand logical rows, physical rows, row.ref(), row.series(), expansion modes, and how formulas and summaries behave on expanded reports.

Summary Formulas

Declare Excel range formulas in summary rows — column.cells() aggregates, fx functions, and shorthand strings.