Why ask for the breakdown if the formula works?

Because “works” means “returned a number on today's data”. The breakdown is how you find out whether it is right, and it is what lets you maintain the sheet later.

Should I ask for a formula or a script?

Ask for the formula first and say “the simplest correct alternative” — a script is harder to audit and harder to hand over. Move to a script only when the formula genuinely cannot express the logic.

Data & spreadsheets

Formula with explanation

Ask for the formula, a part-by-part explanation, and the case where it breaks. Pasting a formula you cannot read is how spreadsheets go quietly wrong.

Use this every single time you ask for a spreadsheet formula, however small.

The pattern

Copy this and replace everything in [SQUARE BRACKETS] with your own specifics.

Prompt template

I have a sheet in [EXCEL / GOOGLE SHEETS / LIBREOFFICE].

Layout: [WHICH COLUMN HOLDS WHAT, with the header row number and the first data row number. Give two or three real example rows, including a messy one.]

I want: [WHAT THE RESULT SHOULD BE, in words, with the answer for one example row so we agree on the target.]

Give me:
1. The formula, ready to paste into [CELL REFERENCE].
2. A part-by-part breakdown — what each function and argument does, in plain words.
3. Three inputs that would break it or give a wrong answer: an empty cell, a text value where a number is expected, and one more of your choosing. Say what it returns in each case.
4. The simplest correct alternative, if one exists.

Why each part is there

Layout with a messy example row: Formulas break on real data, not on clean data. Showing the blank cell or the stray “N/A” up front gets you a formula that survives your actual sheet.
The answer for one example row: A shared target. Without it you and the model can agree on the words and still disagree on the arithmetic — usually about whether a boundary is inclusive.
The part-by-part breakdown: Turns a paste into a lesson. A formula you can read is one you can fix in six months; a formula you cannot read is a liability with a plausible answer in it.
Three inputs that break it: Where the real bugs live. Spreadsheet errors are almost never wrong logic — they are correct logic meeting an empty cell and returning zero.

Before and after

The lazy version

Give me an Excel formula to find the average score of students who passed.

What goes wrong: You get a plausible AVERAGEIF, paste it, and it returns a number. It looks right. It silently counts blank cells as fails, or treats the pass mark as exclusive, and nobody notices for a term.

The pattern, filled in

Worked example

I have a sheet in Google Sheets.

Layout: row 1 is headers. Data starts row 2. Column A = student name, B = marks out of 100, C = attendance percent. Example rows:
  A2 "Anjali", B2 78, C2 91
  A3 "Rohit", B3 (blank — he was absent), C3 44
  A4 "Sana", B4 "AB", C4 12

I want: the average marks of students who scored 35 or more, counting 35 itself as a pass, and ignoring blank and non-numeric marks entirely. For these three rows the answer should be 78.

Give me:
1. The formula, ready to paste into E2.
2. A part-by-part breakdown — what each function and argument does, in plain words.
3. Three inputs that would break it or give a wrong answer: an empty cell, a text value where a number is expected, and one more of your choosing. Say what it returns in each case.
4. The simplest correct alternative, if one exists.

What that buys you: A formula that gives 78 rather than 26, because you named the blank and the “AB” before they could quietly become zeros — plus a breakdown that means you can adapt it yourself next term.

Now do it on your own work

Take a formula already sitting in one of your sheets that you did not write or no longer understand. Ask for the breakdown and the three breaking inputs, then actually type those inputs into a copy of the sheet.

Check yourself — every box should be true

You can explain every argument in the formula out loud, without notes.
You typed all three breaking inputs into a copy and recorded what each returned.
At least one of them produced a wrong answer rather than an error — and you know which.
You either fixed the formula or wrote down the condition under which it is unsafe.

Then explain it back

One of the breaking inputs returned a wrong number instead of an error. Why is that more dangerous than a formula that visibly fails?

Write two or three sentences in your own words. If you cannot, you copied a prompt rather than learning a pattern — which is the whole difference this library is trying to make.

Does this depend on which AI tool I use?

Function names and separators differ between Excel, Google Sheets and LibreOffice — TEXTJOIN, ARRAYFORMULA and the newer LAMBDA-style functions especially — so naming your tool in the prompt is not optional. Some locales also use semicolons as argument separators; if a pasted formula errors immediately, that is usually why.

Why it actually works

Reading a formula part by part instead of trusting its output is the same habit that lets you read the mathematics behind a model instead of trusting a library call.

The maths you actually need →

Prompting well is a skill you can pick up in a week. Understanding what is happening underneath is the 18-month curriculum — and it is what stays useful when the tools change.

FAQ

Why ask for the breakdown if the formula works?: Because “works” means “returned a number on today's data”. The breakdown is how you find out whether it is right, and it is what lets you maintain the sheet later.
Should I ask for a formula or a script?: Ask for the formula first and say “the simplest correct alternative” — a script is harder to audit and harder to hand over. Move to a script only when the formula genuinely cannot express the logic.

Next patterns

The Prompt Patterns Library is a separate, optional track — it does not change the 78-week curriculum or your roadmap. See what the curriculum actually covers →