Formula Engine

ReoGrid Web includes an Excel-compatible formula engine. It features an AST parser, evaluator, and dependency graph for real-time recalculation.

The Lite edition supports arithmetic operators and cell references. All 32+ built-in functions require the Pro edition.

Live Demo

Basic Usage

Cell values starting with = are interpreted as formulas.

const ws = grid.worksheet

// Arithmetic operations
ws.cell('C1').value = '=A1+B1'
ws.cell('C2').value = '=A2*B2'

// Function calls
ws.cell('C3').value = '=SUM(A1:A10)'
ws.cell('C4').value = '=IF(A1>100,"High","Low")'

Cell References

Format	Description	Example
Relative reference	Row and column indices shift	`A1`, `B2`
Absolute reference	Fixed with `$`	`$A$1`, `A$1`, `$A1`
Range reference	Range specified with colon	`A1:B10`

Built-in Functions

Math Functions

Function	Syntax	Description
`SUM`	`SUM(range)`	Sum
`AVERAGE`	`AVERAGE(range)`	Average
`MIN`	`MIN(range)`	Minimum value
`MAX`	`MAX(range)`	Maximum value
`COUNT`	`COUNT(range)`	Count of numeric cells
`COUNTA`	`COUNTA(range)`	Count of non-empty cells
`COUNTIF`	`COUNTIF(range, criteria)`	Count of cells matching criteria
`ABS`	`ABS(number)`	Absolute value
`SQRT`	`SQRT(number)`	Square root
`POWER`	`POWER(base, exp)`	Exponentiation
`MOD`	`MOD(number, divisor)`	Remainder
`ROUND`	`ROUND(number, digits)`	Round
`PRODUCT`	`PRODUCT(range)`	Product

Logical Functions

Function	Syntax	Description
`IF`	`IF(condition, true_val, false_val)`	Conditional branching
`AND`	`AND(cond1, cond2, ...)`	TRUE if all are true
`OR`	`OR(cond1, cond2, ...)`	TRUE if any is true
`NOT`	`NOT(condition)`	Logical negation

String Functions

Function	Syntax	Description
`CONCATENATE`	`CONCATENATE(str1, str2, ...)`	String concatenation
`LEN`	`LEN(text)`	Character count
`UPPER`	`UPPER(text)`	Convert to uppercase
`LOWER`	`LOWER(text)`	Convert to lowercase
`TRIM`	`TRIM(text)`	Remove leading/trailing whitespace
`FIND`	`FIND(search, text, start?)`	Find character position
`REPLACE`	`REPLACE(text, start, count, new)`	Replace characters
`SUBSTRING`	`SUBSTRING(text, start, length?)`	Substring

Lookup Functions

Function	Syntax	Description
`VLOOKUP`	`VLOOKUP(value, range, col_index, approx?)`	Vertical lookup
`HLOOKUP`	`HLOOKUP(value, range, row_index, approx?)`	Horizontal lookup

Date Functions

Function	Syntax	Description
`TODAY`	`TODAY()`	Today’s date (serial value)
`NOW`	`NOW()`	Current date and time (serial value)

Type Checking Functions

Function	Syntax	Description
`ISNUMBER`	`ISNUMBER(value)`	TRUE if numeric
`ISTEXT`	`ISTEXT(value)`	TRUE if text
`ISBLANK`	`ISBLANK(cell)`	TRUE if empty
`ISERROR`	`ISERROR(value)`	TRUE if error

Automatic Dependency Tracking

Dependencies between formulas are tracked automatically. When a cell value changes, all formulas that reference that cell are automatically recalculated.

ws.cell('A1').value = '10'
ws.cell('B1').value = '20'
ws.cell('C1').value = '=A1+B1'  // -> 30

// Changing A1 automatically recalculates C1
ws.cell('A1').value = '50'      // C1 -> 70

You can rebuild formula dependencies after bulk data changes:

ws.rebuildFormulas()

Example: Invoice Calculations

const ws = grid.worksheet

// Headers
ws.cell('A1').value = 'Item'
ws.cell('B1').value = 'Quantity'
ws.cell('C1').value = 'Unit Price'
ws.cell('D1').value = 'Amount'

// Data rows
ws.cell('A2').value = 'Widget'
ws.cell('B2').value = '10'
ws.cell('C2').value = '500'
ws.cell('D2').value = '=B2*C2'   // -> 5000

ws.cell('A3').value = 'Gadget'
ws.cell('B3').value = '5'
ws.cell('C3').value = '1200'
ws.cell('D3').value = '=B3*C3'   // -> 6000

// Subtotal
ws.cell('D5').value = '=SUM(D2:D3)'      // -> 11000

// Tax
ws.cell('D6').value = '=ROUND(D5*0.1,0)' // -> 1100

// Total
ws.cell('D7').value = '=D5+D6'           // -> 12100

Edition Comparison

Feature	Lite (Free)	Pro (Paid)
Arithmetic operators (`+`, `-`, `*`, `/`)	Yes	Yes
Cell & range references	Yes	Yes
Dependency graph	Yes	Yes
Built-in functions (32+)	—	Yes