Formula Engine

ReoGrid Web includes an Excel-compatible formula engine with 109 built-in functions across math, statistics, trigonometry, logical, text, date/time, lookup, info, conditional aggregation, and random categories. The engine features an AST parser, evaluator, and dependency graph for real-time recalculation.

The Lite edition supports arithmetic operators (+, -, *, /, ^, &), comparison operators, and cell references only. All 109 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")'
ws.cell('C5').value = '=VLOOKUP("Apple", A1:B100, 2, FALSE)'

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`

Smart Formula Editor

While editing a formula (cell starting with =), the editor activates Excel-style reference editing:

Click another cell to insert that cell’s address at the caret. Drag to extend it into a range like A1:B5.
Click a different cell again to re-target the just-inserted reference (point mode). Typing or pressing arrow keys exits point mode.
Color-coded references. Each unique reference in the formula is colored from a shared palette; the grid draws a matching dashed rectangle around each referenced range using the same color. Highlight rectangles expand to enclose merged regions.

No API call is required — the editor activates automatically whenever a formula is being edited.

Built-in Functions

Full reference of all 109 built-in functions, grouped by category.

Math & Arithmetic (22)

Function	Syntax	Description
`SUM`	`SUM(value1, value2, ...)`	Sum
`PRODUCT`	`PRODUCT(value1, value2, ...)`	Product
`AVERAGE`	`AVERAGE(value1, value2, ...)`	Average
`COUNT`	`COUNT(value1, value2, ...)`	Count of numeric cells
`COUNTA`	`COUNTA(value1, value2, ...)`	Count of non-empty cells
`COUNTBLANK`	`COUNTBLANK(range)`	Count of blank cells
`MAX`	`MAX(value1, value2, ...)`	Maximum value
`MIN`	`MIN(value1, value2, ...)`	Minimum value
`ROUND`	`ROUND(number, digits)`	Round to nearest
`ROUNDUP`	`ROUNDUP(number, digits)`	Round away from zero
`ROUNDDOWN`	`ROUNDDOWN(number, digits)`	Round toward zero
`ABS`	`ABS(number)`	Absolute value
`MOD`	`MOD(number, divisor)`	Remainder
`INT`	`INT(number)`	Truncate toward negative infinity
`TRUNC`	`TRUNC(number, [digits])`	Truncate toward zero
`CEILING`	`CEILING(number, significance)`	Round up to multiple
`FLOOR`	`FLOOR(number, significance)`	Round down to multiple
`MROUND`	`MROUND(number, multiple)`	Round to nearest multiple
`POWER`	`POWER(base, exponent)`	Exponentiation
`SQRT`	`SQRT(number)`	Square root
`SIGN`	`SIGN(number)`	Sign (1 / 0 / -1)
`SUMPRODUCT`	`SUMPRODUCT(array1, [array2, ...])`	Element-wise product sum

Statistics (4)

Function	Syntax	Description
`MEDIAN`	`MEDIAN(value1, ...)`	Median
`LARGE`	`LARGE(range, k)`	k-th largest value
`SMALL`	`SMALL(range, k)`	k-th smallest value
`RANK`	`RANK(value, range, [order])`	Rank (`order=0` for descending)

Trigonometry & Logarithms (12)

Function	Syntax	Description
`EXP`	`EXP(number)`	e raised to the power
`LN`	`LN(number)`	Natural logarithm
`LOG`	`LOG(number, [base])`	Logarithm (base defaults to 10)
`LOG10`	`LOG10(number)`	Common logarithm
`SIN`	`SIN(number)`	Sine (radians)
`COS`	`COS(number)`	Cosine (radians)
`TAN`	`TAN(number)`	Tangent (radians)
`ASIN`	`ASIN(number)`	Arcsine
`ACOS`	`ACOS(number)`	Arccosine
`ATAN`	`ATAN(number)`	Arctangent
`ATAN2`	`ATAN2(x, y)`	Excel-compatible argument order
`PI`	`PI()`	Pi

Random (2)

Function	Syntax	Description
`RAND`	`RAND()`	Uniform random in [0, 1)
`RANDBETWEEN`	`RANDBETWEEN(low, high)`	Uniform integer random

Note: RAND / RANDBETWEEN are currently not volatile — they do not auto-recalculate on every change. Re-enter the cell to refresh.

Logical (8)

Function	Syntax	Description
`IF`	`IF(condition, true_val, [false_val])`	Conditional branching
`IFS`	`IFS(cond1, value1, ...)`	Multi-branch (Excel 2019+)
`IFERROR`	`IFERROR(value, error_value)`	Catch any error
`IFNA`	`IFNA(value, na_value)`	Catch `#N/A` only
`AND`	`AND(cond1, ...)`	Logical AND (short-circuit)
`OR`	`OR(cond1, ...)`	Logical OR (short-circuit)
`NOT`	`NOT(condition)`	Logical negation
`SWITCH`	`SWITCH(expr, val1, result1, ..., [default])`	Match-by-value branching

