# Sheets: Formulas

> Calculating cells, QUERY included.

Glider documentation · Data · Web page: https://cgglider.com/docs/sheets/formulas/

Sections of this page: [Import](https://cgglider.com/docs/sheets.md) · [The grid](https://cgglider.com/docs/sheets/grid.md) · [Formulas](https://cgglider.com/docs/sheets/formulas.md) · [Formatting](https://cgglider.com/docs/sheets/formatting.md) · [Binding cells](https://cgglider.com/docs/sheets/binding.md) · [Live outputs](https://cgglider.com/docs/sheets/feed.md)

A cell that starts with = is a formula, with the syntax Google Sheets and Excel share: operators + − * / ^ & % = <> < > <= >=, references like A1, $A$1, A1:B9, A:A, 2:2 and open-ended A2:A, another tab by name ('Roster 2026'!B2), array literals {1,2;3,4}, and , or ; between arguments.

- Typing a function name opens suggestions; Tab or Enter inserts it. Inside the parentheses, the help card shows the arguments with the current one highlighted.
- Where a reference can go, click a cell or drag a range (or use the arrow keys, Shift to stretch) to insert it. Each reference gets its own colour, in the text and as an outline on the grid.
- On commit, function names and references are upper-cased and missing closing parentheses are added.
- A formula that returns several values spills right and down. If a cell in the way holds content, the formula shows #REF! instead and the note names the cell.
- Errors show their code with a red corner; hover for the reason. #DIV/0!, #VALUE!, #N/A, #REF! (including circular references), #NAME? (unknown function) and #ERROR! (unreadable formula).
- Arithmetic on ranges works element by element (ARRAYFORMULA behaviour) — =A2:A10*B2:B10 spills one product per row.

## QUERY

QUERY(data, query, [headers]) runs the Google Visualization query language: SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, OFFSET and LABEL, with sum / avg / count / min / max, and / or / not, contains, starts with, ends with, matches, like and is (not) null. Columns are the sheet letters when data is a range, Col1, Col2… when it is a computed array. Each column keeps its majority type, and values of another type read as empty, as in Sheets. PIVOT is not supported yet.

```text
=QUERY(A1:C20, "select B, sum(C) where C > 5 group by B order by sum(C) desc label sum(C) 'Total'", 1)
```

## Functions

| Category | Functions |
| --- | --- |
| Math | ABS, CEILING, EXP, FLOOR, INT, LN, LOG, LOG10, MOD, PI, POWER, PRODUCT, ROUND, ROUNDDOWN, ROUNDUP, SIGN, SQRT, SUM, SUMIF, SUMIFS, SUMPRODUCT, TRUNC |
| Statistical | AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COUNTUNIQUE, LARGE, MAX, MAXIFS, MEDIAN, MIN, MINIFS, RANK, SMALL |
| Logical | AND, FALSE, IF, IFERROR, IFNA, IFS, NOT, OR, SWITCH, TRUE, XOR |
| Text | CHAR, CODE, CONCAT, CONCATENATE, EXACT, FIND, JOIN, LEFT, LEN, LOWER, MID, PROPER, REGEXEXTRACT, REGEXMATCH, REGEXREPLACE, REPLACE, REPT, RIGHT, SEARCH, SPLIT, SUBSTITUTE, TEXT, TEXTJOIN, TRIM, UPPER, VALUE |
| Lookup | CHOOSE, COLUMN, COLUMNS, HLOOKUP, INDEX, MATCH, ROW, ROWS, VLOOKUP, XLOOKUP |
| Array | ARRAYFORMULA, FILTER, FLATTEN, QUERY, SEQUENCE, SORT, TRANSPOSE, UNIQUE |
| Info | ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNUMBER, ISODD, ISTEXT, NA |
| Date | DATE, DATEVALUE, DAY, DAYS, EDATE, EOMONTH, HOUR, MINUTE, MONTH, SECOND, TIME, TODAY, WEEKDAY, YEAR |

> **No volatile functions:** NOW, RAND and RANDBETWEEN are left out on purpose: a bound cell is projected into page data, and a value that changes on every recalculation would churn it. TODAY recalculates whenever the sheet changes.