Cookbook

Grid Cookbook

Grid Cookbook

Recipes for common modeling patterns, organized by task.

Each recipe is self-contained: copy, paste, adapt. They use the canonical style — see style-guide.md for the underlying conventions.

Index

Aggregations

Sum a range, ignoring errors

A1 = SUM(IFERROR(B1:B100, 0))
# or
A1 = AGGREGATE(9, 6, B1:B100)   # 9=SUM, 6=ignore errors

Conditional sum

A1 = SUMIF(category, "produce", amounts)
A2 = SUMIFS(amounts, category, "produce", region, "west")

Weighted average

A1 = SUMPRODUCT(weights, values) / SUM(weights)

Running total

A1 = SCAN(0, values, (acc, x) => acc + x)

Top N

A1 = TAKE(SORT(values, -1), 5)        # top 5 descending

Distinct count

A1 = COUNTUNIQUE(items)
A2 = ROWS(UNIQUE(items))

Filtering And Sorting

Filter by predicate

A1 = FILTER(data, condition)
A2 = FILTER(B1:B100, B1:B100 > 0)

Filter with empty fallback

A1 = FILTER(data, condition, "no matches")

Functional filter in a pipeline

A1 = values |> FILTER(value => value > 0, "no matches")
A2 = values |> MAP(value => value * 2) |> FILTER(value => value > 0, "no matches")

Inside a pipe the named lambda is mapped over the current value to form FILTER's include mask. Outside a pipe, pass the include mask directly as in the two recipes above. Use FILTER(value => ...) in new pipelines; the older FILTER(_, _ > 0) spelling is compatibility syntax.

Sort ascending / descending

A1 = SORT(data)              # ascending
A2 = SORT(data, -1)          # descending
A3 = SORTBY(data, keys)      # sort by another column

Native relational query syntax

A1 =
  SELECT status, SUM(amount) AS total, COUNT(DISTINCT customer) AS customers
  FROM data
  WHERE status IN ("active", "pending") AND customer ILIKE "acme%"
        AND amount BETWEEN 10 AND 100
  GROUP BY status
  HAVING total > 100
  ORDER BY total DESC
  LIMIT 10

This syntax lowers to structured relational IR rather than a SQL string. Header arrays and Tuple/Vector/Deque collections of Record rows execute natively. Connector-produced Frame sources use the same clauses and can feed another native query. Warehouse-backed plans push the supported clauses to PostgreSQL, BigQuery, Snowflake, Redshift, and Databricks; DataFusion executes supported residual clauses. Boolean predicates use native provider parameters rather than interpolated SQL literals. Computed projection arithmetic is represented as a typed scalar tree; divide or remainder by zero yields blank/NULL. Portable computed projections include COALESCE(primary, fallback, 0), NULLIF(quantity, 0), searched CASE, simple CASE status WHEN "paid" THEN "open" ELSE "closed" END, ABS(delta), LOWER(label), SQRT(area), FLOOR(value), CEIL(value), ROUND(value), POWER(base, exponent), UPPER(label), and LENGTH(label). CEILING is an alias for CEIL; a negative SQRT produces blank/NULL. ROUND rounds halves away from zero. POWER produces blank/NULL for a negative base with a fractional exponent or zero with a negative exponent. Native SQL patterns use % for zero or more characters and _ for one; ILIKE ignores case. Blank inputs follow SQL three-valued logic and therefore do not pass negated IN, BETWEEN, or pattern predicates. Grouped and global COUNT, SUM, AVG, MIN, and MAX follow SQL blank/null aggregation semantics. Aggregate outputs require AS aliases. Use either LIMIT 25 or FETCH FIRST 25 ROWS ONLY; FETCH NEXT 25 ROWS ONLY is also accepted and canonicalizes to the same bounded result stage. Skip rows with either LIMIT 25 OFFSET 100 or the standard OFFSET 100 ROWS FETCH NEXT 25 ROWS ONLY; OFFSET 100 ROWS is valid without a limit. Result windows run after ordering and before final projection in resident collections, native Frames, Polars, DataFusion, and warehouse pushdown. Offset-only warehouse queries remain a residual operation when the provider has no portable unlimited-result spelling. Ordering defaults to nulls last in both directions. Use ORDER BY score DESC NULLS FIRST or ORDER BY score ASC NULLS LAST to make placement explicit; the same modifier is accepted inside analytic-window ORDER BY clauses. Outer ordering accepts source scalar expressions and SELECT aliases, including computed aliases: SELECT amount * 2 + fee AS total FROM Orders ORDER BY total DESC. Analytic windows accept the same expressions, for example ROW_NUMBER() OVER (ORDER BY amount / divisor DESC NULLS LAST) and SUM(fee) OVER (ORDER BY amount * 2 RANGE BETWEEN 4 PRECEDING AND CURRENT ROW). Resident runtimes bind each expression once and compute one key per source row; ranking, peer detection, and RANGE bounds reuse those cached values.

Join resident or Frame relations

A3 =
  SELECT o.id AS order_id, c.name AS customer
  FROM A1 AS o
  LEFT JOIN A2 AS c ON o.customer_id = c.id
  ORDER BY o.id

Joined sources require distinct AS aliases. INNER, LEFT, RIGHT, and FULL accept typed ON predicates; pure equality conjunctions use the multi-key hash path with up to 16 key pairs. CROSS JOIN omits ON. Qualified names remain available to WHERE, grouping, HAVING, ordering, and projection.

