Formulas

Row Model

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

When formulas feel confusing in report mode, the issue is almost always the row model.

The short version:

a logical row is one source object from your rows array
a physical row is one actual worksheet row after expansion
row.ref(...) works at the physical row level
row.series(...) works at the logical row level by targeting the full expanded span
column.cells() summarizes physical cells
column.rows() summarizes logical rows

This page is the canonical explanation of that model.

One input row can become many worksheet rows

In report mode, an accessor that returns an array expands one logical row into multiple physical rows.

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

const schema
 = createExcelSchema
<{
  customer
: string;
  monthlyAmounts
: number[];
}>()
  .column
("customer", { accessor
: "customer" })
  .column
("monthlyAmount", {
    accessor
: (row
) => row
.monthlyAmounts
,
  })
  .build
();

For this input:

{
  customer: "Acme",
  monthlyAmounts: [100, 120, 140],
}

typed-xlsx renders:

1 logical row
3 physical worksheet rows

flowchart TB
  subgraph Input[Logical input]
    LR1["Row 1\ncustomer = Acme\nmonthlyAmounts = [100, 120, 140]"]
  end

  subgraph Output[Worksheet rows]
    PR1["Row 2\ncustomer = Acme\nmonthlyAmount = 100"]
    PR2["Row 3\ncustomer = merged\nmonthlyAmount = 120"]
    PR3["Row 4\ncustomer = merged\nmonthlyAmount = 140"]
  end

  LR1 --> PR1
  LR1 --> PR2
  LR1 --> PR3

That is the core distinction the formula APIs model.

Mental model

Use this rule of thumb:

reach for row.ref(...) when the formula should behave like “this worksheet row only”
reach for row.series(...) when the formula should behave like “the whole original source row”

flowchart LR
  A[Logical row] --> B[Expands to physical rows]
  B --> C[row.ref]
  B --> D[row.series]
  C --> E[Single cell in the current physical row]
  D --> F[Range spanning all physical rows for the logical row]

`row.ref(...)` targets one physical-row cell

row.ref(columnId) means: reference the cell for that column on the current physical worksheet row.

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

const schema
 = createExcelSchema
<{
  qty
: number[];
  unitPrice
: number[];
}>()
  .column
("qty", { accessor
: (row
) => row
.qty
 })
  .column
("unitPrice", { accessor
: (row
) => row
.unitPrice
 })
  .column
("lineTotal", {
    formula
: ({ row
 }) => row
.ref
("qty").mul
(row
.ref
("unitPrice")),
    expansion
: "expand",
  })
  .build
();

If one logical row expands to three physical rows, the emitted formulas behave like this:

flowchart TB
  subgraph Sheet[Physical worksheet rows]
    R2["Row 2\nqty=2\nunitPrice=50\nlineTotal = qty * unitPrice"]
    R3["Row 3\nqty=3\nunitPrice=40\nlineTotal = qty * unitPrice"]
    R4["Row 4\nqty=1\nunitPrice=80\nlineTotal = qty * unitPrice"]
  end

  R2 --> F2["=A2*B2"]
  R3 --> F3["=A3*B3"]
  R4 --> F4["=A4*B4"]

This is the right tool for:

line totals
per-sub-row margins
comparisons that should track each emitted worksheet row separately

`row.series(...)` targets the whole logical-row span

row.series(columnId) means: reference the full range of physical cells generated by the current logical row for that column.

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

const schema
 = createExcelSchema
<{ monthlyAmounts
: number[] }>()
  .column
("monthlyAmount", {
    accessor
: (row
) => row
.monthlyAmounts
,
  })
  .column
("rowAverage", {
    formula
: ({ row
, fx
 }) => fx
.round
(row
.series
("monthlyAmount").average
(), 2),
    expansion
: "single",
  })
  .build
();

If monthlyAmount renders in B2:B4, then:

row.series("monthlyAmount").sum() becomes SUM(B2:B4)
row.series("monthlyAmount").average() becomes AVERAGE(B2:B4)

flowchart LR
  A[Logical row Acme] --> B[monthlyAmount cells B2:B4]
  B --> C[SUM B2:B4]
  B --> D[AVERAGE B2:B4]
  B --> E[MIN B2:B4]
  B --> F[MAX B2:B4]

This is the right tool for:

one-per-order totals across expanded line items
one-per-customer averages across expanded monthly values
formula cells that summarize a logical record instead of each worksheet row

Operators compose the same way on both

