Spreadsheet formula builder with explanation

Describe what you want in plain English and get a working Excel or Google Sheets formula, how it works, and edge cases.

LLazyPrompt(@lazyprompt)Data & AnalysisGenerateWorks with any model
No ratings yetSign in to rate
Save 0 Fork Report
Prompt template
Write a {{app}} formula for this task.

What I want to calculate:
{{task}}

My sheet layout (columns, headers, example rows):
{{layout}}

Where the result goes: {{result_location}}

Requirements:
- Give the single best formula first, ready to paste, using the exact cell references and ranges from my layout.
- Prefer approaches that work in the current version of {{app}}. If a modern function is much better but may not be available to everyone, give a fallback version too.
- Explain how the formula works in 3-5 short bullet points, one per function or step.
- Test it on 2-3 rows from my example and show the expected result for each.
- List edge cases (blanks, text in number columns, duplicates, errors) and say how the formula behaves for each. Wrap it in error handling only if that makes sense, and say why.
- If my layout is ambiguous or you must assume something, say so before the formula and ask one concise question at the end.

Format: Formula, How it works, Worked check, Edge cases, Alternative (only if meaningfully different).
Example output
**Assumptions**: Dates in column A are real dates (not text). Region names are in Summary!A2:A6. **Formula** (in Summary!B2, fill down): =SUMIFS(Orders!$D$2:$D$500, Orders!$B$2:$B$500, $A2, Orders!$C$2:$C$500, "Shipped", Orders!$A$2:$A$500, ">="&TODAY()-30) **How it works** - SUMIFS adds column D only for rows meeting every condition. - $A2 matches the region on each summary row; the $ locks the column when you fill down. - ">="&TODAY()-30 builds a date criterion as text, which SUMIFS reads correctly. **Worked check** - Order 12-Mar, East, Shipped, 120, today 20-Mar: counted for East. - Order 01-Feb, East, Shipped, 80: excluded, older than 30 days. - Order 18-Mar, East, Pending, 60: excluded, wrong status. **Edge cases** - Dates stored as text are silently ignored. Check with =ISNUMBER(Orders!A2). - Blank amounts count as 0. Trailing spaces in "Shipped " will not match. - A region with no matches returns 0, not an error. Question: should the 30 days include today? If you want a fixed window instead, tell me the dates.

Why it works

Giving real column letters and example rows lets the model produce a paste-ready formula and a checkable worked example. Always run the worked check against your own data.

Licensed CC0 (public domain) Version 1 · history