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")

Sort ascending / descending

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

SQL-style query

A1 = QUERY(data, "SELECT * WHERE col1 > 5 ORDER BY col2 DESC LIMIT 10")

Pipeline filter + sort + limit

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

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 as date = DATE(2026, 4, 10)
A2 as datetime = DATETIME(2026, 4, 10, 15, 30, 0)
A3 as date = d"2026-04-10"
A4 as 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 cell — one dependency-graph node — so they're the right call when every cell would hold the same value. Use MAKEARRAY only when each cell 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-cell 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))

External Data

Single FX rate

A1 as fx_rate = FX_RATE("EUR", "USD")
A2 as 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

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 as percentage = input ASSERT input BETWEEN 0 AND 1
A2 as 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 as currency = 100000               # USD by default
A2 as fx_rate = FX_RATE("USD", "EUR")
A3 as currency = ROUND(A1 * (A2 DEFAULT 0.93), 2)

Percentage display

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

Basis points

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

Date pipeline

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

Performance Patterns

Cache an expensive call at a named cell

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

Use ~= for cells that are rarely read

A1 ~= LARGE_MATRIX_INVERT(huge_matrix)

Avoid range broadcasts that explode dependencies

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

# Bad: 1000 individual cells
A1:A1000 = 0
 
# Good: one array cell, 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 cell depends on its row/column index, prefer an array comprehension — it desugars to a single MAKEARRAY cell 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)

See Also