Verifiers

Witan Calc — The Formula Engine

Recalculate every formula, update the file, and get structured results your agent can act on. No opaque binaries, no guesswork.

$ witan xlsx calc report.xlsx --json
[{"cell":"B5","value":42},{"cell":"C10","error":"DIV0"}]

The alternative

LibreOffice headless (soffice --headless --macro) is the standard approach for agent-driven recalculation. Here is how it compares.

LibreOffice

  • Binary file that agent must re-read to see what changes
  • No error reporting
  • No distinction between existing and new errors
  • No iterative calculation support outside the GUI
  • No support for dynamic arrays
  • Requires 2GB install

Witan xlsx Calc

  • Structured results with every touched cell's address, formula, & value
  • Every new error reported
  • Only new errors reported. Pre-existing errors are filtered out
  • Both detect-and-report with cycle identification, or iterative calculation
  • Dynamic arrays calculated & written
  • Single binary, no dependencies

The formula engine

The formula engine covers the full breadth of Excel's built-in function library. Your agent can write formulas in any of these categories and verify them immediately.

Maths and statistics

SUM, SUMIF, SUMIFS, SUMPRODUCT, AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTIF, COUNTIFS, MIN, MAX, MEDIAN, STDEV, STDEV.S, STDEV.P, VAR, VAR.S, VAR.P, PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC, QUARTILE, RANK, LARGE, SMALL, ABS, ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, MOD, POWER, SQRT, LOG, LN, EXP, and more.

Lookup and reference

VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, XMATCH, OFFSET, INDIRECT, ROW, COLUMN, ROWS, COLUMNS, CHOOSE, ADDRESS, LOOKUP.

Logical

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

Financial

PV, FV, NPV, XNPV, IRR, XIRR, MIRR, PMT, IPMT, PPMT, NPER, RATE, SLN, DB, DDB.

Text

CONCATENATE, CONCAT, TEXTJOIN, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, REPLACE, TRIM, CLEAN, UPPER, LOWER, PROPER, TEXT, VALUE, REPT, EXACT.

Date and time

DATE, TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DATEVALUE, EDATE, EOMONTH, NETWORKDAYS, WORKDAY, DATEDIF, WEEKDAY, WEEKNUM.

Dynamic array functions

FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY. These return multi-cell results that spill into adjacent cells, just like Excel 365.

Array formulas

Legacy CSE (Ctrl+Shift+Enter) array formulas are fully supported. Multi-cell array formulas that span a range are recalculated as a unit.

Not supported

  • External workbook references — formulas referencing other .xlsx files (e.g. =[Budget.xlsx]Sheet1!A1) are not resolved.
  • VBA / macro evaluation — macro-enabled workbooks (.xlsm) can be recalculated, but VBA code is not executed.
  • Add-in functions — custom functions from Excel add-ins (e.g. Bloomberg, Power Query custom functions) are not recognized.

Formula features

Beyond function coverage, the formula engine handles the full range of Excel's reference and formula syntax.

  • Single and multi-cell references
  • Sheet-qualified references (Sheet1!A1)
  • 3D references across sheets (Sheet1:Sheet3!A1)
  • Named ranges (workbook-scoped)
  • Named ranges (sheet-scoped)
  • Structured table references (Table1[Column])
  • Table specifiers: #Headers, #Data, #Totals, #This Row
  • Array formulas (CSE and dynamic)
  • Dynamic array spill ranges
  • Relative and absolute references ($A$1, A$1, $A1)
  • Entire row/column references (A:A, 1:1)
  • Intersection operator (space)

Circular references

Circular references are one of the trickiest parts of Excel recalculation. The formula engine handles both modes.

Example scenario

A 12-month loan amortization schedule. Each row computes a closing balance that depends on a servicing fee, but the fee is 1% of the closing balance. Every row is circular.

$ witan xlsx calc loan.xlsx \
  -r 'Schedule!B2:F13'

Schedule!C2   =B2*$I$2                  500
Schedule!E2   =B2+B2*$I$2-D2+F2   92338.49
Schedule!F2   =E2*$I$3               923.38
Schedule!B3   =E2                  92338.49
Schedule!E3   =B3+B3*$I$2-D3+F3   84560.91
...
Schedule!E13  =B13+B13*$I$2-D13+F13  -0.03
47 cells recalculated

Without Witan

Claude and ChatGPT return #VALUE! on every circular cell. The agent has no schedule to work with and cannot answer any questions about the loan.

Iterative calculation

