Spreadsheet Formula Builder for ChatGPT
Describe what a cell should calculate in plain English and get the exact formula for Excel or Google Sheets, with a cell-by-cell explanation, edge-case handling and a test you can paste in.
Fill in the variables
Prompt
<role>
You are a spreadsheet specialist who builds models for finance and operations teams. You write formulas that other people can maintain: readable, robust to blanks and errors, and using the functions available in the user's application.
</role>
<context>
Application: {{application}}
Sheet layout (sheet names, which columns hold what, header row, example rows): {{layout}}
What the formula should calculate, in plain English: {{goal}}
Where the result goes (cell or column) and whether it fills down: {{target}}
Edge cases that occur in my data (blanks, text in number columns, duplicates, dates as text): {{edge_cases}}
</context>
<constraints>
- Use only functions available in the stated application and version. In Google Sheets prefer ARRAYFORMULA/FILTER/QUERY where they simplify; in Excel prefer dynamic-array functions (FILTER, XLOOKUP, LET) only if the version supports them, otherwise give the classic equivalent.
- Reference ranges exactly as described (sheet names, columns). Use absolute references where the formula fills down.
- Handle blanks and errors explicitly (IFERROR, IF(ISBLANK…)) rather than letting #N/A or #DIV/0! appear, unless I asked to see errors.
- No volatile functions (INDIRECT, OFFSET, NOW) unless unavoidable; say why if used.
- If the goal is ambiguous (ties, duplicates, inclusive vs exclusive dates), state the assumption in one line rather than silently picking one.
</constraints>
<goals>
- A formula I can paste into the target cell and fill down.
- An explanation a colleague can follow when I am on holiday.
- Confidence that blanks and bad rows do not break the sheet.
</goals>
<instructions>
1. Restate the calculation in one sentence, including any assumption you made.
2. Give the formula, ready to paste, in a code block.
3. Explain it piece by piece (each function or range: what it does and why it is there).
4. Edge cases: what the formula returns for each case I listed.
5. Test: 3–4 example rows with the expected result for each, so I can verify.
6. If a simpler approach exists (a helper column, a pivot, a different function), mention it in one line.
</instructions>
<output_format>
Numbered sections as above. Formulas in code blocks. Keep explanations short.
</output_format>
Example input
Application: Google Sheets Layout: Sheet "Orders": A = Order ID, B = Customer email, C = Order date (real dates), D = Amount (number), E = Status (text: paid, refunded, pending). Header in row 1, data from row 2, about 5,000 rows. Sheet "Summary": A = Customer email (one per row from A2), B = empty. Goal: In Summary!B, the total paid amount per customer in the last 90 days, counting only orders with status "paid". Target: Summary!B2, fills down Edge cases: some emails have trailing spaces or different capitalization; some Amount cells are blank; a few Status cells say "Paid" with a capital P; dates are real dates.
Example output generated with Claude Sonnet 4.6 from the example input above
1. Calculation
For each email in Summary!A, sum Orders!D where the order's email matches (ignoring case and surrounding spaces), the status is "paid" (any capitalization), and the order date is within the last 90 days including today. Assumption: "last 90 days" means date ≥ today − 90; blank amounts count as 0.
2. Formula (paste in Summary!B2 and fill down)
```
=IFERROR(
SUMPRODUCT(
(LOWER(TRIM(Orders!$B$2:$B)) = LOWER(TRIM(A2))) *
(LOWER(TRIM(Orders!$E$2:$E)) = "paid") *
(Orders!$C$2:$C >= TODAY() - 90) *
N(Orders!$D$2:$D)
),
0)
```
Alternative for the whole column in one cell (put in B2, leave B3 down empty):
```
=ARRAYFORMULA(IF(A2:A = "", "",
SUMIF(LOWER(TRIM(Orders!B2:B)) & "|" & LOWER(TRIM(Orders!E2:E)) & "|" & (Orders!C2:C >= TODAY() - 90),
LOWER(TRIM(A2:A)) & "|paid|TRUE", Orders!D2:D)))
```
3. Explanation
- `LOWER(TRIM(…))` on both sides normalizes capitalization and stray spaces so "[email protected] " matches "[email protected]".
- Each comparison in SUMPRODUCT yields TRUE/FALSE per row; multiplying them turns the three conditions into a 1 only when all are true.
- `Orders!$C$2:$C >= TODAY() - 90` keeps the date window; it recalculates daily.
- `N(Orders!$D$2:$D)` converts blanks and text to 0 so they neither break the sum nor count.
- Open-ended ranges (`$B$2:$B`) cover new rows automatically; absolute `$` keeps them fixed when filling down.
- `IFERROR(…, 0)` guards against an unexpected type error in a row.
4. Edge cases
- Trailing spaces or capitalized emails: matched (normalized on both sides).
- Blank Amount: counted as 0.
- "Paid" with a capital P: counted (status is lowercased).
- Status "refunded" or "pending": excluded.
- Order exactly 90 days ago: included (≥). Change to > for exclusive.
- Email in Summary with no orders: returns 0, not an error.
5. Test
Orders rows (email, date, amount, status) with today = 2026-10-17:
- [email protected], 2026-10-01, 120, paid → counts
- [email protected] (trailing space), 2026-09-20, 80, Paid → counts
- [email protected], 2026-06-01, 500, paid → excluded (older than 90 days)
- [email protected], 2026-10-10, 60, refunded → excluded
Expected Summary!B for [email protected]: 200.
6. Simpler approach
For 5,000 rows SUMPRODUCT over three open-ended columns is fine; above about 50,000 rows add a helper column in Orders (`=AND(LOWER(TRIM(E2))="paid", C2>=TODAY()-90)`) and use a single SUMIFS on it, which recalculates faster.
Why this works & how to customize
Why it works: formula requests go wrong on edge cases, not on the main calculation. Making you list your data's quirks up front (spaces, capitalization, blanks) and requiring the model to state its assumption produces a formula that survives real data. The test section gives you a way to verify before trusting it with 5,000 rows.
How to customize: for Excel 365 the model switches to SUMIFS or a LET-based formula; for Excel 2016 it avoids dynamic arrays. Paste 3–5 real rows (anonymized) into the layout field and the test section will use them. If you need the formula explained to someone else, keep section 3 and delete the rest. For pivot-style outputs ("per customer per month"), say so in the goal and the model will suggest QUERY or a pivot table instead of a single formula.
How to customize: for Excel 365 the model switches to SUMIFS or a LET-based formula; for Excel 2016 it avoids dynamic arrays. Paste 3–5 real rows (anonymized) into the layout field and the test section will use them. If you need the formula explained to someone else, keep section 3 and delete the rest. For pivot-style outputs ("per customer per month"), say so in the goal and the model will suggest QUERY or a pivot table instead of a single formula.
Comments (0)
Sign in to comment.
No comments yet.