One important point: row.ref(...) and row.series(...) both return formula objects you can keep composing.

That means the difference is not in the operators. The difference is in what each operand points at.

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

const schema
 = createExcelSchema
<{
  qty
: number[];
  unitPrice
: number[];
  discountPct
: number;
}>()
  .column
("qty", { accessor
: (row
) => row
.qty
 })
  .column
("unitPrice", { accessor
: (row
) => row
.unitPrice
 })
  .column
("discountPct", { accessor
: "discountPct" })
  .column
("lineNet", {
    formula
: ({ row
, fx
 }) =>
      row
        .ref
("qty")
        .mul
(row
.ref
("unitPrice"))
        .mul
(fx
.literal
(1).sub
(row
.ref
("discountPct"))),
    expansion
: "expand",
  })
  .column
("orderAverage", {
    formula
: ({ row
, fx
 }) => fx
.round
(row
.series
("lineNet").average
(), 2),
    expansion
: "single",
  })
  .build
();

Read that as:

lineNet: compute one value per physical row
orderAverage: aggregate the whole logical row after those physical-row values exist

flowchart TB
  subgraph PhysicalMath[Per physical row]
    P1["row.ref qty * row.ref unitPrice"]
    P2["apply discount"]
  end

  subgraph LogicalMath[Per logical row]
    L1["row.series lineNet"]
    L2["average / sum / min / max"]
  end

  P1 --> P2
  P2 --> L1
  L1 --> L2

Expansion controls where the result is written

The formula itself decides what to compute. expansion decides how many cells receive the result.

"auto": infer based on the formula shape
"single": write once for the logical row, merge remaining physical cells
"expand": repeat across every physical row of the logical row

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

const schema
 = createExcelSchema
<{ amounts
: number[] }>()
  .column
("amount", {
    accessor
: (row
) => row
.amounts
,
  })
  .column
("averageOnce", {
    formula
: ({ row
 }) => row
.series
("amount").average
(),
    expansion
: "single",
  })
  .column
("averageRepeated", {
    formula
: ({ row
 }) => row
.series
("amount").average
(),
    expansion
: "expand",
  })
  .build
();

flowchart LR
  A[Logical row expands to 3 physical rows] --> B[averageOnce]
  A --> C[averageRepeated]
  B --> D[One formula cell plus merged remainder]
  C --> E[Same formula emitted on all 3 physical rows]

In practice:

use single for “one rollup per logical record”
use expand for “repeat the result on every emitted worksheet row”

Summaries use the same row model idea

The same distinction appears in summary formulas.

column.cells() aggregates all physical worksheet cells
column.rows() aggregates logical rows first

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

const schema
 = createExcelSchema
<{ monthlyAmounts
: number[] }>()
  .column
("monthlyAmount", {
    accessor
: (row
) => row
.monthlyAmounts
,
    summary
: (summary
) => [
      summary
.formula
(({ column
 }) => column
.cells
().average
()),
      summary
.formula
(({ column
 }) => column
.rows
().average
((row
) => row
.cells
().average
())),
    ],
  })
  .build
();

flowchart TD
  A[column.cells] --> B[Average all physical cells in the worksheet range]
  C[column.rows] --> D[Resolve one logical-row formula at a time]
  D --> E[Aggregate those row-level results]

Use:

column.cells() when the physical worksheet range is the thing you want to summarize
column.rows() when you want each source row to count once, even if it expanded into many worksheet rows

Report mode vs excel-table mode

This row model matters most in report mode, because report mode supports sub-row expansion.

In excel-table mode:

sub-row expansion is not supported
formulas still use the same DSL
row.ref(...) emits structured references
row.series(...) is not the practical center of the model because one logical row does not fan out into multiple worksheet rows

flowchart LR
  A[Report mode] --> B[One logical row can fan out]
  B --> C[row.ref and row.series both matter]

  D[Excel table mode] --> E[One logical row stays one physical row]
  E --> F[row.ref remains the main reference model]

Practical checklist

Use this checklist when deciding which API to use:

use row.ref(...) for per-physical-row formulas
use row.series(...) for per-logical-row formulas over expanded cells
use expansion: "single" when one logical-row result should render once
use expansion: "expand" when the same result should repeat on every physical row
use column.cells() when summaries should operate on worksheet cells directly
use column.rows() when summaries should respect source-row boundaries

Formula Columns

Declare Excel formulas with a type-safe DSL that works in both report and excel-table modes.

Formula Reference

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