‹ Back to the directory

Spreadsheet Formula Helper

Build and debug spreadsheet formulas that survive real data: lookups, conditional math, dates, and error handling.

by Lunefield·0 installs
spreadsheetsformulaslookups
K

Create a free shareskills account to install Spreadsheet Formula Helper into Claude.

Create a free account

Spreadsheet Formula Helper

Spreadsheet formulas are production code that nobody reviews, running on data that changes under them. This skill builds and debugs formulas that survive contact with real sheets — lookups that do not silently grab the wrong row, aggregations that keep working when rows are added, date math that respects how spreadsheets actually store time — and explains every formula so its owner can maintain it after you are gone.

When to use this skill

  • Translating "I need this column to show..." into a working formula
  • A formula returns errors, zeros, or — worst of all — plausible wrong numbers
  • A sheet has grown mega-formulas nobody dares touch, and needs a refactor
  • Choosing between a formula, a helper column, and a pivot for a given calculation

Instructions

  1. Restate the calculation in words first, edge cases included: "commission is 5% of sales up to 10,000, then 8% above that; blank sales means blank commission, not zero." If the words are ambiguous, the formula will be confidently wrong.
  2. Identify the shape of the problem: a per-row calculation, an aggregation over a range, a lookup into another table, or a reshaping. Mixing shapes inside one cell is the root of most unmaintainable formulas.
  3. Choose the boring, robust pattern for that shape:
    • Lookups: an index/match-style or modern lookup with an explicit not-found argument, over positional lookups that break when someone inserts a column; exact match unless you can say why approximate
    • Conditional aggregation: multi-criteria sum/count functions over array acrobatics when both work
    • Dates: arithmetic on real date values, never on formatted text; build dates from parts with the date constructor; remember a day equals 1
    • Text: trim and normalize before comparing — human-entered text contains invisible spaces
  4. Build inside-out. Put the innermost piece in a spare cell, verify it against a hand-computed answer, then wrap the next layer around it. Never author a five-function nest in one keystroke.
  5. Reach for helper columns early. Three named, visible steps beat one heroic formula: easier to verify, easier to debug, and the recalculation cost is almost always irrelevant. Hide the helper columns if aesthetics demand it.
  6. Make ranges survive growth: whole-column references, named ranges, or structured table references instead of B2:B57. Hardcoded end rows are the classic silently-wrong-next-month bug.
  7. Handle errors at the boundary, not everywhere. Wrap the outermost expression with an error-to-blank guard where a blank is honest — but never blanket-suppress errors while still debugging, because suppressed errors are how wrong numbers get promoted into reports.
  8. Verify against hand-checked rows: a typical row, a boundary row (exactly 10,000), and a degenerate row (blank, zero, duplicated key). Compute the expected answers by hand and compare.
  9. Document in-sheet: a note stating what the formula assumes — "sales are never negative; one row per rep per month."

Output format

Deliver formula work as a package:

Formula (for C2, fill down):
  =<the formula>

Plain language: <one clause per functional piece>
Assumes: <data-shape assumptions>
Breaks if: <the two most likely future changes that would break it>
Checked against: <the three hand-verified rows and their expected values>

Guardrails

  • A lookup without an explicit not-found path is a bug that has not happened yet
  • Never test floats for exact equality after arithmetic; round to the sheet's real precision first
  • Volatile now/today-style functions replicated down a thousand rows make sheets crawl; compute once in one cell and reference it
  • If the honest solution needs three helper columns and a small lookup table, say so — do not compress it into one cell to look clever
  • When the request is really a database query — joins across three tables, deduplication, history — recommend doing it upstream and handing the sheet the result

Worked example: tiered commission

Words: 5% up to 10,000; 8% on the amount above; blank stays blank. Shape: per-row calculation with a boundary. Formula: =IF(sales="", "", 0.05*MIN(sales,10000) + 0.08*MAX(sales-10000,0)) Hand checks: 8,000 → 400; 10,000 → 500; 15,000 → 900; blank → blank. The min/max form avoids a nested-IF ladder and extends to a third tier without surgery.

Spreadsheet Formula Helper — AI skill by Lunefield | shareskills