What-If Analysis

Witan supports two workbook workflows for exploring how model outputs change when inputs change:

Workflow	Use it when
What-If Data Tables	You want an Excel-native one-variable or two-variable data table saved in the workbook
`sweepInputs`	You want a script to run input combinations and return outputs without adding a data-table block to the sheet

What-If Data Tables are Excel formula blocks. They are separate from ListObject tables and chart data tables.

What-If Data Tables

Use addDataTable to create an Excel What-If Data Table. The ref is the visible table footprint on the worksheet.

witan xlsx exec model.xlsx --save --stdin <<'WITAN'
const result = await xlsx.addDataTable(wb, "Sheet1", {
  type: "oneVariableColumn",
  ref: "E1:F4",
  columnInputCell: "H1",
  inputValues: [5, 10, 15],
  formulas: ["=H1*2"]
})

return {
  range: result.dataTable.ref,
  touched: result.touched
}
WITAN

A one-variable table can be column-oriented or row-oriented:

await xlsx.addDataTable(wb, "Sheet1", {
  type: "oneVariableRow",
  ref: "E1:H2",
  rowInputCell: "H1",
  inputValues: [5, 10, 15],
  formulas: ["=H1*2"]
})

Use a two-variable table when the output formula depends on two inputs:

await xlsx.addDataTable(wb, "Sheet1", {
  type: "twoVariable",
  ref: "E1:H4",
  rowInputCell: "Inputs!B2",
  columnInputCell: "Inputs!B3",
  rowInputValues: [0.02, 0.04, 0.06],
  columnInputValues: [100000, 200000, 300000],
  formula: "=Inputs!B10"
})

The mutation result includes the created data-table model, recalculated touched cells, changed addresses, formula errors, invalidated render tiles, and updated sheet metadata.

Inspect And Delete

listSheets includes data-table addresses for each sheet. Pass one of those addresses to getDataTable:

witan xlsx exec model.xlsx --stdin <<'WITAN'
const sheets = await xlsx.listSheets(wb)
const addresses = sheets.flatMap(sheet => sheet.dataTables ?? [])
return await Promise.all(addresses.map(address => xlsx.getDataTable(wb, address)))
WITAN

Delete a data table by its visible footprint:

await xlsx.deleteDataTable(wb, "Sheet1!E1:F4")

Deleting a data table removes the Excel data-table formula block and recalculates affected formulas.

Calculation Behavior

Witan Calc evaluates one-variable and two-variable data-table formulas. When an upstream cell changes, Witan tracks data-table source formulas, substitution cells, and the specific data-table output cells that need recalculation.

Data-table output cells are reported in mutation touched and changed results when they are recalculated. They are also part of dependency tracing, so getCellPrecedents, getCellDependents, traceToInputs, and traceToOutputs can explain how a data-table block participates in the model.

Programmatic Sweeps

Use sweepInputs when you want a temporary sensitivity run instead of a saved Excel data-table block:

witan xlsx exec model.xlsx --stdin <<'WITAN'
return await xlsx.sweepInputs(
  wb,
  [
    { address: "Inputs!B2", values: [100000, 200000, 300000] },
    { address: "Inputs!B3", values: [0.02, 0.04] }
  ],
  ["Summary!F25", "Summary!F26"],
  { mode: "cartesian", includeStats: true }
)
WITAN

The result includes:

tsv: a compact tab-separated table with inputs, outputs, and optional stats.
sweeps: structured per-run inputs, outputs, and formula errors.
stats: min, max, mean, and count for numeric outputs when includeStats is true.
sweepCount, inputCount, and outputCount.

The first entry is the workbook's baseline state. Generated sweeps follow it.

Sweep Modes

Mode	Behavior
`cartesian`	Runs every combination of the supplied input values
`parallel`	Zips input value lists by index; all lists must have the same length

Use cartesian for grid-style sensitivity analysis. Use parallel when each input row represents one coordinated case.

Notes And Limits

What-If Data Tables round-trip as normal Excel workbook data-table formulas.
addDataTable supports one-variable column, one-variable row, and two-variable data tables.
sweepInputs is non-persistent: it restores the workbook inputs after running the sweep.
sweepInputs should target ordinary model input cells, not cells inside an Excel data-table formula block.

See xlsx API for data-table signatures and sweepInputs for sweep signatures.

Data Validation

Overview