Text (21)

Function	Syntax	Description
`LEN`	`LEN(text)`	Character count
`LEFT`	`LEFT(text, [n])`	First n characters
`RIGHT`	`RIGHT(text, [n])`	Last n characters
`MID`	`MID(text, start, length)`	Substring
`UPPER`	`UPPER(text)`	Convert to uppercase
`LOWER`	`LOWER(text)`	Convert to lowercase
`PROPER`	`PROPER(text)`	Capitalize first letter of each word
`TRIM`	`TRIM(text)`	Collapse whitespace
`CONCATENATE`	`CONCATENATE(text1, ...)`	Concatenation (no ranges)
`CONCAT`	`CONCAT(value1, ...)`	Concatenation (ranges expanded)
`TEXTJOIN`	`TEXTJOIN(delimiter, ignore_empty, value1, ...)`	Delimiter-joined concatenation
`TEXT`	`TEXT(value, format)`	Format number as string
`VALUE`	`VALUE(text)`	Parse string to number
`FIND`	`FIND(search, text, [start])`	Case-sensitive position
`SEARCH`	`SEARCH(search, text, [start])`	Case-insensitive position; supports wildcards
`EXACT`	`EXACT(text1, text2)`	Case-sensitive equality
`SUBSTITUTE`	`SUBSTITUTE(text, old, new, [occurrence])`	Replace by content
`REPLACE`	`REPLACE(text, start, count, new)`	Replace by position
`REPT`	`REPT(text, count)`	Repeat
`CHAR`	`CHAR(code)`	Code point to character
`CODE`	`CODE(text)`	Code point of first character

Date & Time (14)

Function	Syntax	Description
`TODAY`	`TODAY()`	Today’s serial (integer)
`NOW`	`NOW()`	Current date and time serial
`DATE`	`DATE(year, month, day)`	Build date serial
`YEAR`	`YEAR(serial)`	Year
`MONTH`	`MONTH(serial)`	Month (1-12)
`DAY`	`DAY(serial)`	Day (1-31)
`HOUR`	`HOUR(serial)`	Hour (0-23)
`MINUTE`	`MINUTE(serial)`	Minute (0-59)
`SECOND`	`SECOND(serial)`	Second (0-59)
`WEEKDAY`	`WEEKDAY(serial, [type])`	Day of week (types 1-3, 11-17)
`EDATE`	`EDATE(start, months)`	Add months (month-end clamped)
`EOMONTH`	`EOMONTH(start, months)`	End-of-month n months later
`DAYS`	`DAYS(end, start)`	Difference in days
`DATEDIF`	`DATEDIF(start, end, unit)`	Period (unit: D/M/Y/MD/YM/YD)

Lookup & Reference (11)

