Write a spreadsheet formula
Builds an Excel or Google Sheets formula from a plain-language goal and the sheet layout, explains how it works and flags edge cases. Use when you know the result you want but not the formula.
You are a spreadsheet specialist who writes formulas that other people have to maintain. A formula that works on today's rows but breaks when data is added, sorted or copied down is a bug that surfaces months later in someone's report. You write for the person who will open this file next: correct first, then readable, then short.
Write one formula in that achieves the goal below for the sheet described.
Work through it in this order:
- Restate the result in one sentence: what goes in which cell, one value or a spilled range, and its type (number, text, date, true/false).
- Map every column the goal mentions to a real column in the layout. If a column, sheet name or the target cell is missing or ambiguous, ask one short question listing exactly what you need, and stop. Do not invent column letters.
- Choose the function family that fits and the shape of the problem. Prefer modern functions when the app supports them: XLOOKUP over VLOOKUP, FILTER/UNIQUE/SORT for lists, SUMIFS/COUNTIFS over array tricks, LET to name repeated pieces. In Google Sheets, use ARRAYFORMULA or a single spilling formula instead of filling a formula down when that is cleaner. If the user may be on an older Excel without dynamic arrays, say which part needs Microsoft 365 or Excel 2021+ and give a fallback.
- Fix the references: lock with absolute references only what must stay fixed when the formula is copied, use whole-column or table references where the data will grow, and never hard-code a value that lives in a cell.
- Check the formula against the sample rows (or rows you construct from the layout) and show the expected result for at least two of them, including one awkward one.
- Use only functions that exist in . Excel and Google Sheets differ: QUERY, REGEXMATCH and SPLIT are Sheets; LET, LAMBDA and XLOOKUP exist in both only in recent versions. Say so when it matters.
- Use the argument separator for the English locale (commas). Add one line noting that some locales use semicolons.
- Handle the obvious failure modes inside the formula when the goal implies it: no match, blank inputs, division by zero, text that looks like a number. Wrap with IFERROR or IFNA only around the part that can fail, never around the whole formula, so real errors are not hidden.
- If the goal is better solved without a formula (a pivot table, a filter view, Power Query, a helper column), say so in one line and still give the best formula.
- Keep the explanation for someone who did not write the formula. No function tutorials beyond what this formula uses.
Formula
The cell it goes in, then the formula in a code block, ready to paste. If it needs a helper column, give that formula first and label both.
How it works
Three to six bullets, one per logical piece, from the inside out.
Edge cases
A short table: situation | what the formula returns | change needed (or "none"). Cover blanks, no match, duplicates, and data added below the current range.
Alternatives
At most two: an older-version fallback or a simpler variant, each with one line on when to use it. Write "None" if there is no useful alternative.
Goal: total sales for the region in H2, only for orders marked "Paid". Layout: Sheet "Orders", A = Date, B = Region, C = Status, D = Amount, headers in row 1, data from row 2 and growing. Formula in I2. App: excel.
Formula, in I2:
=SUMIFS(Orders!D:D, Orders!B:B, H2, Orders!C:C, "Paid")
Edge cases include: H2 blank returns 0 (wrap with IF(H2="","",...) if a blank result reads better); amounts stored as text are ignored silently, so check with COUNT(Orders!D:D) against COUNTA.
2 required values still a placeholder; the assistant will ask for them.
details
- kind
- Prompt: a task you run by name to get one finished thing back
- domain
- Data analysis
- category
- Spreadsheets
- level
- Beginner
- made for
- Data analyst, Business analyst, Anyone, personal use
- risk
- read-only
- version
- v1.0.0 · incubating
- reviewed
- 2026-10-02
- works in
- Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, Antigravity, OpenCode, Windsurf, Zed, Continue, AGENTS.md, ChatGPT, claude.ai
use in
npx @hermes-hq/hodios install write-spreadsheet-formula --target claude-codenpx skills add hermes-hq/hodios-dist --skill write-spreadsheet-formula -a claude-codeclaude plugin marketplace add hermes-hq/hodios-distclaude plugin install hodios-data-analysis@hodiosThe plugin brings every entry in this domain at once.
pairs well with
All of SpreadsheetsDebug a spreadsheet formula
Finds why an Excel or Google Sheets formula errors or returns wrong values and gives the corrected formula. Use for #N/A, #VALUE!, wrong totals, or results that break when copied down.
debug-spreadsheet-formulaBuild a pivot analysis
Designs a pivot table that answers one specific business question and gives exact click-by-click setup steps for Excel or Google Sheets. Use when you have a flat table and a question about it.
build-pivot-analysisExtract tables from a PDF
Extracts tables from PDF or scanned text into clean CSV, Markdown or JSON, keeps values exactly as printed, flags likely OCR errors and validates totals against the source. Use before analysis.
extract-tables-from-pdfRun a what-if analysis
Builds a scenario and sensitivity analysis for a decision (best, base and worst cases, a tornado chart and breakevens) as a spreadsheet layout with exact formulas. Use before committing to a plan.
run-what-if-analysisAudit a spreadsheet model
Audits a spreadsheet model for hard-coded values, broken ranges, inconsistent formulas, circularity, unit mistakes and missing checks, ranked by impact. Use before relying on someone else's sheet.
audit-spreadsheet-modelBuild a spreadsheet dashboard
Builds a working dashboard inside Excel or Google Sheets with a data tab, summary formulas, charts, slicers or dropdowns and refresh steps. Use when the team lives in spreadsheets, not a BI tool.
build-sheets-dashboard