# Build a Spreadsheet Formula and Prove It With Test Rows

> Get a spreadsheet formula for your exact layout, a line-by-line explanation, and a test table of edge cases showing what it returns.

- **Author:** [Saanvi Kulkarni (@saanvi_kulkarni)](https://promptabide.com/saanvi_kulkarni)
- **Tested on:** Claude · Opus 5.5
- **You fill in:** `app`, `layout`, `goal`
- **Published:** 2026-08-07
- **Updated:** 2026-09-24
- **Tags:** `spreadsheets`, `data-analysis`, `productivity`, `finance`
- **Keywords:** google sheets invoice status formula, spreadsheet formula with edge cases, overdue invoice formula google sheets, nested ifs formula help, excel formula builder prompt
- **Views:** 1267
- **Likes:** 11

**Best for:** Anyone who lives in Google Sheets or Excel and wants a formula that handles blanks and edge cases instead of breaking on row 47.

## Prompt

```
Write a spreadsheet formula for the job below, and prove it works before I paste it in.

App: {{app}}
My layout (sheet names, columns, header row, sample values): {{layout}}
What the formula must do: {{goal}}

Give me:
1. The formula for the first data row, ready to paste, and where to put it.
2. A plain-English walkthrough, one line per function or nested part.
3. A test table of 6-8 rows, including edge cases (blank cells, text where a number or date is expected, boundary values, no match found), showing the input and the exact result the formula returns.
4. Which of those results might surprise me, and a variant formula if I'd prefer different behaviour.
5. Anything in my layout that makes the formula fragile (merged cells, dates stored as text, whole-column references) and the fix.

Use only functions available in my app. Prefer readable over clever; if LET makes it clearer and the app supports it, use it.
```

## Variables

- `{{app}}` — Your spreadsheet app and, if you know it, the version (e.g. Google Sheets)
- `{{layout}}` — Sheet name, what each column holds, header row and data format (e.g. Sheet 'Invoices': A invoice no, C due date, E paid date (blank if unpaid))
- `{{goal}}` — What the formula should output, including every rule (e.g. Status: Paid, Overdue 30+, Overdue, Due soon or Open)

## Example input

```
app: Google Sheets
layout: Sheet "Invoices". A = Invoice no, B = Client, C = Due date (real dates), D = Amount (£), E = Paid date (blank if unpaid). Header in row 1, data from row 2.
goal: In column F, show a status: "Paid" if E has a date; "Overdue 30+" if unpaid and more than 30 days past due; "Overdue" if unpaid and past due; "Due soon" if unpaid and due within the next 7 days; otherwise "Open". Show nothing on empty rows.
```

## Output

*Produced by Claude · Opus 5.5.*

```
**1. Formula** (paste in F2, then fill down)

=IF(A2="", "", IF(E2<>"", "Paid", IF(NOT(ISNUMBER(C2)), "Check due date", LET(days, TODAY()-C2, IFS(days>30, "Overdue 30+", days>0, "Overdue", days>=-7, "Due soon", TRUE, "Open")))))

**2. Walkthrough**
- IF(A2="", "", ...): no invoice number means an empty row, so it shows nothing.
- IF(E2<>"", "Paid", ...): anything in the paid-date column means paid.
- IF(NOT(ISNUMBER(C2)), "Check due date", ...): Sheets stores real dates as numbers, so a blank or text due date gets flagged instead of producing a wrong status.
- LET(days, TODAY()-C2, ...): the number of days past due. It's negative if the invoice isn't due yet.
- IFS(...): checks the conditions in order. More than 30 days late, then late at all, then due within the next 7 days, otherwise Open.

**3. Tests** (assuming today is 24 Sep 2026)

| A | C (due) | E (paid) | Result |
|---|---|---|---|
| INV-101 | 20 Aug 2026 | blank | Overdue 30+ (35 days) |
| INV-102 | 25 Aug 2026 | blank | Overdue (exactly 30 days) |
| INV-103 | 24 Sep 2026 | blank | Due soon (due today) |
| INV-104 | 1 Oct 2026 | blank | Due soon (7 days ahead) |
| INV-105 | 2 Oct 2026 | blank | Open (8 days ahead) |
| INV-106 | 10 Aug 2026 | 12 Sep 2026 | Paid |
| INV-107 | 10 Aug 2026 | "part paid" | Paid |
| INV-108 | "15/09/2026" as text | blank | Check due date |
| blank | blank | blank | (empty) |

**4. Possible surprises**
- **INV-102:** exactly 30 days late shows "Overdue", not "Overdue 30+", because your rule said *more than* 30. For 30 days or more, change days>30 to days>=30.
- **INV-103:** an invoice due today shows "Due soon", not "Overdue". That's correct under your rules.
- **INV-107:** any text in E counts as paid, including "part paid". To accept only real dates, change E2<>"" to ISNUMBER(E2).

**5. Fragility**
- **Dates stored as text** (common after a CSV import) show as "Check due date". Fix them with Format, then Number, then Date, or convert them with DATEVALUE.
- **TODAY() recalculates every day**, so statuses change on their own. If you need a record, copy and paste the values at month end.
- **Sorting** is safe, because each row only refers to itself.
```

## Why it works

The **test table with edge cases** is what makes this different from asking for "a formula": it shows what the formula actually returns for blanks, text dates and boundary values, before you find out on real data. Asking **which results might surprise me** brings out the silent choices every formula makes, such as whether "more than 30" includes 30. **Readable over clever** with **LET** gives you a formula you can maintain. The **fragility check** catches layout problems, like dates stored as text, that break formulas later.

## When not to use it

For large or shared spreadsheets where logic changes often, a script, a pivot table or a proper database may serve you better than a long formula. Test results that depend on today's date (as here) will change daily, so re-check them. If you need the formula in a locked-down corporate Excel, confirm which functions your version supports.

---

Canonical HTML: https://promptabide.com/bides/build-a-spreadsheet-formula-with-test-rows
Agent guide: https://promptabide.com/llms.txt · https://promptabide.com/agent-instructions.md
Sitemap: https://promptabide.com/sitemap.xml