Write a spreadsheet formula and understand it

Write this formula, explain it, then try to break it.

What I need it to do, in plain language:
[DESCRIBE IT AS YOU WOULD TO A COLLEAGUE]

Excel or Google Sheets, and version: [E.G. EXCEL 365, GOOGLE SHEETS]
What is in the relevant columns: [COLUMN LETTER, WHAT IT HOLDS, AND AN EXAMPLE VALUE FOR EACH]
Where the formula goes and how far it is copied down: [DETAIL]
Real quirks in my data: [BLANKS, TEXT THAT LOOKS LIKE NUMBERS, TRAILING SPACES, MIXED DATE FORMATS, DUPLICATES, MERGED CELLS — OR "NOT SURE"]
How many rows: [ROUGHLY]

Produce four sections.

1. THE FORMULA — ready to paste, with the correct argument separator for my locale noted if it might differ.

2. PIECE BY PIECE — break it into its parts and say what each one does in one line. Name every function and say why that one rather than an obvious alternative. If I could do this more simply, show the simpler version too.

3. WHAT IT DOES WITH AWKWARD DATA — a table: blank cells, text stored as numbers, numbers stored as text, a value that appears twice, a date in the wrong format, a row where the lookup finds nothing, and a value with a trailing space. For each: what my formula returns, and whether that is right.

4. WHERE IT SILENTLY GIVES THE WRONG ANSWER — cases producing no error and the wrong number. Lookups matching the nearest value instead of an exact one, a range that does not expand when rows are inserted, a sum that skips text-formatted numbers, a condition that excludes blanks when it should count them.

If my description and my example data disagree, follow the data and say so. If this really needs a pivot table or a helper column instead of one long formula, say that first.

A spreadsheet formula that is wrong almost never announces it. It does not error — it returns a number, the number is the right shape, and it goes into a report. The classic version is a lookup that matched approximately instead of exactly and quietly returned the value from the row above, which is off by one in a way nobody notices for a quarter.

That is why the fourth section exists and why it is worth more than the formula itself. Anyone can get a working formula; what is hard to get is the list of inputs where your working formula produces a confident wrong answer. Real spreadsheets are full of exactly those inputs — the trailing space from a paste, the number stored as text after an export, the blank that is not empty.

Section two is not padding either. A formula you cannot read is one you cannot safely change in six months, and the usual result is a second formula bolted onto the first in the next column.

1. Describe your columns and give an example value for each

   Nearly every wrong formula comes from a wrong assumption about what is in a cell. One example value per column removes most of them, and it is what section three is built from.

2. Admit the mess in your data

   Trailing spaces, mixed date formats, numbers that arrived as text. 'Not sure' is a fine answer and will make it check all of them — the tidy data you describe is not the data you have.

3. Say Excel or Sheets, and where you are

   Functions differ between them, newer array functions do not exist in older Excel, and locales use a semicolon instead of a comma as the argument separator. Any of the three produces a formula that simply will not paste.

4. Actually run the cases in section three

   The table is written to be tested, not read. Put the awkward values in five spare rows and check the results match what it predicted before you trust the column.

• It cannot see your spreadsheetEverything rests on the description you gave. A column that holds something other than what you said produces a confident formula for a different problem — which is why section three exists and why you should run it.
• Newer functions in older ExcelXLOOKUP, LET, and the dynamic array functions do not exist before certain versions and will not work in a shared file opened by a colleague on an older one. State your version, and the oldest version anyone else will open it with.
• When the answer is not a formulaPast a certain complexity the right tool is a pivot table, Power Query, or a database. A formula long enough to need scrolling is a maintenance problem you will inherit from yourself.

• CodingRagnarökDebug a stack traceTurns an error and its stack trace into an ordered list of what could cause it, each with the specific check that would rule it in or out.
• CodingApolloWrite and test a regex or SQL queryWrites the pattern or query you asked for, explains it piece by piece, and then supplies the inputs that make it silently return the wrong thing.
• CodingRagnarökReview a diff for production risksReviews a change for the failures that only appear under real traffic and real data, and separately lists which of the new behaviour has no test.
• WritingApolloRemove AI tells from a draftRewrites text that reads as machine-written, then lists the specific phrases and structures that gave it away so you stop producing them.