# Formulas Reference

Complete reference for all 140+ GeoSheet spreadsheet formulas including math, text, date, lookup, statistical, spatial, and temporal functions.

GeoSheet includes 140+ formula functions. Type `=` in any cell to start a formula. The formula bar shows autocomplete suggestions as you type.

## How Formulas Work

* Formulas start with `=` (e.g., `=SUM(A1:A10)`)
* Cell references: `A1` (relative), `$A$1` (absolute), `A1:B10` (range)
* Cross-sheet references: `Sheet2!A1`
* Named ranges: define in the Name Box, use by name in formulas (e.g., `=SUM(revenue)`)
* Array formulas return multiple values — shown with a pill indicator

### Special Forms

These are not regular functions — they bind names or accept functions as arguments:

* `LET(name, value, ..., expression)` — define local variables
* `LAMBDA(params..., body)` — create reusable functions
* `MAP(array, lambda)` — apply a function to each element
* `FILTER(array, condition)` — filter rows by condition
* `REDUCE(initial, array, lambda)` — accumulate values

### Temporal Operator (Time Warp)

Use `@` to look up historical cell values. Requires [Time Warp](./time-warp.md) to be enabled for the workbook.

```
=A1@"2026-01-01"           # Value of A1 on Jan 1, 2026
=SUM(A1:A10@"2026-06-15")  # Historical sum
=A1 - A1@"2026-01-01"      # Change since start of year
```

Temporal references work with any formula — combine them with lookups, conditionals, and aggregations to compare current data against historical baselines. See the [Time Warp guide](./time-warp.md) for more examples.

***

## Function Reference

### Math

| Function                    | Description              | Example                |
| --------------------------- | ------------------------ | ---------------------- |
| `SUM(range)`                | Sum of values            | `=SUM(A1:A10)`         |
| `AVERAGE(range)`            | Arithmetic mean          | `=AVERAGE(B1:B100)`    |
| `MIN(range)`                | Minimum value            | `=MIN(A1:A10)`         |
| `MAX(range)`                | Maximum value            | `=MAX(A1:A10)`         |
| `COUNT(range)`              | Count of numbers         | `=COUNT(A1:A10)`       |
| `COUNTA(range)`             | Count of non-empty cells | `=COUNTA(A1:A10)`      |
| `ROUND(number, digits)`     | Round to N decimals      | `=ROUND(3.14159, 2)`   |
| `ROUNDUP(number, digits)`   | Round up                 | `=ROUNDUP(3.1, 0)`     |
| `ROUNDDOWN(number, digits)` | Round down               | `=ROUNDDOWN(3.9, 0)`   |
| `ABS(number)`               | Absolute value           | `=ABS(-5)`             |
| `SQRT(number)`              | Square root              | `=SQRT(16)`            |
| `POWER(base, exp)`          | Exponentiation           | `=POWER(2, 10)`        |
| `MOD(number, divisor)`      | Remainder                | `=MOD(10, 3)`          |
| `FLOOR(number, sig)`        | Round down to multiple   | `=FLOOR(7.8, 1)`       |
| `CEILING(number, sig)`      | Round up to multiple     | `=CEILING(7.2, 1)`     |
| `LOG(number, [base])`       | Logarithm                | `=LOG(100, 10)`        |
| `EXP(number)`               | e raised to power        | `=EXP(1)`              |
| `RAND()`                    | Random 0–1               | `=RAND()`              |
| `RANDBETWEEN(low, high)`    | Random integer           | `=RANDBETWEEN(1, 100)` |
| `PI()`                      | Pi constant              | `=PI()`                |
| `PRODUCT(range)`            | Product of values        | `=PRODUCT(A1:A5)`      |
| `SUMSQ(range)`              | Sum of squares           | `=SUMSQ(A1:A5)`        |

### Text