Compose queries with derived tables and CTEs

LargeOrders =
  WITH active AS (
    SELECT id, customer_id, amount FROM Orders WHERE status = "active"
  )
  SELECT q.id, q.amount
  FROM (SELECT id, amount FROM active WHERE amount >= 100) AS q
  ORDER BY q.amount DESC

Ordinary CTEs are lexical relation values. Parenthesized queries can be used directly in FROM; an optional single-source alias scopes qualified column references without renaming the resulting columns. A resident recursive CTE uses SQL working-table semantics:

Sequence =
  WITH RECURSIVE nums AS (
    SELECT n FROM Seed
    UNION ALL
    SELECT n + 1 AS n FROM nums WHERE n < 100
  )
  SELECT n FROM nums ORDER BY n

Use UNION when only novel rows should enter the next frontier. UNION ALL preserves duplicates. Authored SQL has no fixed iteration ceiling: execution runs to a fixpoint and fails, without returning a partial result, if its row or resident-work budget is exhausted. Add (column, ...) after a binding name to rename its seed schema. Comma-separated recursive bindings advance simultaneously, so mutually recursive terms observe only the previous iteration's frontiers. When the sources are Frames, the complete grouped seed/step/result plan stays resident, including column lists, multiple or mutually recursive bindings, set expressions, and joins from a recursive frontier to another resident Frame.

Aggregate arguments can use the portable scalar-expression subset:

SELECT customer_id,
       SUM(price * quantity) AS revenue,
       AVG(ABS(delta)) AS mean_delta,
       COUNT(DISTINCT LOWER(email)) AS users
FROM Orders
GROUP BY customer_id

Use NULLIF to turn a sentinel or zero denominator into SQL null without an engine-specific conditional:

SELECT id, amount / NULLIF(quantity, 0) AS unit_price
FROM Orders

The same expression remains native over collections, Frames, Polars, DataFusion, and supported warehouse pushdown.

Use CAST for a required conversion and TRY_CAST when bad source data should become null instead of failing the expression:

SELECT id,
       CAST(amount AS STRING) AS amount_text,
       TRY_CAST(imported_amount AS NUMBER) AS imported_amount
FROM Orders

The portable targets are NUMBER, STRING, and BOOLEAN; common SQL aliases such as DOUBLE, TEXT, and BOOL canonicalize to those roots. Null remains null. Numeric casts accept finite numbers, booleans, and trimmed numeric text; boolean casts accept booleans, finite numbers, and case-insensitive true or false text.

Use searched CASE when the output depends on ordered predicates. The first TRUE arm wins; blank/unknown conditions fall through, and omitting ELSE returns blank:

SELECT id,
       CASE
         WHEN amount >= 1000 THEN "large"
         WHEN amount >= 100 THEN "medium"
         ELSE "small"
       END AS size
FROM Orders

Use DISTINCT when the projected row shape, rather than one aggregate argument, defines uniqueness:

SELECT DISTINCT customer_id, status
FROM Orders
ORDER BY customer_id, status
LIMIT 1000

Deduplication happens before the limit.

Combine complete query results without converting them to arrays or writing an ETL lambda:

SELECT customer_id FROM ActiveCustomers
UNION ALL
SELECT customer_id FROM ImportedCustomers
EXCEPT
SELECT customer_id FROM SuppressedCustomers
ORDER BY customer_id
LIMIT 1000

Use ALL when duplicate multiplicity is meaningful. Without it, each typed row appears once and repeated blank/NULL rows collapse. Set arms match columns by position and the result keeps the left arm's names. INTERSECT is evaluated before UNION or EXCEPT; use a derived query when explicit grouping is needed.

Correlated aggregates can enrich each outer row without writing a lambda or performing a nested scan:

SELECT c.id,
       (SELECT SUM(o.price * o.quantity) AS total
        FROM Orders AS o
        WHERE o.customer_id = c.id AND o.status = "paid") AS revenue,
       (SELECT COUNT(*) AS total
        FROM Orders AS o
        WHERE o.customer_id = c.id AND o.status = "paid") AS paid_orders
FROM Customers AS c
ORDER BY c.id

Grid groups each inner query by the correlation key and performs one typed hash lookup per customer. Customers with no matching orders receive blank revenue and a count of zero.

Equality-correlated membership queries are decorrelated into native hash semi/anti joins:

SELECT c.id, c.name
FROM Customers AS c
WHERE EXISTS (
  SELECT o.id FROM Orders AS o
  WHERE o.tenant = c.tenant AND o.customer_id = c.id AND o.active = TRUE
)

Uncorrelated one-column subqueries can also feed comparisons and membership:

AboveAverage =
  SELECT id FROM Orders
  WHERE amount > (SELECT AVG(amount) AS average FROM Orders)
 
ActiveCustomers =
  SELECT id FROM Customers
  WHERE id IN (SELECT customer_id FROM Orders WHERE status = "active")

Rank and compare rows with analytic windows

Ranked =
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS row_num,
         LAG(amount, 1, 0) OVER (PARTITION BY region ORDER BY id) AS prior,
         SUM(amount) OVER (
           PARTITION BY region ORDER BY id
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
         ) AS seven_row_total
  FROM Orders
  ORDER BY id

