Keep the workbook as the source of truth
AI can help explain a formula or reshape rows, but the workbook must retain the source cells, assumptions and reconciliation totals. If the final number cannot be rebuilt, it cannot be trusted.
The rewritten walkthrough connects the five-row formula to eight executed fixtures, explaining why even the correct total can fail the input contract.
Use the invoice table and downloadable test cases to check duplicates, inconsistent labels, number types and unexpected amounts.
Keep in mind: Matching row counts and plausible totals are weak checks. They will not catch every wrong category, duplicated record or silent unit conversion.
In this article7 sections
Editorial note: The invoice rows and totals are fictional and are provided for practice. This is not accounting, tax or financial-control advice, and a real workbook needs review against its own records and rules.
Ask which rows made the number
A total of CAD 170 looks reassuring when CAD 170 is what you expected. But a matching number cannot tell you whether the input types were correct, whether duplicates were resolved properly or whether the inclusion rule matched the business question. The useful evidence is the set of contributing rows and the rule that selected them.
This guide has two connected parts: an Excel formula exercise and an executed JavaScript experiment using synthetic invoices. The experiment records what our two small programs did; it does not measure Excel or an AI model. Keep that boundary in mind when interpreting the results. An assistant can help explain the formula, while the calculation and its checks remain inspectable outside the chat.
Five invoices and one precise question
Create an Excel table named Invoices using the columns below. Enter Amount as numeric Canadian-dollar values. The question is the total for rows whose Province is ON and whose Status is Paid. Refunded rows do not belong in this particular measure, even though their amounts could matter to a different accounting question.
Scroll the table sideways to see every column.
| Invoice | Province | Status | Amount in CAD |
|---|---|---|---|
| A01 | ON | Paid | 120 |
| A02 | ON | Pending | 80 |
| A03 | BC | Paid | 200 |
| A04 | ON | Paid | 50 |
| A05 | ON | Refunded | -20 |
The formula needs an explanation you can disprove
A01 and A04 contribute, giving 120 + 50 = 170. A02 fails the status condition; A03 fails the province condition; A05 fails the status condition. Ask the assistant to identify these rows as well as supply the formula. If its explanation includes A05, it has changed the question even if a later number looks plausible.
Microsoft documents SUMIFS as a conditional sum with multiple criteria. Structured references use the table and column names. Regional settings may require semicolons instead of the commas shown here. Run the formula in your workbook and inspect the referenced columns rather than copying an answer from chat.
=SUMIFS(Invoices[Amount],Invoices[Province],"ON",Invoices[Status],"Paid")What our eight-case run actually found
We executed the downloadable JavaScript runner on September 21, 2026. Its baseline selects exact ON/Paid labels and sums amounts with numeric conversion. The checked version first enforces the fixture’s data contract. Raw outputs, input data and code appear in the experiment record below.
The baseline matched four of eight expected decisions; the checked version matched all eight. Duplicate A01 produced CAD 290 in the baseline. A trailing space after Paid excluded A01 and produced CAD 50. A numeric text value still produced CAD 170, but failed the contract because the type had changed. This last case is the clearest reason to inspect more than the total.
The contract also permits zero and sends a negative Paid amount for review. That is our rule for this teaching dataset, not a universal accounting rule. A credit note or adjustment might legitimately be negative in another system. The data owner must define that treatment before a cleaning routine changes it. Four further cases specified on September 23 matched their expected decisions: a valid one-row amount, a missing amount, a province outside this fixture's vocabulary and a total beyond the safe integer range. The extra probes still do not validate Excel or a real accounting workflow.
Use a discrepancy sheet before changing source data
Preserve the original workbook. Put suspected problems in a separate review sheet with row identifier, original value, proposed change and authorizing rule. A duplicate invoice ID could mean an error, an instalment or line-item data. Deleting the second row without asking which interpretation applies can make the total less accurate.
compare AI answers against a fixed rubricScore the explanation and its supporting evidence as well as checking the arithmetic.
Scroll the table sideways to see every column.
| Finding | Do not silently do this | Resolve this question |
|---|---|---|
| Repeated invoice ID | Delete a row | What combination of fields defines a unique record? |
| Paid with a trailing space | Normalize every label | Is whitespace normalization permitted and logged? |
| Amount stored as text | Convert all values | Are separators, units and currencies unambiguous? |
| Negative Paid amount | Change it to positive | Is this a credit, correction or invalid input? |
Rerun, extend, and keep the limitation visible
Download the original experiment files and the additional probes below; keep the original results before rerunning either script. The JSON uses integer cents, so 17000 represents CAD 170. The runner and input hashes let you check which files produced the recorded result. Write an expected decision before executing a new fixture.
Add a mixed-currency case or an unknown status to see where your real contract needs more work. Our eight cases were designed alongside the rules; passing them is not independent validation. Keep separate evidence for formula correctness, input validity and the business definition of the measure. None of those alone validates an entire financial report.
Inspect and rerun the experiment
First executed September 21 and rerun September 23, 2026. These are synthetic fixtures and actual outputs from a small JavaScript program. The downloadable results file contains the September 23 source hash. No commercial AI model or spreadsheet application was tested. The incomplete baseline is a teaching comparison, not a product benchmark.
| Case | Expected | Baseline | With checks |
|---|---|---|---|
| original | 17000 | 17000 | 17000 |
| new paid row | 20000 | 20000 | 20000 |
| new pending row | 17000 | 17000 | 17000 |
| duplicate id | REVIEW | 29000 | REVIEW |
| trailing space | REVIEW | 5000 | REVIEW |
| numeric text | REVIEW | 17000 | REVIEW |
| zero paid amount | 5000 | 5000 | 5000 |
| negative paid amount | REVIEW | -7000 | REVIEW |
The baseline matched 4 of 8 specified outcomes; the checked version matched 8. The rules were built for these cases, so these counts cannot establish real-world reliability.
Additional probes, September 23
After the original run, we specified more synthetic cases before running them against the unchanged rules. These investigator-designed probes are not a representative held-out sample. Failures remain in the record.
| Case | Expected | Observed | Result |
|---|---|---|---|
| single valid row | 125 | 125 | Matched |
| missing amount | REVIEW | REVIEW | Matched |
| unsupported province | REVIEW | REVIEW | Matched |
| unsafe total | REVIEW | REVIEW | Matched |
4 of 4 additional probes matched the independently written expected decisions. The retrieval selector does not check document text against metadata, missing text, amount types or malformed document dates. Neither suite tested an AI model or real permission system.
- Download the input data (JSON)
- Download the executable source (JavaScript)
- Download the recorded results (JSON)
- Read the method, limits and rerun instructions
- Download the additional cases and expected decisions
- Download the probe runner
- Download all observed probe results
Put all downloaded files in one folder. With Node.js 22 or later, run node run.mjs for the original cases and node probe.mjs for the extra cases. Neither requires a package, account or network connection. Each overwrites its own results file; preserve the published copy before rerunning. The results include input and source hashes.
Continue with the original sources
These claim-relevant primary and first-party references support the reporting above. Open them for technical detail, current requirements and subsequent updates.
- support.microsoft.comMicrosoft: SUMIFS function ↗Function syntax and multiple criteria. The fictional invoice exercise uses this syntax.
- support.microsoft.comMicrosoft: Structured references with Excel tables ↗Explains references to named table columns and how those references adjust as table data changes.
- nist.govNIST: Generative AI Risk Management Profile ↗Risk-management background, including confabulation and information integrity. It does not certify the examples or prescribe our scoring thresholds.
Finished reading? Save that here without waiting for a timer.
September 23: added four synthetic input probes and preserved their outputs alongside the original experiment.
See something we should fix or clarify? Read the corrections policy or tell the newsroom. Material changes are noted here.
