Create a downloadable spreadsheet with working formulas
ChatGPT hands you a neat table with the right total, and the first time someone changes a quantity, nothing moves. The useful test is whether the downloaded workbook updates when you change an input.
What you'll make
a small cost workbook you can download and edit, with quantities and prices as inputs, line costs and a grand total as formulas, and a total you’ve tested by changing one input.
What you'll need
ChatGPT with file creation (it can create a spreadsheet you review in the chat and download), Excel, Google Sheets or another spreadsheet app to open it, and about 15 minutes. If file creation isn’t available, ask for the layout and formulas and build the sheet yourself.
Example result.
Item Qty Unit £ Line £ (formula)
Display stand 3 18.00 =B2*C2 54.00
Desk lamp 2 12.50 =B3*C3 25.00
Storage tray 4 6.00 =B4*C4 24.00
Total =SUM(D2:D4) 103.001. Define the inputs and the calculation
Pick one small table. Name each input, its unit and the calculation: here, quantity times unit cost, then a total. Decide what a blank means. A blank price is an unknown price, not a free item.
Note anything outside the total, such as VAT or delivery, so the sheet can say so.
Why this works: a spreadsheet can’t fix an unclear definition of “total”.
What you should see: three or four lines you could hand to anyone: the inputs, the formula, what blank means, what’s excluded.
2. Ask for the workbook, not a table
Create a downloadable .xlsx workbook called cost-practice.xlsx from
the data below. Use one sheet called Costs with Item, Quantity,
Unit cost (GBP) and Line cost (GBP). Quantity and Unit cost are
editable inputs. Line cost must be a formula, and the grand total
must be a formula. A blank price means missing, not zero: show a
warning instead of a complete total. Note that the total excludes
VAT and delivery.
[item, quantity, unit cost][item, quantity, unit cost] is your data, one row per line.
Why this works: asking for the file and the formulas gets a workbook. Asking for “a table” often gets numbers that look right and can’t change.
What you should see: a spreadsheet you can preview in the chat and download.
3. Open the file and change one input
Download the .xlsx and open it in your spreadsheet app. Click a line cost and read its formula in the formula bar. A number on its own doesn’t tell you whether it’s calculated or typed in.
Then do the test that matters: change one quantity and watch the line cost and the total. Work out what they should become before you look. Change it back, save, close and reopen the file.
Why this works: a workbook that updates is a working workbook. One that doesn’t is a picture of one.
What you should see: the line and the total both changing to the numbers you predicted, and the file reopening with formulas intact.
4. Test a zero and a blank
Set one quantity to 0: that line should become zero and the total should drop by exactly that line. Then clear one price instead: the sheet should show its missing-input warning, not a smaller total that looks complete.
Why this works: zero and blank are different facts, and a careless formula treats them the same.
What you should see: a lower total for the zero, and a warning for the blank.
Check the result
- Every line cost and the total are formulas, visible in the formula bar.
- Changing a quantity changes the line and the total to the numbers you predicted.
- A zero quantity gives a zero line; a blank price gives a warning, not a total.
- The total’s range includes every line, including the last one.
The usual failure: the totals are typed numbers, or the total formula misses a row, so the sheet looks right until someone edits it. Spot it by changing a quantity: if the line changes but the total doesn’t, check the total’s range; if neither changes, the cells don’t contain formulas at all.
Repair the workbook. [Cell or range] currently [problem]. It should
[required behaviour]. Keep the structure, inputs and units. Recheck
the base total, one changed quantity, a zero and a blank price, then
save a corrected .xlsx and tell me the formulas you used.[Cell or range] is where it went wrong, such as “D5”; [problem] is what you saw; [required behaviour] is what it should do.
Worked example: the changed quantity matters
Fictional example. Costs exclude VAT and delivery.
Item,Quantity,Unit cost GBP
Display stand,3,18
Desk lamp,2,12.50
Storage tray,4,6The answer key, worked out first: £54 + £25 + £24 = £103. With five stands, the stand line is £90 and the total should be £103 + (2 × £18) = £139.
In the first workbook, changing stands to 5 updated the stand line to £90, but the total stayed at £103. The total cell held a typed number, not a formula. After the repair prompt (“D5 currently shows a fixed 103. It should be =SUM(D2:D4)”), the total moved to £139.
What mattered:
- The test caught it, not the first look. £103 was right at the start, so nothing looked wrong until an input changed.
- Predicting first made it obvious. Knowing it should be £139 meant £103 couldn’t pass as “close enough”.
- Zero and blank stayed different. With trays set to 0, the total is £79. With the tray price cleared, the sheet shows a warning instead, because an unknown price isn’t free.
Try it
Change a line. The total should follow.
Move the stand quantity; the other two lines stay fixed.
Complete stated item total: £103.
Task kit for this job: Download (ZIP) · Reusable instructions
Go deeper
Check a new row is included
Add a fourth line, two clips at £3, and the base total should become £109; with five stands, £145. If it stays at £103 or £139, the total’s range didn’t grow. Ask for the range to be extended, or for the data to be set up as a table so new rows are included automatically, and test again with the next row you add.
Add rules for messy inputs
Once the formulas work, you can ask for checks on the inputs themselves:
Add input rules to the Costs sheet: quantities must be whole numbers
of zero or more, and prices must be zero or more. Explain the rules
in a note on the sheet. Keep input cells visually distinct from
formula cells. Do not change the formulas.Then try a negative quantity, text in a price cell and a fraction. Input rules help the next person; they’re not a substitute for testing the formulas.
Keep the workbook understandable
Put assumptions and exclusions next to the inputs, not in your head. Keep a clean base copy before experimenting, and share the checked file with a line saying what to edit and what the total covers. For models with several assumptions, see building a simple scenario model.