Witan Calc

Witan Calc recalculates formulas in an Excel workbook. Use the calculation engine from exec when recalculation is part of a workbook script, or use the standalone command when you need a file-level pass, a non-mutating verification check, or CLI reporting of changed cells and formula errors.

From Exec

Inside witan xlsx exec, write operations recalculate affected formulas as part of the operation:

witan xlsx exec model.xlsx --stdin <<'WITAN'
const result = await xlsx.setCells(wb, [
  { address: "Inputs!B5", value: 1.1 }
])

return {
  value: result.touched["Summary!F25"],
  changed: result.changed,
  errors: result.errors
}
WITAN

The returned touched map contains recalculated display values keyed by address. Check errors before saving. If an expected output address is not in touched, that output was not in the recalculated dependency chain for the edit; trace the model or check that you changed the intended input.

Standalone Command

Use witan xlsx calc for a whole-file recalculation pass. By default it writes updated cached values back to the workbook file so Excel, previewers, and import tools see current results immediately.

witan xlsx calc report.xlsx

Clean output is intentionally short:

247 cells recalculated, 0 errors, 0 changed

If formula errors are found, calc prints only those errors by default and exits with code 2:

1 error:
  Summary!B5           =B2/B10  #DIV/0! <- B10 is empty

Automatic Recalculation

This is the usual workflow:

• Read or trace the workbook to identify the input and output cells.
• Call xlsx.setCells or another mutating API.
• Read recalculated values from the operation result.
• Check errors before saving.

Do not run witan xlsx calc after every exec --save edit by default. It is useful as a separate verification or reporting step, not as a required commit step.

Explicit Calc In Exec

The witan xlsx exec scripting API does not require a separate calculation call after normal writes. Methods that change cell values, formulas, or workbook structure perform the needed recalculation and return the affected results.

If you are running Office Script-style code in exec, explicit calculation methods are available:

witan xlsx exec model.xlsx --stdin <<'WITAN'
function main(workbook: ExcelScript.Workbook) {
  workbook.getApplication().calculate(ExcelScript.CalculationType.full)
  return workbook.getApplication().getCalculationState()
}
WITAN

Office Script application.calculate, worksheet.calculate, and range.calculate use the same workbook calculation engine as witan xlsx calc. Prefer the result returned by the write method unless you are running Office Script-style code or deliberately forcing a workbook/range calculation pass inside one script.

Behavior

• By default, the workbook at <file> is overwritten with updated cached formula values.
• With --verify, the workbook is not modified.
• By default, human-readable output shows errors only.
• Use --show-touched to print recalculated cells and their computed values.
• One or more --range values seed recalculation from those ranges; downstream dependent formulas are still recalculated.
• Use --json for the full machine-readable result.

Verify Mode

--verify checks whether recalculation would change the workbook without writing the updated file back to disk:

witan xlsx calc report.xlsx --verify

It exits with code 2 when either condition is true:

• Recalculation produces formula errors.
• Any computed cached value would change.

When --verify is used with human-readable output, calc also prints the changed addresses:

247 cells recalculated, 0 errors, 3 changed

Changed (3):
  Summary!B5
  Summary!B6
  Summary!B7

Showing Recalculated Cells

Use --show-touched when you need the recalculated values in the terminal:

witan xlsx calc budget.xlsx -r "Sheet1!B2:B6" --show-touched

Example output:

Sheet1!B2           =SUM(Inputs!B2:B13)        1,284,500
Sheet1!B3           =B2*0.3                    385,350
Sheet1!B4           =VLOOKUP("Q4",Data!A:C,3,FALSE) 342,100
Sheet1!B5           =B2/B10                    #DIV/0! <- B10 is empty
Sheet1!B6           =IFERROR(B5,"N/A")         N/A

5 cells recalculated, 1 changed, 1 error

Each line contains the address, formula if present, and either the computed display value or an error code with detail.

Range Seeding

--range is repeatable:

witan xlsx calc report.xlsx \
  -r "Inputs!B1:B20" \
  -r "Summary!A1:H10"

Ranges are not just output filters. They tell the engine where to start recalculation. Any downstream formulas that depend on those ranges are still recalculated.

If you need to inspect the touched cells, combine --range with --show-touched.