| Function                               | Description                      | Example                          |
| -------------------------------------- | -------------------------------- | -------------------------------- |
| `CONCAT(text, ...)`                    | Join text                        | `=CONCAT(A1, " ", B1)`           |
| `CONCATENATE(text, ...)`               | Join text (alias)                | `=CONCATENATE(A1, B1)`           |
| `TEXTJOIN(delim, ignore_empty, range)` | Join with delimiter              | `=TEXTJOIN(", ", TRUE, A1:A5)`   |
| `LEN(text)`                            | Character count                  | `=LEN("hello")`                  |
| `LEFT(text, n)`                        | First N chars                    | `=LEFT("hello", 3)`              |
| `RIGHT(text, n)`                       | Last N chars                     | `=RIGHT("hello", 3)`             |
| `MID(text, start, n)`                  | Substring                        | `=MID("hello", 2, 3)`            |
| `UPPER(text)`                          | Uppercase                        | `=UPPER("hello")`                |
| `LOWER(text)`                          | Lowercase                        | `=LOWER("HELLO")`                |
| `PROPER(text)`                         | Title case                       | `=PROPER("hello world")`         |
| `TRIM(text)`                           | Remove extra spaces              | `=TRIM("  hello  ")`             |
| `CLEAN(text)`                          | Remove non-printable chars       | `=CLEAN(A1)`                     |
| `FIND(find, text, [start])`            | Find position (case-sensitive)   | `=FIND("l", "hello")`            |
| `SEARCH(find, text, [start])`          | Find position (case-insensitive) | `=SEARCH("L", "hello")`          |
| `REPLACE(text, start, n, new)`         | Replace by position              | `=REPLACE("hello", 1, 1, "H")`   |
| `SUBSTITUTE(text, old, new)`           | Replace by text                  | `=SUBSTITUTE("hello", "l", "r")` |
| `EXACT(text1, text2)`                  | Case-sensitive comparison        | `=EXACT("Hello", "hello")`       |
| `REPT(text, n)`                        | Repeat text                      | `=REPT("*", 5)`                  |
| `VALUE(text)`                          | Convert text to number           | `=VALUE("42")`                   |

### Logical

| Function                         | Description         | Example                         |
| -------------------------------- | ------------------- | ------------------------------- |
| `IF(condition, then, else)`      | Conditional         | `=IF(A1>10, "High", "Low")`     |
| `IFS(cond1, val1, ...)`          | Multiple conditions | `=IFS(A1>90,"A", A1>80,"B")`    |
| `AND(cond, ...)`                 | All true            | `=AND(A1>0, B1>0)`              |
| `OR(cond, ...)`                  | Any true            | `=OR(A1>0, B1>0)`               |
| `NOT(condition)`                 | Negate              | `=NOT(A1>10)`                   |
| `IFERROR(value, fallback)`       | Catch errors        | `=IFERROR(A1/B1, 0)`            |
| `IFNA(value, fallback)`          | Catch #N/A          | `=IFNA(VLOOKUP(...), "")`       |
| `SWITCH(expr, case1, val1, ...)` | Switch/case         | `=SWITCH(A1, 1,"One", 2,"Two")` |

### Lookup

| Function                             | Description           | Example                            |
| ------------------------------------ | --------------------- | ---------------------------------- |
| `VLOOKUP(key, range, col, [approx])` | Vertical lookup       | `=VLOOKUP("Alice", A:C, 3, FALSE)` |
| `HLOOKUP(key, range, row, [approx])` | Horizontal lookup     | `=HLOOKUP("Q1", A1:D2, 2)`         |
| `INDEX(range, row, [col])`           | Value at position     | `=INDEX(A1:C10, 3, 2)`             |
| `MATCH(key, range, [type])`          | Find position         | `=MATCH("Alice", A:A, 0)`          |
| `OFFSET(ref, rows, cols, [h], [w])`  | Offset reference      | `=OFFSET(A1, 2, 1)`                |
| `CHOOSE(index, val1, val2, ...)`     | Choose by index       | `=CHOOSE(2, "a", "b", "c")`        |
| `INDIRECT(ref_text)`                 | Reference from string | `=INDIRECT("A" & B1)`              |

### Statistical

| Function                 | Description                     | Example                     |
| ------------------------ | ------------------------------- | --------------------------- |
| `STDEV(range)`           | Standard deviation (sample)     | `=STDEV(A1:A100)`           |
| `STDEVP(range)`          | Standard deviation (population) | `=STDEVP(A1:A100)`          |
| `VAR(range)`             | Variance (sample)               | `=VAR(A1:A100)`             |
| `VARP(range)`            | Variance (population)           | `=VARP(A1:A100)`            |
| `MEDIAN(range)`          | Median value                    | `=MEDIAN(A1:A100)`          |
| `PERCENTILE(range, k)`   | Kth percentile                  | `=PERCENTILE(A1:A100, 0.9)` |
| `CORREL(range1, range2)` | Correlation                     | `=CORREL(A1:A100, B1:B100)` |
| `LARGE(range, k)`        | Kth largest                     | `=LARGE(A1:A100, 3)`        |
| `SMALL(range, k)`        | Kth smallest                    | `=SMALL(A1:A100, 3)`        |
| `RANK(value, range)`     | Rank in range                   | `=RANK(A1, A:A)`            |

### Conditional