RANK and DENSE_RANK preserve peer groups. Window aggregates without ORDER BY cover the whole partition; ordered window aggregates use the peer-aware default frame unless an explicit ROWS BETWEEN ... AND ... frame is present. RANGE BETWEEN ... AND ... is also available; offset ranges use one numeric order key, while RANGE BETWEEN CURRENT ROW AND CURRENT ROW selects the complete peer group. LAG and LEAD accept an optional scalar default after their offset. Distribution functions NTILE, PERCENT_RANK, and CUME_DIST are available, as are the peer-aware value functions FIRST_VALUE, LAST_VALUE, and one-based NTH_VALUE. Value functions may also use an explicit ROWS or RANGE frame. In grouped queries, windows run after HAVING and may reference aggregate aliases, for example RANK() OVER (ORDER BY total DESC) beside SUM(amount) AS total. Window outputs always require AS.

Pipeline filter + sort + limit

A1 = data
  >> FILTER(value => value > 0)
  >> SORT(_, -1)
  >> TAKE(10)

Here value is one element of data; _ in SORT(_, -1) is the whole result of the preceding step.

Lookups And Joins

VLOOKUP / HLOOKUP / XLOOKUP

A1 = VLOOKUP("Alice", users, 2, FALSE)
A2 = HLOOKUP("Q1", quarters, 2, FALSE)
A3 = XLOOKUP("Alice", users[Name], users[Email], "n/a")

INDEX / MATCH

A1 = INDEX(prices, MATCH("EUR", currencies, 0))

Two-key lookup

A1 = INDEX(values, MATCH(1, (rows = "Alice") * (cols = "Q1"), 0))

Lookup with explicit fallback

A1 = IFNA(VLOOKUP(name, users, 2, FALSE), "n/a")
A2 = VLOOKUP(name, users, 2, FALSE) DEFAULT "n/a"

Conditional Logic

Single branch

A1 = score > 0.5 THEN "pass" ELSE "fail"

Multiple tiers (chained, single line)

A1 = score >= 0.9 THEN "A" ELSE score >= 0.7 THEN "B" ELSE score >= 0.5 THEN "C" ELSE "F"

For long chains, use CASE WHEN (below) which spans lines.

Match a known set

A1 = MATCH(status, "draft" -> :gray, "pending" -> :amber, "approved" -> :green, _ -> :red)

For longer matches, wrap the arms across lines:

A1 = MATCH(status,
  "draft"    -> :gray,
  "pending"  -> :amber,
  "approved" -> :green,
  _          -> :red
)

CASE WHEN (different conditions)

A1 = CASE
  WHEN score >= 90 THEN "A"
  WHEN score >= 80 THEN "B"
  WHEN score >= 70 THEN "C"
  ELSE "F"
END

Guard with IF

A1 = IF(divisor = 0, 0, numerator / divisor)
# or
A1 = TRY numerator / divisor ELSE 0

Text And Strings

Concatenate

A1 = "Hello, " & name & "!"
A2 = CONCAT("a", "b", "c")
A3 = TEXTJOIN(", ", TRUE, items)

Format with placeholders

A1 = `total={B1:$#,##0.00} pct={B2:0.0%}`
A2 = TEXT(amount, "$#,##0.00")

Substring / split

A1 = LEFT(name, 3)
A2 = RIGHT(name, 3)
A3 = MID(name, 2, 4)
A4 = SPLIT(csv_row, ",")

Regex

A1 = REGEXMATCH("INV-2026-001", raw"^INV-\d+-\d+$")
A2 = REGEXEXTRACT("INV-2026-001", /INV-(\d+)/)
A3 = REGEXREPLACE("owner:ops", /owner:\w+/, "owner:shared")

Wildcard match

A1 = WILDCARDMATCH(filename, glob"INV-*.csv")

Trim and clean

A1 = TRIM(name)
A2 = LOWER(email)
A3 = PROPER("hello world")

Dates And Times

Today / now

A1 = TODAY()
A2 = NOW()

Construct a date

A1 is date = DATE(2026, 4, 10)
A2 is datetime = DATETIME(2026, 4, 10, 15, 30, 0)
A3 is date = d"2026-04-10"
A4 is datetime = dt"2026-04-10T15:30:00Z"

Date arithmetic

A1 = TODAY() + 7              # 7 days from now
A2 = TODAY() + 1mo            # 1 month from now
A3 = EOMONTH(TODAY(), 0)      # end of current month
A4 = WORKDAY(TODAY(), 5)      # 5 business days from today

Date difference

A1 = DAYS(end, start)
A2 = NETWORKDAYS(start, end)
A3 = YEARFRAC(start, end)

Format a date

A1 = TEXT(TODAY(), "yyyy-mm-dd")
A2 = `as of {TODAY():yyyy-mm-dd}`

Arrays And Spilling

Array literal

A1 = [10, 20, 30]              # row, spills A1:C1
A1 = [10; 20; 30]              # column, spills A1:A3
A1 = [1, 2; 3, 4]              # 2x2 matrix

Reference a spill

