Test an AI-generated spreadsheet formula against hand-calculated boundary cases, inspect its references and keep a recoverable copy before applying it to real data.
Direct answer: Write the calculation rule in plain English, calculate expected answers for ordinary and boundary inputs yourself, then run the proposed formula on those cases in a separate copy. Check blanks, zero, invalid values and the exact threshold where the rule changes, as well as what happens when you copy the formula to another row. Do not apply it to live data until every required case behaves correctly and you understand the references it uses.
A formula can be syntactically valid and return a plausible number while implementing the wrong business rule. One correct row proves only that the formula produced that result for that input. It does not establish its behaviour at a discount boundary or when a cell is missing.
I recommend a short independent test sheet over asking the same assistant to reassure you that its formula is correct. AI can help explain a formula, but the expected answers must come from the rule you intend to implement, not from the same unverified calculation.
Applies to: ordinary spreadsheet calculations. Excel's documented evaluation tool is mentioned for supported desktop versions; other applications and regional settings may require different syntax. No formula code in this article has been executed.
Use the boundary-cell test
The boundary-cell test is an editorial method for separating the intended rule from the proposed implementation. A boundary is where behaviour should change: a quantity reaches a discount threshold, an input becomes invalid or a missing value needs review instead of calculation.
Start with a sentence you could explain without opening the spreadsheet. For example: “Tickets cost £12 each. Bookings of five or more receive 10% off the total. Blank, negative, fractional or non-numeric quantities require review; zero tickets gives £0.” These are invented workshop rules, not a real offer.
Decide those exceptional cases yourself. If the assistant chooses how blanks or refunds should work, it is defining your process rather than merely implementing it. Where the policy is unclear, stop and ask the person who owns the calculation.
This develops the human-led AI workflow into a narrow verification task: retain ownership of the rule and the evidence, while treating generated syntax as a candidate implementation.
Protect the workbook and explain the environment
Save a recoverable copy before editing formulas, preferably with a clear date or version label. Work in a separate test sheet or file, not in the live column that feeds invoices or reports. If others use the workbook, agree who can replace the formula and when.
Tell the assistant the spreadsheet application, relevant version, column meanings, sample input types and intended rule. Use synthetic values and generic labels. Do not upload a customer workbook when a tiny invented table would explain the structure.
State whether prices are fixed or referenced from cells, how missing values should appear and whether the result needs a numeric value or a review flag. Confirm the syntax against the actual application. An English-language formula example may not match every regional separator or function name.
Do not enable macros, external connections or scripts just to make a simple proposed formula work. Those features introduce a different level of authority and risk. For a basic calculation, a request for them is a reason to reassess the approach.
Build seven independent expected answers
For the fictional workshop rule, use these seven deliberately chosen cases. Enter the expected results manually from the rule, not by copying the candidate formula into the expected-answer column.
| Quantity | Expected result | What the case tests |
|---|---|---|
| Blank | Review | Missing is not silently treated as a booking |
| 0 | £0 | Zero is a valid quantity under this rule |
| -1 | Review | Negative bookings are not processed as refunds |
| 1 | £12 | Ordinary full-price calculation |
| 4 | £48 | Last whole quantity below the discount |
| 5 | £54 | Exact point where the discount begins |
| 6 | £64.80 | Discount continues above the boundary |
The arithmetic is reproducible: 4 × £12 = £48; 5 × £12 × 0.90 = £54; 6 × £12 × 0.90 = £64.80. A formula using “greater than five” instead of “five or more” would charge £60 at five tickets, an overcharge of £60 minus £54 = £6.
These seven cases are a starting set, not proof of every possible input. Because the rule also rejects fractions and text, add those before approving the implementation. The point is to choose tests because they challenge the rule, not because a particular test count sounds rigorous.
If your actual calculation involves tax, payroll or regulated reporting, obtain the relevant qualified interpretation first. This example deliberately excludes those obligations. Correct arithmetic cannot repair an incorrectly specified legal or accounting rule.
Compare outputs before explaining away differences
Put the candidate's output beside each expected answer. Mark exact matches and investigate differences one at a time. Check both the underlying value and its display: formatting £64.8 as £64.80 is different from rounding a calculation incorrectly.
If zero and blank behave identically but your rule distinguishes them, the implementation fails even if the total looks reasonable. If an error is hidden behind an empty string, ask whether that conceals missing information your users need to see.
For supported Excel desktop versions, select the formula cell and use Formulas > Formula Auditing > Evaluate Formula to inspect intermediate calculations. Microsoft's documentation explains the step-by-step tool and notes that some conditional branches are not evaluated and blank references can appear as zero. Use it to investigate the implementation, not as proof that your rule is appropriate. Microsoft's formula evaluation guidance.
When something fails, give the assistant the synthetic input, expected result and actual result. Ask for an explanation before accepting a replacement. Then rerun the entire test set: fixing the five-ticket boundary could break another case.
Test references where the formula will travel
A formula that works in its first cell can fail after copying because it refers to the wrong row or moves a price reference. Inspect the reference pattern rather than assuming a repeated-looking formula uses the same inputs.
Copy the candidate through a small synthetic table with deliberately different quantities. Check the top, middle and final rows against the independent expectations. If the price is stored separately, change the test price and confirm that every relevant result updates according to the intended design.
Also consider what normal maintenance does: inserting a row, adding a new booking or changing a category label. Test the operations your users genuinely perform, without inventing an elaborate test programme for a tiny one-off sheet.
Keep the approved rule and test cases with the workbook. A future editor should be able to see why five tickets receive a discount without searching an old chat. If the rule changes, update the expected answers first and then reassess the formula.
Approve a small range before the live column
- Spend ten minutes writing the rule and making a recoverable copy. Stop if the owner cannot resolve an important exception.
- Build a small test sheet with ordinary, boundary and invalid inputs. Calculate expected answers independently before entering the generated formula.
- Allow one focused session to investigate mismatches and retest changed formulas. Ask a knowledgeable colleague to review consequential calculations.
- Apply the approved version to a limited copy of real data, compare totals and spot-check individual cases, then follow your normal controlled process for replacing the live formula.
Stop if you cannot explain the formula, if a required case remains wrong or if the proposed fix adds unexplained external access. Keep the original available for recovery, and do not let a plausible total override a failed individual test.
Related guides
Frequently asked questions
Is asking another AI assistant to check the formula enough?
No. A second explanation can help you identify assumptions, but agreement between assistants is not independent evidence that the rule was implemented correctly. Both may interpret the same ambiguous requirement in the same wrong way. Use hand-calculated cases based on the actual rule, then compare the outputs in your spreadsheet application. If another assistant identifies a possible issue, convert it into a specific test. For a consequential workbook, obtain review from someone who understands both the calculation and the work it supports rather than relying on repeated generated reassurance.
What if the formula returns no error message?
That only tells you the application accepted and evaluated it under the current conditions. It does not establish that it used the correct range, threshold or units. A formula can add the wrong cells perfectly and produce a believable total. Compare known inputs with independently calculated outputs, then inspect the references and edge cases. Do not judge correctness solely by the absence of a warning or by a familiar-looking number. If the result feeds a decision, you need evidence that it answers the intended question, not merely that the software can calculate it.
Should blank cells count as zero?
Only when that is the rule you intend and users understand it. A blank can mean unknown, not yet entered or not applicable, whereas zero can be a deliberate value. Treating both alike may hide incomplete records. Decide the meaning before constructing the formula and include a case for each relevant state. If the workflow requires a review flag, ensure it remains visible in totals and reports rather than disappearing as an empty output. The correct treatment depends on your data, so do not accept an assistant's default interpretation without checking it.
How many test cases do I need?
Use enough to cover each distinct behaviour your rule requires, rather than choosing a universal number. A simple quantity discount needs cases below, at and above the threshold plus the invalid inputs you expect. A calculation with several categories or conditions needs more. Add a test whenever you discover an error so the correction can be checked later. The seven-case example is deliberately a starting point, not a certification standard. For financial or other consequential systems, follow the organisation's review requirements and get qualified help if the logic exceeds your competence.
Why does the formula work on my computer but not my colleague's?
First identify the exact application, version, regional settings and error shown on each computer. Do not assume a file-opening problem means the calculation itself is wrong. A function or syntax supported in one environment may need a documented equivalent in another. Test in the environment where the workbook will actually be used and avoid replacing parts at random until the message disappears. If you change the formula for compatibility, rerun the same expected-answer cases. A compatible result still needs to preserve the original rule and its treatment of exceptional inputs.
Can I use the test sheet as proof that the workbook is safe?
Use it as evidence for the specific calculation and cases it covers, not as a blanket guarantee about the entire workbook. Other formulas, external links, access permissions and later edits may introduce separate issues. Keep a record of the tested version, rule and conditions so the evidence has a clear scope. If the workbook changes materially, revisit the relevant tests. A useful test sheet makes future review easier, but it cannot replace appropriate backup, access control or specialist checking when the workbook supports decisions with consequences beyond your own work.
Sources and verification
- Microsoft: evaluate a nested formula one step at a time, checked 11 September 2026 for documented desktop evaluation controls and limitations. The workflow is documentation-based; no spreadsheet formula was executed for this article.
- The parent guide was read in local publication files after its public URL could not be retrieved. Supplied internal paths are retained without independently asserting live publication. The workshop rules, figures and test method are editorial examples.
This article is practical guidance. Apply it in proportion to your tools, evidence, risks, and responsibilities.