| Function                                    | Description            | Example                               |
| ------------------------------------------- | ---------------------- | ------------------------------------- |
| `SUMIF(range, criteria, [sum_range])`       | Conditional sum        | `=SUMIF(A:A, ">10", B:B)`             |
| `COUNTIF(range, criteria)`                  | Conditional count      | `=COUNTIF(A:A, "Yes")`                |
| `AVERAGEIF(range, criteria, [avg_range])`   | Conditional average    | `=AVERAGEIF(A:A, ">0", B:B)`          |
| `SUMIFS(sum_range, range1, crit1, ...)`     | Multi-criteria sum     | `=SUMIFS(C:C, A:A, "NY", B:B, ">10")` |
| `COUNTIFS(range1, crit1, ...)`              | Multi-criteria count   | `=COUNTIFS(A:A, "NY", B:B, ">10")`    |
| `AVERAGEIFS(avg_range, range1, crit1, ...)` | Multi-criteria average | `=AVERAGEIFS(C:C, A:A, "NY")`         |
| `MINIFS(min_range, range1, crit1, ...)`     | Multi-criteria min     | `=MINIFS(C:C, A:A, "NY")`             |
| `MAXIFS(max_range, range1, crit1, ...)`     | Multi-criteria max     | `=MAXIFS(C:C, A:A, "NY")`             |

Criteria support: exact match (`"NY"`), operators (`">10"`, `"<=5"`, `"<>abc"`), wildcards (`"app*"`, `"?at"`).

### Date & Time

| Function                    | Description         | Example                 |
| --------------------------- | ------------------- | ----------------------- |
| `TODAY()`                   | Current date        | `=TODAY()`              |
| `NOW()`                     | Current date + time | `=NOW()`                |
| `DATE(year, month, day)`    | Create date         | `=DATE(2025, 6, 15)`    |
| `YEAR(date)`                | Extract year        | `=YEAR(A1)`             |
| `MONTH(date)`               | Extract month       | `=MONTH(A1)`            |
| `DAY(date)`                 | Extract day         | `=DAY(A1)`              |
| `HOUR(datetime)`            | Extract hour        | `=HOUR(A1)`             |
| `MINUTE(datetime)`          | Extract minute      | `=MINUTE(A1)`           |
| `DATEDIF(start, end, unit)` | Date difference     | `=DATEDIF(A1, B1, "D")` |
| `DAYS(end, start)`          | Days between dates  | `=DAYS(B1, A1)`         |
| `WEEKDAY(date)`             | Day of week (1-7)   | `=WEEKDAY(A1)`          |
| `EOMONTH(date, months)`     | End of month        | `=EOMONTH(A1, 1)`       |
| `EDATE(date, months)`       | Add months          | `=EDATE(A1, 3)`         |

### Duration

| Function                  | Description      | Example               |
| ------------------------- | ---------------- | --------------------- |
| `DURATION(h, m, s)`       | Create duration  | `=DURATION(2, 30, 0)` |
| `HOURS(duration)`         | Hour component   | `=HOURS(A1)`          |
| `MINUTES(duration)`       | Minute component | `=MINUTES(A1)`        |
| `SECONDS(duration)`       | Second component | `=SECONDS(A1)`        |
| `DURATIONSECS(duration)`  | Total seconds    | `=DURATIONSECS(A1)`   |
| `DURATIONMINS(duration)`  | Total minutes    | `=DURATIONMINS(A1)`   |
| `DURATIONHOURS(duration)` | Total hours      | `=DURATIONHOURS(A1)`  |

### Financial

| Function                    | Description          | Example                         |
| --------------------------- | -------------------- | ------------------------------- |
| `PMT(rate, nper, pv)`       | Payment amount       | `=PMT(0.05/12, 360, 300000)`    |
| `FV(rate, nper, pmt, [pv])` | Future value         | `=FV(0.07/12, 120, -500)`       |
| `PV(rate, nper, pmt)`       | Present value        | `=PV(0.05/12, 360, -1500)`      |
| `NPV(rate, values...)`      | Net present value    | `=NPV(0.1, A1:A10)`             |
| `RATE(nper, pmt, pv)`       | Interest rate        | `=RATE(360, -1500, 300000)`     |
| `IRR(values)`               | Internal return rate | `=IRR(A1:A10)`                  |
| `NPER(rate, pmt, pv)`       | Number of periods    | `=NPER(0.05/12, -1500, 300000)` |
| `XNPV(rate, values, dates)` | NPV with dates       | `=XNPV(0.1, A1:A5, B1:B5)`      |

### Array

Array formulas return multiple values that spill into adjacent cells. They are shown with a pill indicator on the source cell.

| Function                                  | Description                  | Example                 |
| ----------------------------------------- | ---------------------------- | ----------------------- |
| `SEQUENCE(rows, [cols], [start], [step])` | Generate number sequence     | `=SEQUENCE(5, 1, 1, 2)` |
| `UNIQUE(range)`                           | Remove duplicates            | `=UNIQUE(A1:A10)`       |
| `SORT(range, [col], [order])`             | Sort values (1=asc, -1=desc) | `=SORT(A1:B10, 2, -1)`  |
| `TRANSPOSE(range)`                        | Flip rows and columns        | `=TRANSPOSE(A1:D1)`     |