B1 = SUM(A1#)                  # sum the entire spilled rectangle
B2 = INDEX(A1#, 2, 3)          # specific cell within

Sequence

A1 = SEQUENCE(10)              # 1..10
A2 = SEQUENCE(10, 1, 0, 0.1)   # 0.0, 0.1, 0.2, ..., 0.9
A3 = 1..10                     # range shorthand
A4 = 1..2..10                  # 1, 3, 5, 7, 9

Constant fills

A1 = ZEROS(1000, 1)           # 1000-row column of 0
A2 = ONES(3, 4)               # 3x4 array of 1
A3 = FILL("TBD", 100, 1)      # 100-row column of "TBD"
A4 = REPEAT(0, 1000)          # column shorthand: 1000x1 of 0
A5 = REPEAT([1, 2, 3], 4)     # tile a row: 4x3 of [1,2,3]

FILL, ZEROS, ONES, and REPEAT produce a single array-valued binding — one dependency-graph node — so they're the right call when every array element would hold the same value. Use MAKEARRAY only when each output element genuinely depends on its row/column index.

Reshape

A1 = MAKEARRAY(3, 4, LAMBDA(r, c, r * c))
A2 = TOROW(A1)                 # flatten to row
A3 = TOCOL(A1)                 # flatten to column
A4 = WRAPROWS(A1, 2)           # rewrap into chunks of 2

Combine

A1 = HSTACK(B1#, C1#)          # side by side
A2 = VSTACK(B1#, C1#)          # one below the other

Higher-Order Helpers

Map

A1 = MAP([1, 2, 3], x => x * 2)
A2 = MAP(prices, qtys, (p, q) => p * q)
A3 = SIN.([0, 1.57, 3.14])     # broadcast shorthand

Reduce

A1 = REDUCE(0, values, (acc, x) => acc + x)
A2 = +/ values                 # reduction operator shorthand

Scan (cumulative)

A1 = SCAN(0, values, (acc, x) => acc + x)

Make array

A1 = MAKEARRAY(3, 5, LAMBDA(r, c, r * c))

For most index-dependent fills, an array comprehension is more readable and lowers to the same single-binding MAKEARRAY under the hood:

A1 = [r * c FOR r IN 1..3, c IN 1..5]   # equivalent to MAKEARRAY above
A2 = [i * i FOR i IN 1..10]             # 1D: 1, 4, 9, ..., 100
A3 = [r * c FOR r IN 1..5, c IN 1..5 IF r <= c]  # upper-triangular, with BLANK below the diagonal

Reach for MAKEARRAY directly when the body needs three or more index variables, when you want to share a heavy intermediate via LET, or when the dimensions are computed dynamically.

Per-row / per-column

A1 = BYROW(matrix, row => SUM(row))
A2 = BYCOL(matrix, col => AVERAGE(col))

Geometry And Meshes

Polygon rings with holes

Use a plain N x 2 array for a simple polygon. Use an array of rings for a polygon with holes: first the exterior ring, then one row per interior ring.

outer = [[0, 0], [10, 0], [10, 10], [0, 10]]
holes = [[[2, 2], [8, 2], [8, 8], [2, 8]]]
 
inside_solid = GEOM_POINT_IN_POLYGON([1, 1], outer, holes)  # 1
inside_hole  = GEOM_POINT_IN_POLYGON([5, 5], outer, holes)  # 0

WKT / GeoJSON round trip

GEOM_FROM_WKT and GEOM_FROM_GEOJSON preserve interior rings. The same nested ring array can be serialized back to either format.

shape = GEOM_FROM_GEOJSON(
  "{\"type\":\"Polygon\",\"coordinates\":[[[0,0],[10,0],[10,10],[0,10],[0,0]],[[2,2],[8,2],[8,8],[2,8],[2,2]]]}"
)
 
wkt     = GEOM_TO_WKT(shape)
geojson = GEOM_TO_GEOJSON(shape)

Fast triangulation with Earcut

Use GEOM_POLYGON_TRIANGULATE when you need a fast triangle list and GEOM_POLYGON_TRIANGULATE_MESH when the consumer wants indexed mesh buffers. GEOM_POLYGON_TRIANGULATE_DEVIATION returns Earcut's relative area error; 0 means the triangulation area exactly matches the input polygon area.

triangles = GEOM_POLYGON_TRIANGULATE(outer, holes)
mesh      = GEOM_POLYGON_TRIANGULATE_MESH(outer, holes)
quality   = GEOM_POLYGON_TRIANGULATE_DEVIATION(outer, holes)

Robust and refined meshes

The triangulation functions are useful for robust, Delaunay, Steiner-point, and tessellated meshes. The _MESH variants return [points, indices]; the non-mesh variants return copied triangle vertex arrays.

robust_mesh    = GEOM_POLYGON_TRIANGULATE_ROBUST_MESH(outer, holes)
delaunay_mesh  = GEOM_POLYGON_TRIANGULATE_DELAUNAY_MESH(outer, holes)
steiner_mesh   = GEOM_POLYGON_TRIANGULATE_STEINER_MESH(outer, [[5, 5]], holes)
tessellated    = GEOM_POLYGON_TESSELLATE_MESH(outer, holes, 0.25)
centroid_cells = GEOM_POLYGON_CENTROID_NET(outer, holes, 0)
convex_parts   = GEOM_POLYGON_CONVEX_DECOMPOSE(outer, holes)

External Data

Single FX rate

A1 is fx_rate = FX_RATE("EUR", "USD")
A2 is currency = ROUND(amount * (A1 DEFAULT 1.08), 2)

HTTP JSON with defensive chain

A1 ~= HTTP_JSON("https://api.example.com/users/" & TEXT(user_id, ""))
 
A2 = WITH user = A1, email = user.email
THEN email ELSE "unavailable"

ML score

A1 ~= ML_SCORE([0.12, 0.18, 0.27, 0.43])
A2 = A1 DEFAULT 0
A3 = A2 > 0.7 THEN "manual-review" ELSE "auto-approve"

Retry with last cached value

B1 = FX_RATE("EUR", "USD")
# Use ?= so a transient failure doesn't blank the previous good value
last_good_rate ?= B1
display_rate = last_good_rate DEFAULT 1.08

Per-cell external fanout (listable)

When a function is registered with listable: true, the dot-broadcast syntax f.(args) fans the call out into one independent per-cell external invocation. Use it whenever a range argument represents a batch of independent jobs — per-row LLM scoring, per-cell ML inference, or other independent job-queue calls.

# 100 independent prompts, dispatched in parallel.
# Each output coordinate gets its own cache entry, retry budget, and failure state.
A1:A100 = ASK_NUMERIC.("Score the lead", $B$1:$B$100)

The result is an array; assigning it to a single cell spills the remaining cells below the anchor. Two important properties:

  1. Per-cell cache reuse. When two owners cover the same arguments with a shareable (background) cache policy, each unique argument tuple fires the worker exactly once across both owners.
  2. Failure isolation. A worker failure on one cell does not cancel its siblings — the failed cell's status is failed, the others are ready.

Operational guard rails:

  • Listable call sites must use absolute references ($B$1:$B$100). Relative ranges depend on the anchor and can't be statically fanned out.

  • Each function carries a fanout cap (default 1024) that fails the deploy loudly rather than silently enqueueing millions of jobs.

  • A specific call site can override the cap with _policy:

    A1 = ML_SCORE.(
  $B$1:$B$5000,
  { listable_max_fanout: 5000 } AS _policy
)

Functions currently registered as listable: ASK_NUMERIC, ASK_BOOLEAN, ASK_JSON, ASK_TEXT, ASK, AI_PROMPT. Compiled models opt in via compileGridFunction(source, { listable: true }).

If your function is not registered as listable, f.(...) is a compile-time error. Either remove the dot (the array is then sent as a single argument to the worker) or add listable: true to the function contract if per-cell semantics is genuinely what you want.

Error Handling

Cheapest fallback

A1 = source DEFAULT 0

Catch all errors

A1 = IFERROR(EXPR, fallback)

Catch only #N/A

A1 = IFNA(VLOOKUP(...), "n/a")

Inline guard

A1 = TRY 1 / divisor ELSE 0

Multi-step chain with single fallback

A1 = WITH data = HTTP_JSON(url), first = data.results[1], name = first.name
THEN name ELSE "unknown"

Assert input

A1 is percentage = input ASSERT input BETWEEN 0 AND 1
A2 is currency = price ASSERT price > 0 ELSE #VALUE!

Detect specific error type

status = IF(ISNA(value), "missing",
         IF(ISERR(value), "error",
         IF(ISBLANK(value), "empty", "ok")))

Rules And Schedules

Reactive alert

WHEN load > threshold THEN
  status = "alert"
  alerted_at = NOW()
END

Periodic counter

EVERY duration"PT15M" SKIP MISSED THEN
  ticks = ticks + 1
  last_tick = NOW()
END

Cron schedule

EVERY cron"0 9 * * 1-5" SKIP MISSED THEN
  daily_open = NOW()
END

One-shot at deadline

AT dt"2026-12-31T23:59:00Z" BACKFILL THEN
  year_end_close = TRUE
END

Combine reactive + scheduled

WHEN cash_runway < threshold THEN
  alert = "liquidity"
END
 
EVERY duration"PT1H" SKIP MISSED THEN
  cash_runway_log = cash_runway_log + 1
END

Type Tags

Currency conversion with tags

A1 is currency = 100000               # USD by default
A2 is fx_rate = FX_RATE("USD", "EUR")
A3 is currency = ROUND(A1 * (A2 DEFAULT 0.93), 2)

Percentage display

A1 is percentage = 0.21               # displays as 21%
A2 is percentage = 21pct              # equivalent

Basis points

A1 is bps = 25bps                     # displays as 25bp
A2 = principal * A1 / 10000         # convert to a fraction for math

Date pipeline

A1 is date = TODAY()
A2 is date = A1 + 7
A3 is duration = duration"P1D"

Divisibility, IS predicates, and collection checks

See infix-operators.md for the full reference.

R1C1 = candidate IS MULTIPLE OF batch_size
R1C2 = gcd_a COPRIME gcd_b
R1C3 = ids SUBSET OF allowed_ids
R1C4 = 7 IS PRIME
R1C5 = payload IS EMPTY
R1C6 = values >> SORT(_) IF LEN(values) < 1000
R1C7 = [1, 2] IN [[1, 2], [3, 4]]       # TRUE: direct row membership
R1C8 = [1, 2, 3, 4] CONTAINS [2, 3]      # TRUE: contiguous order

IN/HAS, SUBSET/SUPERSET, and OVERLAPS compare whole direct elements; they do not flatten nested collections. Set relations ignore order and duplicates, while CONTAINS requires one contiguous run in text or an ordered collection.

Performance Patterns

Cache an expensive call at a named binding

# Compute once, reuse many times
fx_eur_usd = FX_RATE("EUR", "USD") DEFAULT 1.08
 
A1 is currency = amount_a * fx_eur_usd
A2 is currency = amount_b * fx_eur_usd
A3 is currency = amount_c * fx_eur_usd

Use ~= for bindings that are rarely read

A1 ~= LARGE_MATRIX_INVERT(huge_matrix)

Avoid range broadcasts that explode dependencies

A1:A1000 = 0 parses, but Grid expands it into one thousand individual A1 = 0, A2 = 0, ..., A1000 = 0 coordinate bindings — each with its own dependency-graph node. For constant fills, prefer a single array-valued binding that spills:

# Bad: 1000 individual cells
A1:A1000 = 0
 
# Good: one array-valued binding, one graph node
A1 = ZEROS(1000, 1)        # column of zeros
A1 = ONES(1000, 1)         # column of ones
A1 = FILL("TBD", 1000, 1)  # column of any constant
A1 = REPEAT(0, 1000)       # equivalent shorthand for column of zeros

When each output element depends on its row/column index, prefer an array comprehension — it desugars to a single MAKEARRAY binding and reads more naturally:

A1 = [r * c FOR r IN 1..1000, c IN 1..10]

Use MAKEARRAY directly when you need three or more index variables, or when the dimensions are computed dynamically. For constant fills the helpers above (ZEROS/ONES/FILL/REPEAT) are clearer and avoid the lambda machinery.

Hoist common subexpressions with DO

A1 = DO
  base = SUM(B1:B100)
  base * 1.1 + base * 0.05
END

Use spill references instead of repeating ranges

# Bad
A1 = SUM(B1:B100)
A2 = AVERAGE(B1:B100)
A3 = MAX(B1:B100)
 
# Better
data = B1:B100
A1 = SUM(data)
A2 = AVERAGE(data)
A3 = MAX(data)

Analysis And Optimization

Grid's analytical functions are grid_extension built-ins — they run in the formula evaluator like any other function. The family map is in reference.md.

Goal-seek a cell with SOLVE

SOLVE finds the input that drives a cell to a target, without mutating the variable cell.

# Find the price (A2, between 0 and 1000) that makes revenue (A3) hit 200,000.
SOLVE Price_Solution = A2 IN [0, 1000] GOAL A3 = 200000

Price_Solution receives the solved price; A2 is untouched. Solves are reactive — change an upstream input and it re-solves.

Minimize or maximize an objective cell

SOLVE Best_Qty = Q IN [0, 5000] MAXIMIZE Profit

For a scalar objective expressed as a lambda rather than a cell, use the function forms:

A1 = MINIMIZE(x => (x - 3)^2 + 1, -10, 10)      # local, bounded
A2 = MINIMIZE.GLOBAL(x => x^2 + 10 * SIN(x), -10, 10)   # escapes local minima

Linear and mixed-integer programs

Maximize c·x subject to A x ≤ b, x ≥ 0.

c = {3, 2}                    # objective coefficients
A = {{1, 1}, {1, 0}}          # constraint matrix
b = {4, 2}                    # right-hand side
 
# Continuous LP
plan = LINEAR_PROGRAM(c, A, b, TRUE)         # TRUE = maximize → optimal x vector
 
# Same model, both variables constrained to integers
mask = {1, 1}                                # 1 = integer, 0 = continuous
whole = MIXED_INTEGER_PROGRAM(c, A, b, mask, TRUE)

MIXED_INTEGER_PROGRAM needs a solver-enabled build; elsewhere it returns an explicit capability error.

Shadow prices and infeasibility

LINEAR_PROGRAM_SENSITIVITY shares the LP solve and reports duals (shadow prices), reduced costs, binding rows, or an irreducible infeasible subsystem — select with the final component argument.

duals = LINEAR_PROGRAM_SENSITIVITY(c, A, b, TRUE, "duals")   # marginal value of each constraint
iis   = LINEAR_PROGRAM_SENSITIVITY(c, A, b, TRUE, "iis")     # which constraints conflict, if infeasible

Integrate a differential equation

ODE_SOLVE(fn, y0, t_start, t_end, samples) integrates dy/dt = fn(t, y) and returns a samples × (1 + n) trajectory with time in the first column. The method is adaptive by default and switches to a stiff integrator automatically.

# Exponential decay dy/dt = -0.5 y, y(0) = 1, sampled 101 times over [0, 10].
traj = ODE_SOLVE(LAMBDA(t, y, -0.5 * y), 1, 0, 10, 101)
 
# Just the final state (y0 may be an array for a system of equations).
y_end = ODE_FINAL(LAMBDA(t, y, -0.5 * y), 1, 0, 10)

Solve a nonlinear system

NSOLVE finds F(x) = 0. The lambda declares the unknowns as its parameters and returns one component per unknown; x0 supplies the starting guess.

# Intersection of a circle and a line: x² + y² = 25, x − y = 1.
sol = NSOLVE(LAMBDA(x, y, {x^2 + y^2 - 25, x - y - 1}), {3, 3})   # → 1×2 row [x, y]

Fit a curve to data

CURVE_FIT fits a model by Levenberg-Marquardt. The lambda's first parameter is the observation variable; the rest are the coefficients to fit.

# Fit a·exp(−b·t) + c to (times, values), starting from {1, 1, 1}.
coeffs = CURVE_FIT(LAMBDA(t, a, b, c, a * EXP(-b * t) + c), times, values, {1, 1, 1})

Eigenvalues, SVD, and conditioning

variance = EIGEN.VALUES(cov)          # principal-component variances, descending
loadings = EIGEN.VECTORS(cov)         # matching eigenvectors as columns
 
rank = SVD.RANK(X)                     # numerical rank of a data matrix
sv   = SVD.VALUES(X)
cond = MAX(sv) / MIN(sv)              # 2-norm condition number

Interpolate a curve

# Monotone (shape-preserving) interpolation — the yield-curve workhorse.
rate = INTERPOLATE.PCHIP(2.5, tenors, rates)
# Smooth natural cubic spline.
y = INTERPOLATE.CUBIC(2.5, xs, ys)

Estimate a power spectrum

# One-sided PSD of a signal sampled at 100 Hz, Hann-windowed.
psd = SIGNAL.PSD(signal, 100, "hann")   # (freq, density) rows

Grammars, Trees, And Proofs

These recipes share one small grammar. The full surface — formalisms, generators, bounds, and every tree declaration — is in grammars.md.

Parse structured text into a tree

GRAMMAR calc IS CFG
  SKIP /\s+/
  START expression
 
  expression =
      "plus" "(" left:expression "," right:expression ")" BUILDS add(left, right)
    | "zero" BUILDS zero()
END
 
parser = calc USING EARLEY
parsed = PARSE("plus(zero, zero)", parser)
ok = PARSE_RESULT_OK(parsed)          # TRUE — rejection is a value, not an error
tree = PARSE_RESULT_TREE(parsed)

BUILDS names the semantic constructor each alternative produces — the constructor name (add) is independent of the concrete spelling (plus). Those constructors define the sorts that every tree declaration below binds with ON calc.expression.

Find every place a pattern matches

PATTERN with_zero(x:expression) ON calc.expression: plus(x, zero)
 
matches = TREE.MATCH(tree, with_zero, :subtrees, 8, 10000)

Patterns are written in the grammar's own concrete notation. x is a metavariable declared with its sort; the two trailing arguments are the match and work bounds.

Simplify a tree with equational laws

THEORY calc_laws ON calc.expression
  right_zero(x:expression): plus(x, zero) == x
  commutes(x:expression, y:expression): plus(x, y) == plus(y, x)
END
 
TRANSFORM simplify ON calc.expression
  USING calc_laws
  ORIENT BY canonical_tree
  BOTTOM UP
  REPEAT
  LIMIT 10000
END
 
result = simplify(tree)
simplified = result.tree

USING supplies undirected theory laws oriented by the declared policy; a transform body may also add its own name(vars): lhs => rhs directed rules.

Prove two expressions are equivalent

SOLVER equivalence ON calc.expression
  USING calc_laws
  GOAL EQUAL
  SEARCH BREADTH FIRST
  DEPTH 2
  STATES 32
  SOLUTIONS 1
  LIMIT 100000
END
 
lhs = PARSE_RESULT_TREE(PARSE("plus(zero, zero)", parser))
rhs = PARSE_RESULT_TREE(PARSE("zero", parser))
proof = equivalence(lhs, rhs)
 
verdict = proof.status      # :solved — right_zero proves the equality
finished = proof.complete   # FALSE only when a bound cut the search short

An EQUAL solver is called with two trees; MATCH and NORMAL solvers take one. TREE.SOLVE(solver, ...) does the same dynamically. Only equality authority — theory laws and equality-preserving transforms — can appear in an equality proof.

Prove once, reuse everywhere

THEOREM plus_zero(x) ON calc.expression USING equivalence:
  plus(x, zero) == x
 
THEORY proved_laws ON calc.expression
  INCLUDES plus_zero
END

A THEOREM is admitted only when its named solver produces a complete replayable proof at compile time; otherwise the declaration fails with GRID_THEOREM_UNPROVED. A proved theorem is then accepted anywhere a declaration takes an equation source — THEORY ... INCLUDES, an ALGEBRA role clause, TRANSFORM ... USING, or SOLVER ... USING — and carries :proved authority in derivations, distinct from the :assumed authority of ordinary theory laws.

Symbolic Mathematics

Symbols compose through ordinary Grid operators — no held-body syntax or string evaluation. Every SYMBOLIC.* operation returns a record carrying the result expression beside its status and replayable certificate. The complete surface is in symbolic-mathematics.md.

Declare symbols and compose expressions

symbol x
input A1 = 2
 
polynomial = x^2 + 2*x + 1
reactive = A1*x^2 + 1                 # coefficients stay reactive

Simplify, expand, factor, differentiate

canonical_result = SYMBOLIC.CANONICALIZE(polynomial)
canonical = canonical_result.expression
expanded_result = SYMBOLIC.EXPAND(polynomial)
factored_result = SYMBOLIC.FACTOR(polynomial)
derivative_result = SYMBOLIC.D(polynomial, x)
derivative = derivative_result.expression

Solve exactly

roots_result = SYMBOLIC.SOLVE(polynomial == 0, x, :complex)
roots_status = roots_result.status
roots_complete = roots_result.complete

Reason under assumptions

real_positive = SYMBOLIC.ASSUMPTIONS(
  SYMBOLIC.DOMAIN(x, :real),
  x > 0
)
cancelled_result = SYMBOLIC.CANCEL(x / x, real_positive)
decision_result = SYMBOLIC.DECIDE(x > 0, real_positive)
decision_status = decision_result.status

Decisions are four-state; an unprovable proposition reports its status instead of guessing.

Evaluate exactly, approximate explicitly

at_three = SYMBOLIC.SUBSTITUTION(x, 3)
value_result = SYMBOLIC.EVALUATE(polynomial, at_three)
value = value_result.expression
 
one_third = SYMBOLIC.RATIONAL(1, 3)
approximation_result = SYMBOLIC.APPROXIMATE(one_third, 64, :nearest_even)
approximation = approximation_result.expression

Exact rationals never silently become floats; SYMBOLIC.APPROXIMATE is the explicit, precision-and-rounding-declared boundary.

Political And Social Science

Grid ships definitions-only political and social-science modules. Their functions inline at compile time:

USE "shared/politics.gs" AS pol
USE "shared/socsci.gs" AS soc
 
poll = soc.weighted_mean([0.48, 0.51, 0.49], [800, 1200, 900])
enp = pol.effective_number([0.50, 0.30, 0.20])

Apportionment

APPORTION(votes, seats, method) returns an integer seat vector whose sum is exactly seats. Supported methods are "d'hondt" ("jefferson"), "sainte-lague" ("webster"), "hare", "droop", and "huntington-hill" ("equal-proportions"). Exact ties go to the earlier input, making recounts and audits deterministic.

votes = [100000, 60000, 20000]
seats = APPORTION(votes, 6, "d'hondt")   # [4, 2, 0]

The pure quotient construction and native result are shown together in examples/canonical/14-dhondt-apportionment.grid.

Ballot Tallies

Ranked-ballot functions take one ballot per row, expressed as ordered, 1-based candidate ids. PLURALITY_TALLY counts first preferences, BORDA_TALLY scores every expressed preference, and IRV_TALLY returns [round, candidate, total, status] rows so every elimination is inspectable. Exact IRV elimination ties remove the later candidate, preserving the same first-input-wins rule as apportionment.

ballots = [1, 2, 3; 1, 3, 2; 2, 3, 1; 3, 2, 1]
trace = IRV_TALLY(ballots, 3)
pairwise = CONDORCET_MATRIX(ballots, 3)

Approval voting uses a separate rectangular boolean or 0/1 matrix because approval ballots are not rankings:

approvals = [TRUE, TRUE, FALSE; FALSE, TRUE, TRUE]
totals = APPROVAL_TALLY(approvals)

CONDORCET_MATRIX returns pairwise counts rather than inventing a winner when the majority relation cycles. Multi-seat STV is intentionally not aliased to IRV; quota, surplus-transfer, exhausted-ballot, and tie rules need an explicit contract before it is exposed.

District Metrics

EFFICIENCY_GAP(a_votes, b_votes) uses the sign convention (wasted A - wasted B) / total votes. MEAN_MEDIAN(shares) returns mean minus median district share. PARTISAN_BIAS(shares) uniformly swings the statewide mean to 50% and returns seat share minus 50%.

Inequality And Poverty

GINI, LORENZ, THEIL, ATKINSON, and FGT accept finite non-negative observations. Negative values are rejected; they are never silently removed. LORENZ returns [population_share, value_share] rows including [0,0]. FGT(values, poverty_line, alpha) is the headcount ratio at alpha = 0, with alpha = 1 giving the poverty-gap index.

Binomial GLMs

LOGIT.FIT(y, x, intercept?, max_iterations?, tolerance?) and PROBIT.FIT fit binomial models with iteratively reweighted least squares. The result is a glm_fit object containing coefficients, standardErrors, covariance, fitted, deviance, iterations, and converged. Invalid binary responses, singular designs, perfect separation, and non-convergence produce explicit errors rather than partial coefficients.

Survey Weights

RAKE(weights, categories, targets, tolerance?, max_iterations?) returns calibrated weights. Category rows align with observations. Each target row is [dimension, category, total], with dimensions numbered from 1:

weights = [1, 1, 1, 1]
categories = [0, 0; 0, 1; 1, 0; 1, 1]
targets = [1, 0, 60; 1, 1, 40; 2, 0, 30; 2, 1, 70]
raked = RAKE(weights, categories, targets)
estimate = SVYMEAN([10, 20, 30, 40], raked)

SVYMEAN and SVYTOTAL are explicitly weighted estimators. DEFF(weights) is specifically Kish's unequal-weighting design effect; it does not claim stratified or clustered Taylor variance estimation. MOE(se, confidence?) uses a two-sided normal critical value, and CRONBACH_ALPHA(items) expects a respondents-by-items matrix.

Coalition Power And Games

BANZHAF(weights, quota?) and SHAPLEY_SHUBIK(weights, quota?) return normalized power vectors for integer weighted voting games. The default quota is a strict majority. Both are exact dynamic programs, limited to 25 players and a bounded (players² × quota) work budget.

weights = [2, 1, 1]
banzhaf = BANZHAF(weights, 3)              # [0.6, 0.2, 0.2]
shapley = SHAPLEY_SHUBIK(weights, 3)

NASH_2X2(row_payoffs, column_payoffs) accepts two 2×2 payoff matrices and returns a nash_2x2 object. Its equilibria array contains every pure Nash equilibrium and the strictly interior mixed equilibrium when one exists.

Demography

LESLIE_PROJECT(population, fertility, survival, periods) returns a population trajectory with the initial state in row zero. Fertility has one value per age class; survival has one fewer value.

LIFE_TABLE(qx, radix?) returns [age, qx, lx, dx, Lx, Tx, ex] rows. It uses one-year intervals, defaults to a radix of 100,000, and requires the terminal qx to equal 1 so the table is complete.

Civic Dimensions And Network Influence

votes, seats, and persons are distinct units. Under strict dimensions, 100votes + 5seats is rejected while arithmetic within one civic dimension is valid.

GRAPH_EIGENVECTOR_CENTRALITY(graph, weight?, tolerance?, max_iterations?) returns normalized Perron eigenvector scores. Edge weights must be non-negative; convergence and work remain under the Graph region budgets.

The seeded end-to-end polling example is examples/canonical/15-election-forecast.grid.

See Also