Formula Dependencies

Witan Calc is built on the same formula dependency graph exposed through exec. Use dependency queries before changing a model, or when a recalculation result is surprising.

Use getCellPrecedents to see what feeds a formula:

witan xlsx exec model.xlsx --stdin <<'WITAN'
return await xlsx.getCellPrecedents(wb, "Summary!F25", 3)
WITAN

Use getCellDependents to see what will be affected by an input edit:

witan xlsx exec model.xlsx --stdin <<'WITAN'
return await xlsx.getCellDependents(wb, "Inputs!B5", 3)
WITAN

The depth argument limits how many dependency hops to follow. Use 1 for the immediate formulas on either side of a cell, a larger number for a bounded trace, or Infinity when the script needs the complete reachable graph.

For a model-level view, traceToInputs walks upstream until it reaches leaf input cells, and traceToOutputs walks downstream until it reaches final output cells:

witan xlsx exec model.xlsx --stdin <<'WITAN'
const inputs = await xlsx.traceToInputs(wb, "Summary!F25")
const outputs = await xlsx.traceToOutputs(wb, "Inputs!B5")
return { inputs, outputs }
WITAN

This is useful before an edit because it tells the script which assumptions support an output and which visible results should be checked after changing an input. After an edit, compare the expected downstream outputs with the touched addresses returned by setCells or printed by calc --show-touched.

Dependency Index Coverage

The dependency index tracks static workbook references at cell, range, sheet, and workbook scope. Supported references include:

Some Excel formulas are intentionally dynamic: the cells they reference are determined only when the formula is evaluated. In those cases, the dependency index reports warnings instead of silently pretending the static graph is complete. Common warning cases are:

These warnings affect static dependency tracing, not calculation correctness. Untracked functions are fully supported for recalculation: Witan evaluates them, runs bounded extra passes for dynamic-reference formulas, and then recalculates their static dependents. For example, INDIRECT("A1") can update correctly when A1 changes even though getCellPrecedents cannot report A1 as a static edge before the formula runs.

Dependency Behavior

The calculation engine uses a workbook dependency graph rather than blindly evaluating every formula after every edit. For incremental writes, the engine:

• Starts from the cells modified by the operation.
• Finds the downstream formula dependency closure.
• Evaluates the affected formulas in dependency order.
• Includes volatile formulas such as NOW, TODAY, RAND, RANDBETWEEN, and RANDARRAY on recalculation passes, along with their dependents.
• Handles dynamic-array spills by cascading recalculation to formulas that depend on newly spilled cells.
• Respects workbook iterative-calculation settings for circular models and reports convergence errors when they do not settle within the workbook limits.

When a formula edit changes the dependency graph, Witan rebuilds the dependency state before later recalculation. Format-only changes do not trigger formula recalculation.

Some Excel formulas use dynamic references that cannot be fully known statically, such as INDIRECT and some OFFSET patterns. Witan tracks those as untracked formulas and runs bounded extra passes so their dependents can settle after ordinary dependency-graph recalculation.

See xlsx API for the exact dependency tracing and formula evaluation return shapes.

Formula Evaluation

Formula evaluation helpers use the same calculation engine without requiring a cell write:

witan xlsx exec model.xlsx --stdin <<'WITAN'
return await xlsx.evaluateFormulas(wb, "Summary", [
  "=SUM(Inputs!B2:B13)",
  "=XLOOKUP(\"Q4\", Inputs!A:A, Inputs!B:B)"
])
WITAN

Use these helpers to test formulas against the workbook context before assigning them to cells, especially when formulas depend on defined names, table structured references, spill ranges, or sheet-qualified references.

Formula Features

Witan evaluates normal formulas, shared formulas, legacy CSE array formulas, dynamic-array formulas, and Excel data tables.

Supported calculation features include:

• Arithmetic, comparison, text concatenation, range, union, and intersection operators.
• A1 references, R1C1 references, sheet-qualified references, 3D references, table structured references, defined names, and spill references.
• External workbook references when the referenced values are available in the workbook's cached external-link data. External 3D references and external structured references are not evaluated.
• Scalar values, ranges, arrays, array constants, implicit intersection, array broadcasting, and functions that return arrays.
• Dynamic-array spill resize, spill blocker detection, cleanup of former spill cells, and recalculation of formulas that depend on spill children.
• Legacy CSE array formulas with fixed output ranges.
• One-variable and two-variable data table formulas.
• Future-function storage normalization, so user-facing formulas can use names such as XLOOKUP, LET, and SEQUENCE without the internal _xlfn. prefix.
• Volatile functions. Registered volatile functions are AREAS, CELL, INDIRECT, INFO, NOW, OFFSET, RAND, RANDARRAY, RANDBETWEEN, and TODAY.
• Circular-reference handling. When iterative calculation is enabled in the workbook, Witan uses the workbook's iteration count and max-change settings. When iterative calculation is disabled, circular references in the affected calculation scope are reported as formula errors.

Supported Functions

The calculation engine registers 497 worksheet functions. Functions in this list are evaluated by Witan; functions not listed here, including custom add-in functions and unsupported Excel feature families such as cube functions, are not evaluated.

Future functions are shown by their normal Excel names. The workbook file may store them with an internal _xlfn. prefix, but that prefix is not part of the public formula syntax you should write.

Database

DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP

Date And Time

DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, EDATE, EOMONTH, HOUR, ISOWEEKNUM, MINUTE, MONTH, NETWORKDAYS, NETWORKDAYS.INTL, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, WORKDAY, WORKDAY.INTL, YEAR, YEARFRAC

Engineering

BESSELI, BESSELJ, BESSELK, BESSELY, BIN2DEC, BIN2HEX, BIN2OCT, BITAND, BITLSHIFT, BITOR, BITRSHIFT, BITXOR, COMPLEX, CONVERT, DEC2BIN, DEC2HEX, DEC2OCT, DELTA, ERF, ERF.PRECISE, ERFC, ERFC.PRECISE, GESTEP, HEX2BIN, HEX2DEC, HEX2OCT, IMABS, IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMCOSH, IMCOT, IMCSC, IMCSCH, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSEC, IMSECH, IMSIN, IMSINH, IMSQRT, IMSUB, IMSUM, IMTAN, OCT2BIN, OCT2DEC, OCT2HEX

Financial

ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, CUMIPMT, CUMPRINC, DB, DDB, DISC, DOLLARDE, DOLLARFR, DURATION, EFFECT, FV, FVSCHEDULE, INTRATE, IPMT, IRR, ISPMT, MDURATION, MIRR, NOMINAL, NPER, NPV, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PDURATION, PMT, PPMT, PRICE, PRICEDISC, PRICEMAT, PV, RATE, RECEIVED, RRI, SLN, SYD, TBILLEQ, TBILLPRICE, TBILLYIELD, VDB, XIRR, XNPV, YIELD, YIELDDISC, YIELDMAT

Information

CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, N, NA, SHEET, SHEETS, TYPE

Lambda

BYCOL, BYROW, ISOMITTED, LAMBDA, LET, MAKEARRAY, MAP, REDUCE, SCAN

Logical

AND, FALSE, IF, IFERROR, IFNA, IFS, NOT, OR, SWITCH, TRUE, XOR

Lookup

ADDRESS, AREAS, CHOOSE, CHOOSECOLS, CHOOSEROWS, COLUMN, COLUMNS, DROP, EXPAND, FILTER, FORMULATEXT, GETPIVOTDATA, HLOOKUP, HSTACK, HYPERLINK, INDEX, INDIRECT, LOOKUP, MATCH, OFFSET, ROW, ROWS, RTD, SORT, SORTBY, TAKE, TOCOL, TOROW, TRANSPOSE, TRIMRANGE, UNIQUE, VLOOKUP, VSTACK, WRAPCOLS, WRAPROWS, XLOOKUP, XMATCH

Math And Trigonometry

ABS, ACOS, ACOSH, ACOT, ACOTH, AGGREGATE, ARABIC, ASIN, ASINH, ATAN, ATAN2, ATANH, BASE, CEILING, CEILING.MATH, CEILING.PRECISE, COMBIN, COMBINA, COS, COSH, COT, COTH, CSC, CSCH, DECIMAL, DEGREES, EVEN, EXP, FACT, FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, ISO.CEILING, LCM, LN, LOG, LOG10, MDETERM, MINVERSE, MMULT, MOD, MROUND, MULTINOMIAL, MUNIT, ODD, PERCENTOF, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDARRAY, RANDBETWEEN, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SEQUENCE, SERIESSUM, SIGN, SIN, SINH, SQRT, SQRTPI, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, TAN, TANH, TRUNC