When the workbook has iterative calculation enabled (as set in Excel's calculation options), the engine iterates until convergence. This matches Excel's own behavior. The workbook's configured maximum iterations and delta threshold are respected.

If the calculation does not converge within the configured limits, the engine reports the failure with the remaining delta, so the agent knows the result is approximate and by how much.

Why this matters for agents

Many financial models use intentional circular references for iterative solving (e.g., interest calculations that depend on total debt that depends on interest). The formula engine handles these correctly instead of failing on them.

Array formulas

Dynamic array functions — FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY — return multi-cell results that spill into adjacent cells, just like Excel 365. Legacy CSE (Ctrl+Shift+Enter) array formulas are also fully supported and recalculated as a unit.

Example scenario

A sales report uses UNIQUE to extract a list of regions, FILTER to pull each region's rows, and SORT to rank them by revenue. The formulas spill across dozens of cells that downstream SUMIFS and charts depend on.

$ witan xlsx calc report.xlsx \
  -r 'Summary!A2:D20'

Summary!A2   =UNIQUE(Data!A2:A200)           North
Summary!A3   (spill)                         South
Summary!A4   (spill)                         East
Summary!A5   (spill)                         West
Summary!B2   =SORT(FILTER(...),1,-1)         95000
Summary!B3   (spill)                         87000
Summary!B4   (spill)                         64000
...
Summary!D2   =SUMIFS(Data!C2:C200,...)       246000
34 cells recalculated

Without Witan

Claude and ChatGPT cannot evaluate dynamic array functions. Every spill-range cell returns an error or is silently empty, breaking every downstream formula that references the spilled results.

Smart error reporting

The formula engine filters out pre-existing errors and only reports new errors introduced during recalculation. This is critical for agents: they see only the bugs they introduced, not issues that were already in the file.

Without this filtering, an agent editing a workbook with 15 pre-existing #N/A errors would have to figure out which errors are theirs and which were already there. With smart filtering, the answer is immediate: every error in the output is one the agent needs to fix.

Code Meaning
#DIV/0! Division by zero or empty cell
#N/A Lookup value not found
#NAME? Unrecognized function or named range
#NULL! Incorrect range intersection
#NUM! Invalid numeric value (e.g., negative square root)
#REF! Invalid cell reference (deleted row/column, or circular)
#VALUE! Wrong argument type (text where number expected)
#CALC! Calculation engine error (e.g., empty array from FILTER)
#SPILL! Dynamic array cannot spill because cells are not empty

Error details

Each error includes four pieces of information:

  • Cell address — exactly where the error is (e.g. Summary!B7)
  • Error code — the Excel error type (e.g. #DIV/0!)
  • Formula — the formula that produced the error (e.g. =B2/B99)
  • Explanation — a human-readable description of what went wrong (e.g. B99 is empty)

This structured output lets agents programmatically identify and fix errors without parsing prose.

The file gets updated

When you run witan xlsx calc, the engine does two things:

  1. Recalculates every formula in the workbook.
  2. Writes the correct computed values back into the file's cached value store.

This means when someone opens the resulting file in Excel, they see correct values immediately. Excel stores a "cached value" for each formula cell — the last computed result. Many editing libraries (openpyxl, xlwings in write-only mode) leave these cached values stale or missing. witan xlsx calc ensures they are current.

No double-open required

Without updated cached values, users opening the file may see stale numbers until Excel finishes its own recalculation. Some viewers (Google Sheets import, preview tools) may never recalculate and show the stale values permanently. Calc eliminates this class of problems.

The agent gets back both the updated file on disk and the structured JSON results (touched cells, errors) for immediate programmatic use.

CLI reference

Flag Description Required
<file> Path to the .xlsx file. Positional argument. Yes
--range, -r Range(s) to show computed values for. Repeatable. No
--errors-only Only show errors, even when ranges are specified. No

The entire workbook is always recalculated regardless of which ranges are requested. The --range flag controls what is displayed, not what is calculated.

Output format

With range: values and errors 

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

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 error

Each line is columnar: address, formula, then either the computed value or the error with explanation. The summary line at the bottom gives totals.

Errors only 

$ witan xlsx calc budget.xlsx

1 error:

Sheet1!B5   =B2/B10  #DIV/0! ← B10 is empty

Clean recalculation 

$ witan xlsx calc budget.xlsx

247 cells recalculated, 0 errors

When there are no errors and no range is specified, the output is a single line confirming how many cells were recalculated.

Previous
Render
Next
Lint