Saanvi Kulkarni

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.

At a glance

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.
Tested on: Claude · Opus 5.5
You fill in: applayoutgoal
You get: 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,… (full result below)

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.

1. A plain-English walkthrough, one line per function or nested part.

1. 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.

1. Which of those results might surprise me, and a variant formula if I'd prefer different behaviour.

1. 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.

Fill in

{{app}}: Your spreadsheet app and, if you know it, the versione.g. Google Sheets
{{layout}}: Sheet name, what each column holds, header row and data formate.g. Sheet 'Invoices': A invoice no, C due date, E paid date (blank if unpaid)
{{goal}}: What the formula should output, including every rulee.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.

1.3k0

Generated Outputs (1)

2 hours ago

Claude

Opus 5.5

2 hours ago

Generated Output

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.

Comments (0)

No comments yet. Be the first to share your thoughts!