Prompt: Write an Excel Formula from a Description

Turns a plain-English description of a calculation into a working Excel or Google Sheets formula, with a one-line explanation of how it works.

The prompt

Open in Studio

You are a spreadsheet specialist who writes formulas that work first time, referencing only the columns you're given.

Objective: Write a {{SPREADSHEET APPLICATION — e.g. Excel, Google Sheets}} formula that {{DESCRIBE THE CALCULATION}}.

Context: {{COLUMN LAYOUT — e.g. column A has order date, column B has region, column C has revenue}}.

Constraints:
- Reference only the columns named in the context above; do not invent a column that wasn't given
- Return exactly one formula, not several alternative versions
- Handle blank or zero values explicitly if the result could otherwise divide by zero or return an error
- Use functions compatible with the named application only; do not mix Excel-only and Sheets-only functions

Example: "the average revenue per region, ignoring blank rows" is specific enough to write directly; "some kind of summary of sales" is not.

Output format: Return the formula in a single fenced code block, followed by exactly one sentence explaining what it does in plain English.

Before answering, check the formula only references columns named in the context and would not error on a blank input cell.

Example

Input

SPREADSHEET APPLICATION — e.g. Excel, Google Sheets: Excel
DESCRIBE THE CALCULATION: the average revenue per region, ignoring blank rows and treating a blank as excluded rather than zero
COLUMN LAYOUT — e.g. column A has order date, column B has region, column C has revenue: column A has order date, column B has region, column C has revenue

Output

```excel
=IFERROR(AVERAGEIFS(C:C, B:B, B2, C:C, "<>"), "")
```
This formula averages the revenue values in column C for every row whose region in column B matches the region in the current row, with the "<>" condition making it explicit that blank revenue cells are excluded from the average rather than counted as zero, and IFERROR returning an empty cell instead of a #DIV/0! error whenever the region in the current row has no filled revenue entries at all to average in column C.

Why it works

Naming the exact column layout stops the model guessing at ranges or inventing a column that isn't in the sheet.
Requiring exactly one formula, not several alternatives, avoids a wall of options you'd otherwise have to test one by one.
The pre-answer check for blank-cell handling catches a divide-by-zero or #DIV/0! error before it reaches a live spreadsheet.

Why the basic version fails

“Write me a formula to average revenue by region” without the column layout forces the model to guess which column holds what, producing a formula that references the wrong range or that breaks the moment a blank cell appears in real data.

What this prompt fixes

Naming the exact columns removes the guessing, and asking for exactly one formula (not several near-identical variants) saves the time otherwise spent testing each one to see which actually matches your sheet. The blank-handling check specifically catches the #DIV/0! error that a naive AVERAGEIFS or division formula produces the first time it meets a genuinely empty cell.

Tips per model

ChatGPT writes clean formulas when the column layout is given; without it, it will confidently guess a layout that doesn’t match your sheet.
Claude is good at flagging when a description is ambiguous between two different calculations (e.g. average of the visible column versus a filtered dataset) — let it ask rather than assuming.
State whether your headers are in row 1 — some formulas need adjusting if the real data starts on a different row than the model assumes.