WITAN

Witan Exec · Render · Calc · Lint · Alternatives · Pricing · Agent Skills · Install Witan

---

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

---

© Witan Labs Inc. 2026
hello@witanlabs.com · API Docs · GitHub · Research · Terms · Privacy