Shop / Prompts & Skills / Explainable Spreadsheet Formula — Formula & Sanity-Check Prompt for Office Coordinators

Explain any formula in plain English

You inherit a spreadsheet — from a colleague, a client, or your own past self — and one formula in it decides real money. It looks plausible. Nobody can say what it actually calculates, so nobody touches it. Here is how to take a formula apart, check it by hand, and spot the ways it can be quietly wrong.

A worked teardown: one formula, piece by piece

This is a real output from our spreadsheet formula prompt, on a small formula with a common job: multiply quantity by unit price, but leave incomplete rows blank.

=IF(OR(B2="",C2=""),"",B2*C2)

• OR(B2="",C2="") — asks one question: is either input cell empty?
• IF(..., "", ...) — if something is missing, the result stays blank instead of showing a wrong number.
• B2*C2 — otherwise, quantity × unit price.

Two details the explanation calls out, because they are where trust is won or lost: no absolute references are needed here, and the check assumes columns B and C hold numeric values — not prices stored as text.

Goal: Multiply quantity in column B by unit price in column C, but keep incomplete rows blank.
Formula: =IF(OR(B2="",C2=""),"",B2*C2)
Paste in: D2; fill downward so row references update.
Why it works: OR detects missing inputs; otherwise the row's two cells multiply. No absolute references needed.
Manual check: Row 2 = 3 × 12.50 = 37.50; row 3 = 2 × 8.00 = 16.00.
Assumptions / verify: B and C contain numeric values, not prices stored as text.

Sample data above is fictional, shown so you can judge the output before buying.

The hand-check routine

1. Pick a row you can already price by hand. In the sample: row 2 is 3 × 12.50, row 3 is 2 × 8.00.
2. Predict the result before you look: 37.50 and 16.00.
3. Compare. If the sheet disagrees with your arithmetic, the formula — not your arithmetic — is the first suspect.

One minute, no tools. A formula that survives a hand-check on two or three known rows has earned provisional trust; one that fails gets explained before it gets used.

The 4 silent ways a plausible formula goes wrong

1. Wrong column. The formula points at a neighbouring column that holds similar-looking numbers. Results stay reasonable, so nobody notices.
2. Reference drift. Fill a formula down a column and its row references move with it — including the one cell (a rate, a threshold) that was supposed to stay fixed.
3. Hidden rounding. The display shows two decimals; the stored value carries more. Totals built from what you see disagree by cents.
4. Stale range. A total still ends at row 40 while the data now ends at row 52. Everything below the range is silently outside the sum.

None of these announce themselves. Each one is a question a plain-English explanation forces into the open: what does this part read, what does it assume, and where does it stop?

Questions