These work with the special forms (`MAP`, `FILTER`, `REDUCE`) for powerful data transformations:

```
=SORT(UNIQUE(A1:A100))                    # Sorted unique values
=FILTER(A1:C100, B1:B100 > 1000)          # Filter rows where B > 1000
=MAP(SEQUENCE(10), LAMBDA(n, n * n))      # First 10 squares
```

### Spatial

See [Spatial Features](./spatial-features.md) for detailed spatial formula documentation.

| Function                      | Description             | Example                   |
| ----------------------------- | ----------------------- | ------------------------- |
| `POINT(lat, lon)`             | Create point            | `=POINT(40.71, -74.00)`   |
| `GEOCODE(address)`            | Address to point        | `=GEOCODE("New York")`    |
| `REVERSE_GEOCODE(point)`      | Point to address        | `=REVERSE_GEOCODE(A1)`    |
| `DISTANCE(p1, p2, [unit])`    | Distance between points | `=DISTANCE(A1, B1, "km")` |
| `LAT(point)` / `LON(point)`   | Extract coordinates     | `=LAT(A1)`                |
| `LINE(points...)`             | Create line             | `=LINE(A1:A5)`            |
| `POLYGON(points...)`          | Create polygon          | `=POLYGON(A1:A10)`        |
| `MULTIPOINT(points...)`       | Create multipoint       | `=MULTIPOINT(A1:A5)`      |
| `COLLECTION(geoms...)`        | Create collection       | `=COLLECTION(A1, B1)`     |
| `CONTAINS(geom, point)`       | Point-in-polygon test   | `=CONTAINS(A1, B1)`       |
| `CENTROID(geometry)`          | Center point            | `=CENTROID(A1)`           |
| `BBOX(geometry)`              | Bounding box            | `=BBOX(A1)`               |
| `BUFFER(geom, radius, unit)`  | Buffer zone             | `=BUFFER(A1, 5, "km")`    |
| `NEAREST(target, range, [n])` | Find nearest            | `=NEAREST(A1, B:B, 3)`    |
| `INTERSECTS(g1, g2)`          | Intersection test       | `=INTERSECTS(A1, B1)`     |
| `INTERSECTION(g1, g2)`        | Compute intersection    | `=INTERSECTION(A1, B1)`   |
| `POLYGON_AREA(poly)`          | Area calculation        | `=POLYGON_AREA(A1)`       |
| `POLYGON_PERIMETER(poly)`     | Perimeter               | `=POLYGON_PERIMETER(A1)`  |

### Routing (async, powered by Mapbox)

| Function                       | Description             | Example                   |
| ------------------------------ | ----------------------- | ------------------------- |
| `DRIVE_TIME(origin, dest)`     | Driving duration        | `=DRIVE_TIME(A1, B1)`     |
| `DRIVE_DISTANCE(origin, dest)` | Driving distance (km)   | `=DRIVE_DISTANCE(A1, B1)` |
| `ROUTE(origin, dest)`          | Road path (LINE)        | `=ROUTE(A1, B1)`          |
| `ISOCHRONE(center, minutes)`   | Drivable area (POLYGON) | `=ISOCHRONE(A1, 15)`      |

### Other

| Function                               | Description                        |
| -------------------------------------- | ---------------------------------- |
| `LOCATION(user, [device], [interval])` | Live GPS position via system sheet |
| `SPARKLINE(range, [kind], [color])`    | Inline chart (line/bar/winloss)    |

### Cross-GeoSheet References

| Function                     | Description                             | Example                                |
| ---------------------------- | --------------------------------------- | -------------------------------------- |
| `IMPORTRANGE(source, range)` | Pull a live range from another GeoSheet | `=IMPORTRANGE("Customers", "A1:E200")` |

`source` accepts a GeoSheet name (`"Customers"`) or URL (`"https://geosheet.app/s/abc123"`). `range` is a single cell, a rectangle, or a tab-qualified range (`"Summary!B2:D50"`). Imported values keep their type — including spatial types — and refresh in the background while the consumer sheet is open. See the [Cross-GeoSheet References guide](./cross-geosheet-references.md) for source-side governance, common patterns, and limitations.

***

## Error Codes

| Error      | Meaning                                        |
| ---------- | ---------------------------------------------- |
| `#DIV/0!`  | Division by zero                               |
| `#VALUE!`  | Wrong argument type                            |
| `#REF!`    | Invalid cell reference                         |
| `#NAME?`   | Unrecognized function or name                  |
| `#N/A`     | Value not found (VLOOKUP, MATCH)               |
| `#NULL!`   | Invalid range intersection                     |
| `#PENDING` | Async formula in progress (geocoding, routing) |