Function	Syntax	Description
`VLOOKUP`	`VLOOKUP(value, table, col_index, [approx])`	Vertical table lookup
`HLOOKUP`	`HLOOKUP(value, table, row_index, [approx])`	Horizontal table lookup
`XLOOKUP`	`XLOOKUP(value, lookup_range, return_range, [default], [match_mode], [search_mode])`	Modern lookup
`INDEX`	`INDEX(range, row, [column])`	Value by position
`MATCH`	`MATCH(value, range, [match_type])`	Position of value
`XMATCH`	`XMATCH(value, range, [match_mode], [search_mode])`	Modern MATCH
`ROW`	`ROW([reference])`	Row number
`COLUMN`	`COLUMN([reference])`	Column number
`ROWS`	`ROWS(range)`	Number of rows in range
`COLUMNS`	`COLUMNS(range)`	Number of columns in range
`ADDRESS`	`ADDRESS(row, column, [abs], [a1], [sheet])`	Build A1/R1C1 address string

Limitation: Single-cell ROW(A5) is not currently supported (returns #VALUE!). Argument-less ROW() / COLUMN() and range arguments ROW(A1:A10) work.

Not yet implemented: INDIRECT and OFFSET require volatile-cell support in the dependency graph; planned for a future release.

Info & Error Checking (9)

Function	Syntax	Description
`ISBLANK`	`ISBLANK(value)`	TRUE if blank
`ISNUMBER`	`ISNUMBER(value)`	TRUE if numeric
`ISTEXT`	`ISTEXT(value)`	TRUE if text
`ISLOGICAL`	`ISLOGICAL(value)`	TRUE if boolean
`ISERROR`	`ISERROR(value)`	TRUE if any error (including `#N/A`)
`ISERR`	`ISERR(value)`	TRUE if error other than `#N/A`
`ISNA`	`ISNA(value)`	TRUE if `#N/A`
`NA`	`NA()`	Returns `#N/A`
`ERROR.TYPE`	`ERROR.TYPE(value)`	Error code number (1-8)

Conditional Aggregation — `IF` / `IFS` (6)

Function	Syntax	Description
`SUMIF`	`SUMIF(range, criteria, [sum_range])`	Single-criteria sum
`COUNTIF`	`COUNTIF(range, criteria)`	Single-criteria count
`AVERAGEIF`	`AVERAGEIF(range, criteria, [average_range])`	Single-criteria average
`SUMIFS`	`SUMIFS(sum_range, range1, criteria1, ...)`	Multi-criteria sum
`COUNTIFS`	`COUNTIFS(range1, criteria1, ...)`	Multi-criteria count
`AVERAGEIFS`	`AVERAGEIFS(average_range, range1, criteria1, ...)`	Multi-criteria average

Criteria syntax

SUMIF / COUNTIF / AVERAGEIF and their *IFS variants accept:

Example	Meaning
`5`	Value equals 5
`"apple"`	Text equals apple (case-insensitive)
`">5"`	Greater than 5
`">=10"`	Greater than or equal to 10
`"<>0"`	Not equal to 0
`"a*"`	Text starts with `a`
`"?at"`	Any single character + `at`

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 with Lookup

const ws = grid.worksheet

// Price table (A1:B4)
ws.cell('A1').value = 'Product'; ws.cell('B1').value = 'Unit Price'
ws.cell('A2').value = 'Widget';  ws.cell('B2').value = '500'
ws.cell('A3').value = 'Gadget';  ws.cell('B3').value = '1200'
ws.cell('A4').value = 'Sprocket'; ws.cell('B4').value = '350'

// Line items (D1:G4)
ws.cell('D1').value = 'Item'; ws.cell('E1').value = 'Qty'
ws.cell('F1').value = 'Price'; ws.cell('G1').value = 'Amount'

ws.cell('D2').value = 'Widget'
ws.cell('E2').value = '10'
ws.cell('F2').value = '=VLOOKUP(D2,$A$2:$B$4,2,FALSE)'  // -> 500
ws.cell('G2').value = '=E2*F2'                          // -> 5000

// Total
ws.cell('G5').value = '=SUM(G2:G4)'
ws.cell('G6').value = '=ROUND(G5*0.1,0)'  // 10% tax
ws.cell('G7').value = '=G5+G6'            // total

Edition Comparison

Feature	Lite (Free)	Pro (Paid)
Arithmetic & comparison operators	Yes	Yes
Cell & range references	Yes	Yes
Dependency graph	Yes	Yes
Smart formula editor (color-coded refs)	Yes	Yes
Built-in functions (109)	—	Yes