# Formulas and functions

> The Flax formula language — cell and range references, operators, error values, and a reference of the built-in functions grouped by category.

*[View this page in the Flax docs](https://flax-analytics.com/docs/sheets/formulas-and-functions)*

Sheets speak the spreadsheet formula language you already know. This page covers
how to reference cells, the operators available, the error values you may see, and
the built-in functions grouped by category.

## Writing a formula

A formula starts with `=` and can combine literals, references, operators, and
function calls, for example:

```text
=SUM(A1:A10) * 1.2
=IF(B2 > 0, "profit", "loss")
=VLOOKUP(A2, Orders!A:C, 3, FALSE)
```

Unknown function names resolve to `#NAME?` rather than failing silently, so a
mistyped formula is always visible.

## Cell and range references

| Form | Meaning |
| --- | --- |
| `A1` | A single cell (column A, row 1) |
| `$A$1` | Absolute reference (both fixed); mixed forms `$A1` and `A$1` also work |
| `A1:B3` | A rectangular range |
| `A:A` | A whole column |
| `Orders!A1` | A cell on another tab in the same workbook |
| `'Cash Flow'!B2` | A cell on a tab whose name has spaces (single-quoted) |

Cross-tab references are covered in [Workbooks and tabs](/docs/sheets/workbooks-and-tabs).
A range may not span two tabs.

## Operators

| Operator | Purpose |
| --- | --- |
| `+ - * /` | Arithmetic |
| `^` | Exponentiation (right-associative) |
| `&` | String concatenation |
| `= <> < <= > >=` | Comparison |
| unary `+` / `-` | Sign |

`TRUE` and `FALSE` are literals. Text literals use double quotes, with `""` to
escape a quote inside a string.

## Error values

When a formula cannot produce a value it returns a standard error code:

| Error | Meaning |
| --- | --- |
| `#DIV/0!` | Division by zero |
| `#VALUE!` | Wrong type of argument |
| `#NAME?` | Unknown function or unresolved reference |
| `#REF!` | Reference to a missing cell or renamed tab |
| `#N/A` | Value not available (e.g. a failed lookup) |
| `#NUM!` | Invalid numeric result |
| `#CYCLE!` | Circular reference between cells |

## Function reference

The formula engine ships a broad library. The tables below list the functions by
category; each takes arguments the same way as its spreadsheet equivalent.

### Math and rounding

| | | | |
| --- | --- | --- | --- |
| SUM | PRODUCT | ABS | SQRT |
| INT | ROUND | ROUNDUP | ROUNDDOWN |
| TRUNC | MROUND | CEILING | FLOOR |
| MOD | QUOTIENT | POWER | EXP |
| LN | LOG | LOG10 | SIGN |
| GCD | LCM | EVEN | ODD |
| PI | SUMPRODUCT | | |

### Aggregation and conditional aggregation

| | | | |
| --- | --- | --- | --- |
| AVERAGE | COUNT | COUNTA | COUNTBLANK |
| MIN | MAX | SUMIF | SUMIFS |
| COUNTIF | COUNTIFS | AVERAGEIF | AVERAGEIFS |
| MAXIFS | MINIFS | | |

### Statistical

| | | | |
| --- | --- | --- | --- |
| MEDIAN | MODE | STDEV | STDEVP |
| VAR | VARP | LARGE | SMALL |
| RANK | PERCENTILE | QUARTILE | |

### Logical and information

| | | | |
| --- | --- | --- | --- |
| IF | IFS | IFERROR | IFNA |
| SWITCH | AND | OR | XOR |
| NOT | ISBLANK | ISNUMBER | ISTEXT |
| ISERROR | ISNA | ISLOGICAL | N |
| NA | | | |

### Lookup and reference

| | | | |
| --- | --- | --- | --- |
| VLOOKUP | HLOOKUP | XLOOKUP | LOOKUP |
| INDEX | MATCH | CHOOSE | ROWS |
| COLUMNS | | | |

### Text

| | | | |
| --- | --- | --- | --- |
| CONCAT | CONCATENATE | TEXTJOIN | LEN |
| LEFT | RIGHT | MID | FIND |
| SEARCH | REPLACE | SUBSTITUTE | REPT |
| UPPER | LOWER | PROPER | TRIM |
| CLEAN | EXACT | CHAR | CODE |
| VALUE | NUMBERVALUE | | |

### Date and time

| | | | |
| --- | --- | --- | --- |
| DATE | DATEVALUE | YEAR | MONTH |
| DAY | HOUR | MINUTE | SECOND |
| TIME | WEEKDAY | WEEKNUM | EDATE |
| EOMONTH | DAYS | DATEDIF | TODAY |
| NOW | | | |

> [!TIP]
> As you type a function name, the editor suggests matching functions with their
> signatures, so you rarely need to remember exact argument order.

## Next steps

- [Workbooks and tabs](/docs/sheets/workbooks-and-tabs)
- [Editing](/docs/sheets/editing)
- [Overview](/docs/sheets/overview)