Did you know that a spreadsheet can display a perfectly plausible answer while using the wrong formula? A neat total at the bottom of a school project budget is not proof that the calculation is sound. The more powerful habit is to predict what should change when one input changes, and then check whether the spreadsheet behaves that way.
A one-input prediction test is a simple way to examine a formula: first understand the relationship it is supposed to represent, then change one input by a known amount, predict the new answer independently, and compare that prediction with the recalculated output. It helps students test the structure of their spreadsheet rather than trusting whatever number appears.
This article uses invented classroom scenarios, not real student records or audited accounts. The lesson is about thinking independently of the software. A formula check is one useful test, not a guarantee that every hidden error has been discovered.
First diagnosis: what should happen if the number of kits rises?
Imagine a class preparing small science kits. The fictional working plan says each kit costs S$7.50 in materials and the whole order has one fixed S$15 packaging fee. Twelve kits are requested. Without opening a spreadsheet, ask the learner: If we order one extra kit, by exactly how much should the bill rise?
The answer is S$7.50. The number of kits changes, but the one-off packaging fee does not. If the learner says S$22.50, they may have multiplied the fixed fee by the number of kits. If they say S$15, they may have focused on the wrong cell. This diagnostic reveals conceptual confusion before the teacher asks the student to enter a single formula.
A complete worked example: cost, change, and independent check
Set up the fictional sheet with B2 = 7.50 (Singapore dollars per kit), B3 = 12 (kits), B4 = 15.00 (Singapore dollars per order), and B5 containing =B2*B3+B4. Give every input a label and unit. A count is not money, and a cost per kit is not a fixed charge.
- Materials: 12 kits × S$7.50 per kit = S$90.00.
- Fixed packaging: S$15.00 once, regardless of the number of kits.
- Expected total: S$90.00 + S$15.00 = S$105.00.
- Prediction: raising B3 from 12 to 14 kits adds 2 × S$7.50 = S$15.00.
- Expected new total: S$105.00 + S$15.00 = S$120.00.
Now, and only now, change B3 to 14. The correct formula yields S$120.00. Restore B3 to 12 after the test and confirm that the original S$105.00 comes back. This last step matters: students sometimes leave a test value in a file and submit an unintended budget.
The independent calculation tests the difference between two outputs as well as the output itself. If the spreadsheet shows S$105.00 initially but S$135.00 after the change, the formula may be wrongly charging a fee for every kit or reading a different cell. The actual numbers are illustrative: none describes a real supplier quote.
How a plausible wrong formula reveals itself
Suppose the learner accidentally enters =B2*B3+B4*B3. For twelve kits it gives S$270.00, because the S$15 fee is counted twelve times. After changing to fourteen kits it gives S$315.00, a rise of S$45 rather than S$15. A student who understands the situation can reject the result without needing to know the exact spreadsheet error at first glance.
A subtler mistake uses a cell containing the correct total entered manually, not a formula. The value looks right at twelve kits, yet changing the count to fourteen leaves the displayed total stuck at S$105.00. Here the prediction test detects that the output has not responded at all.
Why spreadsheet testing is really mathematical reasoning
A formula is a model of a relationship. In this example, total cost is (cost per kit × number of kits) + one-off fee. One part depends on the number of kits and the other does not. That is why a change in quantity should alter the total in a predictable way. The student is learning to distinguish a variable part from an invariant part rather than merely memorising multiplication signs.
Microsoft’s explanation of precedent and dependent cells in Excel is useful further reading: a precedent supplies data to a formula, while a dependent cell uses other cells. Features such as Trace Precedents can help locate references. But the software can trace a perfectly functioning wrong model; the learner still has to decide whether the model matches the real question.
A repeatable six-step classroom method
- State the claim: write in words what the result cell means, including units.
- Sketch the relationship: identify what is charged per item and what is charged only once.
- Calculate a baseline independently: obtain the expected answer before asking the program.
- Change one input: choose a small, meaningful change, such as two more kits.
- Predict then inspect: compare both the new total and the direction and amount of change.
- Restore and record: return the original inputs and note what was verified and what remains unchecked.
Change only one input for a basic test. If price, quantity and packaging all change simultaneously, several possible errors can cancel or amplify each other. Students may then reach the right-looking total for the wrong reasons. A one-input test makes cause and effect easier to trace.
Guided practice: where should the cost move?
Practice A: the class photocopy budget
A teacher invents a sheet showing S$0.08 per sheet, 250 sheets and one S$5 delivery charge. Ask the student to calculate S$25.00, then predict that 300 sheets will cost S$29.00. The rise is 50 × S$0.08 = S$4.00. Do not let the pupil enter the new value until they can explain why the delivery charge stays unchanged.
Practice B: a fixed charge disappears
Return to twelve science kits. Change the one-off packaging fee from S$15 to S$0, leaving everything else unchanged. The total must fall from S$105 to S$90, exactly S$15. If it falls by S$180, packaging has probably been applied repeatedly.
Practice C: the learner chooses the test
Give a student a small project budget with one variable price and one fixed fee, but do not suggest which input to edit. Ask: ‘Which one-cell change will expose an error most clearly, and what will you predict before making it?’ The student should choose, explain, test, restore, and describe the limitation of that test.
When this technique is helpful—and when it is not enough
Prediction tests are especially useful for classroom fundraising sheets, materials plans, calculated averages, inventory records, and simple science tables. They are also useful whenever an answer changes because an assumption changes. They expose certain wrong references, missing multipliers, incorrect fixed charges and pasted values masquerading as live calculations.
But one successful test cannot prove a whole workbook correct. Two incorrect formulas may respond to a particular change in the same way. A formula may work for positive quantities but fail at zero; a copied formula may work in the first row but refer to the wrong row later. Advanced sheets can contain conditional calculations, hidden rows, external links or volatile functions that require different checks.
A stronger follow-up is to test a boundary case such as zero kits and one kit, inspect important cell references, and check an independently calculated example. For this particular model, zero kits still incurs the S$15 fixed packaging charge if the stated policy genuinely charges it even when no kits are ordered. If the supplier would waive it at zero, the model itself must change. The real-world assumption comes before the spreadsheet.
What parents and teachers can actually observe
A parent need not learn an entire spreadsheet menu to help. Show a small total and ask the child, ‘If we order two more, what should happen to the price?’ A learner who explains the movement has more transferable understanding than someone who can only point to a green cell containing the answer.
A teacher can collect three short pieces of evidence: the student’s written prediction, the changed spreadsheet output, and a sentence explaining whether the result matched. Across several lessons, progress means fewer unexplained surprises, quicker identification of fixed versus changing quantities, and increasingly independent selection of good tests—not a claimed examination-score guarantee.
Students who first need practice seeing what is and is not included in a sum can read how subtotals can be counted twice. For a different error mechanism, see why mixed measurement units spoil a spreadsheet formula. Both connect calculations back to meaning.
Frequently asked questions
Does Excel or another spreadsheet automatically find every wrong formula?
No. Software can detect some syntax and reference problems. It cannot reliably know whether a mathematically valid formula reflects the intended classroom rule. A person must supply and test that meaning.
Why not simply compare the final total with a calculator?
An independent total is valuable, but changing one input tests an additional relationship. A manually typed total might match once but fail to update. The two checks complement each other.
Should students use real personal or payment information?
Not for practice. Use fictional prices and anonymous counts. If a school handles real payments, authorised adults should apply the school’s actual financial controls and data-protection requirements.
What if the prediction and spreadsheet agree?
Record that the tested case behaved as expected. Do not write ‘verified error-free’. Test another case, including a boundary, if the decision is important.
A useful way to finish
A spreadsheet should extend a student’s capacity to reason, not replace it. The learner’s most important sentence is not ‘the computer says S$120’. It is ‘I expected a S$15 rise because only two S$7.50 kits changed, and the formula behaved consistently with that explanation.’ That is the core educational gain: independent checking that transfers into new decisions.
For broader learning pathways, visit the eduKate Education Hub. The diagnosis-first, explain-first and progressively independent practice philosophy is also visible in the unchanged Secondary 1 Mathematics tutor Clementi reference.