Formulas

werkbook includes a compiled formula engine with automatic dependency tracking. In the current community build it supports more than 300 built-in functions, including many modern Excel functions such as LET, LAMBDA, FILTER, XLOOKUP, UNIQUE, BYROW, BYCOL, HSTACK, and VSTACK.

Setting Formulas

Set formulas without the leading =:

sheet.SetFormula("A5", "SUM(A1:A4)")
sheet.SetFormula("B5", "AVERAGE(B1:B4)")
sheet.SetFormula("C1", "IF(A1>100,\"High\",\"Low\")")

Reading Formula Results

GetValue on a formula cell returns the computed result. The formula is evaluated lazily on first access:

sheet.SetFormula("A1", "2+2")

v, _ := sheet.GetValue("A1")
fmt.Println(v.Raw()) // 4

To read the formula text itself:

f, _ := sheet.GetFormula("A1")
fmt.Println(f) // "2+2"

Dependency Tracking

werkbook maintains a dependency graph across all formulas. When a cell’s value changes, any formulas that reference it are marked dirty and re-evaluated on next access:

sheet.SetValue("A1", 10)
sheet.SetFormula("B1", "A1*2")

v, _ := sheet.GetValue("B1")
fmt.Println(v.Raw()) // 20

sheet.SetValue("A1", 50)
v, _ = sheet.GetValue("B1")
fmt.Println(v.Raw()) // 100 - automatically recalculated

Force Recalculation

To evaluate all dirty formulas at once:

wb.Recalculate()

This is useful before saving or when you need all formula results to be current.

Cross-Sheet References

Reference cells on other sheets using the Sheet!Cell syntax:

data := wb.Sheet("Data")
data.SetValue("A1", 100)
data.SetValue("A2", 200)

summary := wb.Sheet("Summary")
summary.SetFormula("A1", "SUM(Data!A1:Data!A2)")

v, _ := summary.GetValue("A1")
fmt.Println(v.Raw()) // 300

Quoted sheet names also work:

summary.SetFormula("A2", "'Regional Sales'!B2")

Named Ranges and Tables

Formulas can reference defined names and worksheet tables:

wb.SetDefinedName(werkbook.DefinedName{
    Name:         "TaxRate",
    Value:        "Config!$B$2",
    LocalSheetID: -1,
})

sheet.AddTable(werkbook.Table{
    DisplayName: "SalesTable",
    Ref:         "A1:D10",
})

sheet.SetFormula("E2", "D2*TaxRate")
sheet.SetFormula("F1", "SUM(SalesTable[Total])")

Structured references work when the table exists in the workbook, either because you added it with AddTable or because it was loaded from an existing .xlsx file.

Dependency Inspection

If you need to build audit tools or explain formula chains, the workbook exposes direct dependency helpers:

points, ranges, err := wb.Precedents("Summary", "D10")
deps, err := wb.DirectDependents("Summary", "B2")

These helpers return types from github.com/jpoz/werkbook/formula, so import that package if you want to inspect the fields in detail.

Dynamic Array Behavior

The formula engine supports array-producing functions such as FILTER, UNIQUE, SORT, SEQUENCE, HSTACK, and VSTACK.

When you read a single formula cell through GetValue, werkbook returns the anchor cell result. For a spilled array, that means the top-left element of the result set:

sheet.SetFormula("B1", `FILTER(A1:A10,A1:A10<>"")`)

v, _ := sheet.GetValue("B1")
fmt.Println(v.Raw()) // first value from the spilled result

Supported Function Families

werkbook supports a broad set of functions organized by category:

Category	Examples
Math and trig	ABS, ROUND, POWER, SUMPRODUCT, SEQUENCE
Statistical	AVERAGE, COUNTIFS, MEDIAN, PERCENTILE, RANK.EQ
Text	TEXT, CONCAT, LEFT, MID, SEARCH, SUBSTITUTE
Date and time	DATE, DATEDIF, DATEVALUE, NETWORKDAYS, NOW
Lookup and reference	INDEX, MATCH, XLOOKUP, FILTER, CHOOSECOLS, DROP
Logical and lambda	IF, IFS, LET, LAMBDA, MAP, BYROW, SORT
Financial	PMT, PV, FV, NPV, IRR, RATE
Engineering and information	CONVERT, BIN2DEC, FORMULATEXT, ISFORMULA, ERROR.TYPE

See the full Formula Functions reference for details on each function.

Circular References

werkbook detects circular references and returns a #REF! error:

sheet.SetFormula("A1", "B1+1")
sheet.SetFormula("B1", "A1+1")

v, _ := sheet.GetValue("A1")
fmt.Println(v.Type)   // TypeError
fmt.Println(v.String) // "#REF!"

Operators

Formulas support the standard spreadsheet formula operators:

Operator	Description	Example
+	Addition	A1+B1
-	Subtraction	A1-B1
*	Multiplication	A1*B1
/	Division	A1/B1
^	Exponentiation	A1^2
&	Text concatenation	A1&" "&B1
=	Equal	IF(A1=0,...)
<>	Not equal	IF(A1<>0,...)
>, >=	Greater than	IF(A1>100,...)
<, <=	Less than	IF(A1<0,...)

Whole-column references such as A:A are also supported in many common formulas.