Native Sheets formula reference

Native Sheets cells accept a single formula language: a fixed, sandboxed set of functions and reference types, deliberately smaller than Excel or Google Sheets. This page documents exactly what that formula language accepts. Anything not listed here is rejected at parse time as #NAME? — it is not a temporary gap, it is the boundary of what v1 supports.

Writing a formula

Start a cell’s contents with = to enter a formula, e.g. =SUM(A1:A10). Formulas support:

Number, text ("like this"), and boolean (TRUE/FALSE) literals
Arithmetic operators: + - * / ^ (plus unary +/- for signs)
Comparison operators: = <> < > <= >=
Parentheses for grouping
Function calls, e.g. IF(A1>0, "positive", "not positive")
Cell and range references (see below)

There is no string concatenation operator and no array-literal syntax in v1.

Cell and range references

Syntax	Meaning
`A1`	A single cell on the same tab as the formula
`A1:B20`	A rectangular range on the same tab
`Drivers!B2`	A single cell on another tab (`Drivers`) in the same sheet
`Drivers!B2:B10`	A range on another tab in the same sheet
`'Q4 Actuals'!A1`	A tab name that contains spaces or other special characters must be quoted with single quotes

Drivers!B2 and 'Drivers'!B2 both work — quoting is only required when the tab name needs it (spaces, punctuation, etc.), but it’s always accepted.

Supported functions

Aggregation

Function	Signature	Description
`SUM`	`SUM(value1, [value2, ...])`	Sum of all numeric values; text and blanks are ignored
`AVERAGE`	`AVERAGE(value1, [value2, ...])`	Arithmetic mean of all numeric values
`MIN`	`MIN(value1, [value2, ...])`	Smallest numeric value
`MAX`	`MAX(value1, [value2, ...])`	Largest numeric value
`COUNT`	`COUNT(value1, [value2, ...])`	Count of numeric values (text and blanks not counted)
`COUNTA`	`COUNTA(value1, [value2, ...])`	Count of non-blank values of any type

SUM/AVERAGE/MIN/MAX/COUNT silently skip blank cells and non-numeric text; booleans count as 1/0. COUNTA counts every non-blank cell regardless of type.

Conditional aggregation

Function	Signature	Description
`SUMIFS`	`SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])`	Sum of `sum_range` where every criteria pair matches
`COUNTIFS`	`COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])`	Count of rows where every criteria pair matches
`AVERAGEIFS`	`AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])`	Average of `average_range` where every criteria pair matches; errors (`#DIV0!`) if nothing matches

Every criteria range must be the same size as the sum/average/first range. A criterion can be:

A comparison, e.g. ">100", "<>0"
A wildcard pattern using * (any run of characters) and ? (any single character), e.g. "Q?-Actuals*"
A literal value to match exactly (case-insensitive for text)

Logic and errors

Function	Signature	Description
`IF`	`IF(condition, value_if_true, [value_if_false])`	Returns one of two values depending on `condition`; `value_if_false` defaults to `FALSE` if omitted
`AND`	`AND(value1, [value2, ...])`	`TRUE` if every argument is truthy
`OR`	`OR(value1, [value2, ...])`	`TRUE` if any argument is truthy
`IFERROR`	`IFERROR(value, value_if_error)`	Returns `value`, or `value_if_error` if evaluating `value` raised a formula error

Lookup

Function	Signature	Description
`XLOOKUP`	`XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`	Looks up `lookup_value` in `lookup_array` and returns the corresponding entry from `return_array`
`INDEX`	`INDEX(array, row_num, [column_num])`	Returns the value at a given row/column position within `array`
`MATCH`	`MATCH(lookup_value, lookup_array, [match_type])`	Returns the 1-based position of `lookup_value` within `lookup_array`

XLOOKUP’s match_mode is 0 (exact, default), -1 (exact, else next-smaller), 1 (exact, else next-larger), or 2 (wildcard match on a text lookup_value). search_mode is 1 (first-to-last, default) or -1/-2 (last-to-first). If no match is found and if_not_found is omitted, the formula raises #REF!.

MATCH’s match_type is 1 (largest value <= lookup_value, requires ascending order, default), 0 (exact match), or -1 (smallest value >= lookup_value, requires descending order).

Dates

Function	Signature	Description
`DATE`	`DATE(year, month, day)`	Builds a date from year/month/day parts
`YEAR`	`YEAR(date)`	Year component of a date
`MONTH`	`MONTH(date)`	Month component (1-12) of a date
`DAY`	`DAY(date)`	Day-of-month component of a date
`EDATE`	`EDATE(start_date, months)`	Date `months` months before/after `start_date`, same day-of-month (clamped to month length)
`EOMONTH`	`EOMONTH(start_date, months)`	Last day of the month that is `months` months before/after `start_date`
`YEARFRAC`	`YEARFRAC(start_date, end_date, [basis])`	Fraction of a year between two dates; `basis` is `0` = 30/360 US (default), `1` = actual/actual, `2` = actual/360, `3` = actual/365, `4` = 30/360 European

Dates are stored as serial numbers using the Google Sheets epoch (serial 0 = 1899-12-30), not Excel’s — there is no 1900 leap-year bug.

Rounding and signs

Function	Signature	Description
`ROUND`	`ROUND(value, num_digits)`	Rounds to `num_digits` decimal places, halves away from zero
`ROUNDUP`	`ROUNDUP(value, num_digits)`	Rounds away from zero to `num_digits` decimal places
`ROUNDDOWN`	`ROUNDDOWN(value, num_digits)`	Rounds toward zero to `num_digits` decimal places
`ABS`	`ABS(value)`	Absolute value

Financial calculations

Function	Signature	Description
`XNPV`	`XNPV(rate, values, dates)`	Net present value of cash flows on irregular dates
`XIRR`	`XIRR(values, dates, [guess])`	Internal rate of return for cash flows on irregular dates; requires at least one positive and one negative cash flow
`PMT`	`PMT(rate, nper, pv, [fv], [when_due])`	Payment amount for a loan/annuity given a constant rate and number of periods

What’s not supported

By design, Native Sheets v1 formulas cannot reference or execute anything outside the sheet they live in:

Not supported	Why
Cross-model, cross-run, or cross-branch references	A cell’s value must be fully determined by the sheet itself
References to another sheet or an external file	No syntax exists for it — it simply doesn’t parse
Network/import formulas (`IMPORTRANGE`, web requests, etc.)	Sandboxed evaluation — no I/O
Volatile functions (`NOW`, `TODAY`, `RAND`, …)	Not in the v1 function set, so any use of them fails to parse as `#NAME?`
String concatenation operator, array literals	Not part of the v1 grammar
Any function not listed above	Rejected at parse time as `#NAME?`

This list is generated from the same allowlist the parser enforces — see services/mcp_server/domain/native_sheets_formula.py’s ALLOWED_FUNCTIONS if you’re checking whether a specific function exists.