Statistical

AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, BETA.DIST, BETA.INV, BETADIST, BETAINV, BINOM.DIST, BINOM.DIST.RANGE, BINOM.INV, BINOMDIST, CHIDIST, CHIINV, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT, CHISQ.TEST, CHITEST, CONFIDENCE, CONFIDENCE.NORM, CONFIDENCE.T, CORREL, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COVAR, COVARIANCE.P, COVARIANCE.S, CRITBINOM, DEVSQ, EXPON.DIST, EXPONDIST, F.DIST, F.DIST.RT, F.INV, F.INV.RT, F.TEST, FDIST, FINV, FISHER, FISHERINV, FORECAST, FORECAST.LINEAR, FREQUENCY, FTEST, GAMMA, GAMMA.DIST, GAMMA.INV, GAMMADIST, GAMMAINV, GAMMALN, GAMMALN.PRECISE, GAUSS, GEOMEAN, GROWTH, HARMEAN, HYPGEOM.DIST, HYPGEOMDIST, INTERCEPT, KURT, LARGE, LINEST, LOGEST, LOGINV, LOGNORM.DIST, LOGNORM.INV, LOGNORMDIST, MAX, MAXA, MAXIFS, MEDIAN, MIN, MINA, MINIFS, MODE, MODE.MULT, MODE.SNGL, NEGBINOM.DIST, NEGBINOMDIST, NORM.DIST, NORM.INV, NORM.S.DIST, NORM.S.INV, NORMDIST, NORMINV, NORMSDIST, NORMSINV, PEARSON, PERCENTILE, PERCENTILE.EXC, PERCENTILE.INC, PERCENTRANK, PERCENTRANK.EXC, PERCENTRANK.INC, PERMUT, PERMUTATIONA, PHI, POISSON, POISSON.DIST, PROB, QUARTILE, QUARTILE.EXC, QUARTILE.INC, RANK, RANK.AVG, RANK.EQ, RSQ, SKEW, SKEW.P, SLOPE, SMALL, STANDARDIZE, STDEV, STDEV.P, STDEV.S, STDEVA, STDEVP, STDEVPA, STEYX, T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T, T.TEST, TDIST, TINV, TREND, TRIMMEAN, TTEST, VAR, VAR.P, VAR.S, VARA, VARP, VARPA, WEIBULL, WEIBULL.DIST, Z.TEST, ZTEST

Text

ARRAYTOTEXT, ASC, CHAR, CLEAN, CODE, CONCAT, CONCATENATE, DOLLAR, EXACT, FIND, FINDB, FIXED, LEFT, LEFTB, LEN, LENB, LOWER, MID, MIDB, NUMBERVALUE, PROPER, REGEXEXTRACT, REGEXREPLACE, REGEXTEST, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, SEARCH, SEARCHB, SUBSTITUTE, T, TEXT, TEXTAFTER, TEXTBEFORE, TEXTJOIN, TEXTSPLIT, TRIM, UNICHAR, UNICODE, UPPER, VALUE, VALUETOTEXT

Web

ENCODEURL, EUROCONVERT, FILTERXML

JSON Output

--json prints the calculation response object:

witan xlsx calc report.xlsx --json

{
  "touched": {
    "Summary!B5": {
      "value": "$1,250,000",
      "formula": "=SUM(Revenue!B2:B13)"
    }
  },
  "changed": ["Summary!B5"],
  "errors": []
}

Fields:

The CLI removes returned workbook bytes from JSON output before printing, because the file payload is not useful in terminal automation.

CLI Reference

witan xlsx calc <file> [flags]

Exit Codes

Limits

calc does not execute VBA macros. Macro-enabled workbooks can be opened and recalculated, but macro code is not run.

Custom add-in functions are not evaluated unless the engine has native support for that function. External workbook references are resolved from available cached workbook data; calc does not open arbitrary referenced workbooks from disk.