Formulas

Scope & References

Complete reference for the formula DSL, scope rules, predecessor ordering, and report-vs-table output behavior.

This page is the canonical reference for the formula DSL, formula scope, and mode-specific output. 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, refs, fx, ctx }:

import { createExcelSchema
 } from "typed-xlsx";

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

Parameter	Type	Description
`row`	`FormulaRowContext`	Row-level helpers like `row.series(...)` and `row.if(...)`
`refs`	`FormulaRefs`	Scope selectors for `refs.column(...)`, `refs.group(...)`, and `refs.dynamic(...)`
`fx`	`FormulaFunctions`	Spreadsheet functions (`ROUND`, `IF`, `SUM`, `AVERAGE`, …)
`ctx`	Schema context type	The schema-level context object from `createExcelSchema<Row, Context>()`

`refs` — scope selectors

`refs.column(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 "typed-xlsx";

createExcelSchema
<{ units
: number; rate
: number }>()
  .column
("units", { accessor
: "units" })
  .column
("rate", { accessor
: "rate" })
  .column
("cost", {
    formula
: ({ row
, refs
 }) => refs
.column
("units").mul
(refs
.column
("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 "typed-xlsx";

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

`refs.group(groupId)` and `refs.dynamic(dynamicId)`

Return a scope reference that can be passed into aggregate helpers like fx.sum(...), fx.average(...), fx.min(...), fx.max(...), and fx.count(...).

Use refs.group(...) for structural groups created with .group().

Use refs.dynamic(...) for runtime-generated scopes created with .dynamic().

See Column Groups for structural groups and Dynamic Columns for runtime-generated scopes.

`row` — row context

The row parameter provides row-level helpers that operate on the current logical row.

`row.series(columnId)`

References the full expanded physical-row span for a column within the current logical row. Returns a FormulaSeriesContext with .sum(), .average(), .min(), .max(), and .count().

Use row.series(...) for logical-row aggregates over expanded rows.

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

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

`FormulaOperand` — arithmetic and comparison

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

Arithmetic

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

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

For guarded division, use fx.safeDiv(left, right, fallback?) or fx.safeDiv(left, right, { fallback, when }) instead of changing .div() semantics globally.

Unary

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

n defaults to 0.

Comparison — returns `FormulaCondition`

Method	Excel equivalent	Example
`.eq(right)`	`A = B`	`refs.column("status").eq("active")`
`.neq(right)`	`A <> B`	`refs.column("code").neq(0)`
`.gt(right)`	`A > B`	`refs.column("score").gt(90)`
`.gte(right)`	`A >= B`	`refs.column("mrr").gte(1000)`
`.lt(right)`	`A < B`	`refs.column("age").lt(30)`
`.lte(right)`	`A <= B`	`refs.column("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)`	`refs.column("a").gt(0).and(refs.column("b").lt(100))`
`.or(right)`	`OR(A, B)`	`refs.column("tier").eq("A").or(refs.column("mrr").gte(5000))`
`.not()`	`NOT(A)`	`refs.column("active").eq(true).not()`

Conditions can be chained multiple times:

import { createExcelSchema
 } from "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
, refs
, fx
 }) =>
      fx
.if
(
        refs
          .column
("active")
          .eq
(true)
          .and
(refs
.column
("score").gte
(80))
          .and
(refs
.column
("tier").eq
("premium")),
        "YES",
        "NO",
      ),
  })
  .build
();

`fx` — formula functions

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

import { createExcelSchema
 } from "typed-xlsx";

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

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

import { createExcelSchema
 } from "typed-xlsx";

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

`fx.safeDiv(numerator, denominator, fallback?)`

Wraps a division in IF(denominator<>0, numerator/denominator, fallback). fallback defaults to 0.

You can also pass an options object to customize the fallback and guard condition:

fallback: value returned when the guard fails
when: custom condition to use instead of denominator <> 0

when may be either:

a direct condition expression
a callback that receives the already-supplied numerator and denominator operands

import { createExcelSchema
 } from "typed-xlsx";

createExcelSchema
<{ amount
: number; quota
: number }>()
  .column
("amount", { accessor
: "amount" })
  .column
("quota", { accessor
: "quota" })
  .column
("attainment", {
    formula
: ({ row
, refs
, fx
 }) => fx
.safeDiv
(refs
.column
("amount"), refs
.column
("quota")),
  })
  .build
();

import { createExcelSchema
 } from "typed-xlsx";

createExcelSchema
<{ revenue
: number; cost
: number }>()
  .column
("revenue", { accessor
: "revenue" })
  .column
("cost", { accessor
: "cost" })
  .column
("marginPct", {
    formula
: ({ refs
, fx
 }) =>
      fx
.safeDiv
(refs
.column
("revenue").sub
(refs
.column
("cost")), refs
.column
("revenue"), {
        fallback
: 0,
        when
: ({ denominator
 }) => denominator
.gt
(0),
      }),
  })
  .build
();

`fx.round(value, decimals?)`

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

import { createExcelSchema
 } from "typed-xlsx";

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

`fx.abs(value)`

Wraps a value in ABS(value).

import { createExcelSchema
 } from "typed-xlsx";

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

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

Accept any number of operands, literals, or conditions.

import { createExcelSchema
 } from "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
, refs
, fx
 }) => fx
.max
(refs
.column
("ask").sub
(refs
.column
("bid")), 0),
  })
  .build
();

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

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

import { createExcelSchema
 } from "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
, refs
, fx
 }) =>
      fx
.if
(
        fx
.and
(refs
.column
("a").gt
(0), refs
.column
("b").gt
(0), refs
.column
("c").gt
(0)),
        "YES",
        "NO",
      ),
  })
  .build
();

`fx.not(condition)`

Wraps a condition in NOT(…):

import { createExcelSchema
 } from "typed-xlsx";

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

Scope aggregates

When columns are declared inside a .group() or .dynamic(), a later formula column can aggregate all of that scope's columns by combining refs.group(...) or refs.dynamic(...) with fx.sum(...), fx.average(...), fx.min(...), fx.max(...), or fx.count(...):

import { createExcelSchema
 } from "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
: ({ refs
, fx
 }) => fx
.sum
(refs
.group
("quarters")),
    style
: { font
: { bold
: true } },
  })
  .column
("avgQuarter", {
    header
: "Avg / Quarter",
    formula
: ({ refs
, fx
 }) => fx
.round
(fx
.average
(refs
.group
("quarters")), 0),
  })
  .build
();

Available scope aggregates

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

Scope 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.

Formula scope and mode output

Logical rows and physical rows

Report mode can expand one logical row into multiple physical worksheet rows. That row model is explained in depth in Row Model, including when to use refs.column(...), row.series(...), column.cells(), and column.rows().

Predecessor-based scope

Formula columns can only reference columns declared before them in the same schema. This rule is enforced by TypeScript at declaration time through the schema builder's accumulated type state.

Group and dynamic scope

Columns declared inside a .group() or .dynamic() follow the same predecessor rule, but with an outer scope and a local scope.

A1 vs structured references

The formula DSL is identical in both modes. What changes is the emitted Excel formula string:

report mode emits A1-style references based on physical worksheet position
excel-table mode emits structured references based on column headers

Runtime validation

TypeScript catches most scope violations at declaration time, but some errors can only be detected at output time:

Situation	Behavior
Referenced column not declared yet	TypeScript error
Referenced column excluded via `select.exclude`	Throws at output time
Referenced column inside a group or dynamic scope, referenced from outside	TypeScript error
Group ID referenced before the group is declared	TypeScript error
Dynamic ID referenced before the dynamic scope is declared	TypeScript error
Formula column in excel-table mode with sub-row accessor	Throws at output time

Practical rule of thumb

use refs.column(...) for per-physical-row formulas
use row.series(...) for per-logical-row formulas over expanded cells
use column.cells() in summaries when you want physical-row math
use column.rows() in summaries when you want logical-row math

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`	`{ series, if }` — the `row` parameter
`FormulaRefs`	`{ column, group, dynamic }` — the `refs` parameter
`FormulaFunctions`	`{ literal, if, round, abs, sum, average, count, min, max, and, or, not }` — the `fx` parameter
`FormulaSeriesContext`	`{ sum, average, min, max, count }` — returned by `row.series()`

Summary Formulas

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

Table Mode

Native Excel tables — autoFilter, styled banded rows, totals row, and structured formula references.