AI uses for records
Agent spreadsheet formula failure case: testing a wrong-period reference
By SourceX Editorial · Updated
10 sections
Short answer
Test it on the recalculated workbook, not on the agent's message or the file opening cleanly. Recalculate in a spreadsheet engine, trace each new cell's precedents back to the source rows, and check that every row feeding a period column falls inside that period. Plant boundary rows on the last and first days around each period and run several input variants so a formula that is right by accident still fails.
Agent run
Agent spreadsheet formula
Key takeaways
- A workbook that opens with no errors and a confident agent summary is not evidence the figures are right.
- Recalculate before grading; cached values in a saved file can lag the formulas.
- Trace each new cell to its source rows and check their dates against the period in the header.
- Planted rows on period edges turn a hidden period error into a specific, explainable difference.
- Grade outcomes, not formula text, and include counterexamples that should pass.
- Real close workbooks with edit history supply the variety illustrative cases lack.
How do you test an agent's spreadsheet formula for the wrong period?
Grade the recalculated outcome against a period rule, not the agent's report. An agent spreadsheet formula failure case usually looks like success: the workbook opens, no cell shows an error, and the final message states a number with confidence. The test has to prove that each new figure is built only from source rows dated inside the period it claims to cover.
In practice that means four checks. Recalculate the workbook in a real calculation engine. Trace every cell the agent created or changed back to its source rows. Compare the dates of those rows with the period in the column or row header. Then rerun the same instruction on input variants with rows planted on the period boundaries, so a formula that happens to produce a believable number on one file is caught on another.
The case below is illustrative. It is built to show where each piece of evidence comes from and which check catches the failure, so an evaluation team can copy its structure for real cases.
The starting state: a monthly close workbook with one new column to add
The illustrative workbook has four sheets and is saved in manual calculation mode. The agent's instruction is: add a September 2026 column to the accrued expenses summary, matching the existing months, and report the change from August. The table shows what a correct edit would touch.
| Sheet | What it holds | Cells that matter | What a correct edit changes |
|---|---|---|---|
| GL_Detail | One row per journal line: posting date, account, department, amount | Posting date column and account column | Nothing; it is the source |
| Settings | Period start dates in a header row, one per month | Period start cells for July, August and September | Nothing, if September's start date is already present |
| Accruals_Summary | One row per accrual account, one column per month | July and August columns with SUMIFS formulas over GL_Detail | A new September column whose formulas point to September's start date |
| Variance | Month-over-month change by account | Formula comparing the two latest month columns | Updated to compare September with August |
| Workbook settings | Saved in manual calculation mode | Workbook-level setting | No change, but results must be recalculated before grading |
Where the run went wrong, step by step
The agent made one plausible move that carried a reference forward unchanged. Every later step was built on it.
Checklist · 6 items
- Step 1: the agent reads Accruals_Summary and sees that each month column uses SUMIFS on GL_Detail, with posting date criteria running from a start-date cell to the end of that month.
- Step 2: it copies the August column one column to the right. The start-date reference in the August formulas was written with a dollar sign on both the column and the row, so it stays pointed at August's start date in Settings instead of moving to September's.
- Step 3: the new column fills with numbers. They are August's numbers, because the criteria still select August posting dates.
- Step 4: the agent updates the Variance sheet to compare the new column with August. The result is a change of zero on every account.
- Step 5: the agent reads results back with a Python library, gets cached values, sees no errors, and reports that September has been added and accruals were flat month over month.
- Step 6: the run ends with a well-formed workbook and a confident summary. Nothing in the transcript flags a problem.
0 of 6 ticked. Tick each item as you go.
Why did a wrong column look like a finished task?
Because three different signals said success and none of them measured the outcome. The file opened, so a file-validity check passed. No cell held an error value, so an error scan passed. The agent's summary was fluent and internally consistent, so a reviewer reading only the transcript would accept it.
Spreadsheet mechanics make this easy. Microsoft's documentation explains that when a formula is copied, relative references change and references locked with dollar signs stay fixed, which is exactly what kept the start date on August. Excel also has a manual calculation mode in which, as the XlCalculation documentation puts it, calculation is done when the user requests it, so values saved in the file can lag behind the formulas.
Reading tools add a second layer. The openpyxl library's data_only option returns, for a formula cell, the value stored the last time Excel read the sheet, and its documentation states that openpyxl never evaluates formulas. An agent or a grader that reads values this way sees whatever was cached, not what the formulas now produce. Here the stale and the fresh values happened to match, because the formula was wrong in a way that still returned August's real totals.
The flat variance should have been a clue. A month-over-month change of exactly zero on every account is rare in real accruals, and a grader that flags identical adjacent columns would have caught this case even before tracing references.
The artifact verification and period boundary case record
Record every failure case in the same structure so cases can be compared, deduplicated and reused as evaluation items. Each row names the evidence and the check that proves it.
At a glance
The artifact verification and period boundary case record
| Field | Value in this case | Evidence reference | Check that proves it |
|---|---|---|---|
| Case label | Illustrative, constructed for this guide | Case header | Label present before any other field |
| Instruction | Add September column and report change from August | Task prompt | Prompt stored verbatim with the case |
| Starting state | Workbook with July and August columns, manual calculation | Input file checksum | Checksum matches the file given to the agent |
| Failure step | Copied formulas kept an absolute start-date reference | Agent action log, step 2 | Formula text of new column shows August's start cell |
| Applicable rule | Each period column sums only rows dated inside that period | Task rubric | Rule written before the run |
| Resulting state | September column equals August column on every account | Recalculated output file | Cell-by-cell comparison of the two columns |
| Outcome verdict | Fail: wrong period, plausible output | Grader record | Boundary test and precedent trace both fail |
| Severity | High: a reported figure is wrong with no visible error | Severity scale | Error reaches the summary the user would act on |
| Recovery check | Agent fixes reference and variance shows real change | Rerun output | All boundary variants pass after the fix |
Which planted rows expose period boundary errors?
Rows dated exactly at the edges of each period. A formula that is right in the middle of the month can still be wrong at the edges, and a formula pointed at the wrong period is wrong everywhere, but only planted rows make either failure show up as a specific, explainable difference. Add these rows to copies of the source sheet, each with a distinctive account or amount pattern so you can see where it lands.
- Dates entered as text are a known trap: Microsoft's EoMonth documentation warns that problems can occur when dates are entered as text and that the function returns a #NUM! error for an invalid start date.
- If the business uses a fiscal calendar, plant rows on fiscal period edges as well, read from the workbook's own settings sheet, not from calendar months.
- Run the instruction on at least three input variants. SpreadsheetBench, a benchmark built from real spreadsheet questions, grades each instruction against several test-case spreadsheets for the same reason: one file can be passed by luck.
| Planted row | Posting date or value | Should land in | Bug it catches |
|---|---|---|---|
| Last day of prior month | August 31 | August only | Start date set one day early, or wrong period entirely |
| First day of the period | September 1 | September only | Start criterion using greater-than instead of greater-than-or-equal |
| Last day of the period | September 30 | September only | End date computed one day short |
| Late timestamp on the last day | September 30 at 23:59 | September only | End criterion that compares a datetime against midnight |
| First day of next month | October 1 | Neither column | End criterion using less-than-or-equal on the next month's start |
| Date stored as text | A text string that reads like September 15 | Flagged, not silently dropped | Criteria that skip text dates without warning |
Which linked records prove the outcome, not just the formula?
Four artifacts, linked by case ID. The recalculated output workbook shows the values the formulas actually produce. A precedent trace for each new cell, which Excel exposes through the Range.Precedents property, shows which cells feed it. The list of source rows behind each new cell, with their posting dates, ties the trace to the period rule. A control total, such as the count of GL_Detail rows dated in September, confirms that the right population was summed.
This is the same principle used in agent benchmarks that grade the end state. The tau-bench paper evaluates an agent by comparing the database state at the end of a conversation with an annotated goal state, rather than trusting what the agent said it did. For a workbook, the end state is the recalculated file and its dependency graph; the agent's message is only a claim about it.
Counterexamples: one that should pass and one that only looks wrong
A good case includes a run that should pass with different formula text. Suppose another agent builds the September column with SUMPRODUCT and its own date math instead of copying SUMIFS. The formula looks nothing like the reference answer, but every planted row lands correctly and the control totals match. It passes. Grading on formula text would fail it unfairly, which is why the verdict rests on the boundary test and the recalculated values.
Include a run that only looks wrong. Suppose the September column matches August on one account because that accrual is a fixed monthly amount. A rule that flags identical adjacent values would raise it, but the precedent trace shows September rows feeding the cell and the boundary rows land correctly. The flag is a prompt to check, not a verdict.
How should a grader score severity and recovery?
Score by what the error would do to the user's decision. A wrong period in a figure that feeds a reported summary, with no visible error, is high severity because a person would act on it. The same error in a scratch calculation the agent later overwrote is low. A formula that breaks loudly with an error value is medium: wrong, but hard to miss.
Recovery is a separate measure. Give the agent the grader's feedback, or a nudge such as the flat variance, and check whether it finds the locked reference, fixes every affected cell, updates the variance and passes all boundary variants. An agent that fixes the visible column but leaves the Variance sheet pointed at old cells has only partly recovered, and the case record should say so.
Keep the grader honest about what it did not check. If the run never recalculated the file, record the verdict as unverified rather than pass. If the boundary rows were added after the agent ran, store them as a separate variant so the original run is still reproducible. And when a finance reviewer overrules the automated verdict, for example because a cutoff rule lives in a policy document rather than the workbook, keep both verdicts and the reason, since those disagreements are often the most instructive cases in the set.
Sourcing real period-boundary failures for an evaluation set
Illustrative cases teach the structure; real ones carry the variety. The most useful source is close and reporting workbooks with version history, where a reviewer's correction to a period reference is visible as an edit between saved versions, together with the general ledger extract the workbook summarized. Those pairs show real mistakes and real fixes, including fiscal calendars and odd cutoffs that synthetic cases miss.
An evaluation team that wants such pairs can put the request to SourceX, which looks for them at U.S. businesses when a buyer asks instead of keeping an inventory. The request names the scope, volume, language, format, timing and licensing terms, for example two years of close workbooks with edit history plus the matching ledger extracts.
Frequently asked questions
Why not just compare the agent's formula with a reference formula?
Because many different formulas can produce the correct result, and a formula can match the reference text while pointing at the wrong cell. Grade the recalculated values and the source rows that feed each cell, and use formula text only to explain a failure.
Do I need Excel to recalculate the workbook for grading?
You need a calculation engine, not necessarily Excel, but you cannot rely on cached values. Libraries such as openpyxl read formulas or the values last stored in the file and do not evaluate formulas, so recalculate first and confirm the engine handles the functions the workbook uses.
What is a period boundary test?
It adds rows dated on the edges of each period, such as the last day of the prior month, the first and last day of the period, and the first day of the next. Each row should land in exactly one period. Where it lands shows whether the start and end criteria are right.
How many input variants should each spreadsheet task have?
At least three, so a formula that is right by accident on one file still fails on another. Vary the period lengths, the position of boundary rows and the presence of text dates. SpreadsheetBench uses several test-case spreadsheets per instruction for the same reason.
Is a zero month-over-month change a reliable failure signal?
It is a useful flag, not a verdict. Fixed recurring amounts can legitimately repeat. Treat identical adjacent columns as a prompt to trace precedents and run the boundary rows, and record the result either way.
Sources
- Microsoft explains that copying a formula changes relative references while dollar-sign references stay fixed. Source
- In Excel's manual calculation mode, calculation is done when the user requests it. Source
- openpyxl's data_only option returns the value stored the last time Excel read the sheet instead of the formula. Source
- openpyxl never evaluates formulas. Source
- Microsoft's EoMonth reference warns that dates entered as text cause problems and invalid start dates return #NUM!. Source
- Excel's Range.Precedents property returns all the precedents of a cell. Source
- SpreadsheetBench is built from real spreadsheet questions and grades each instruction on several test-case spreadsheets. Source
- tau-bench grades agents by comparing the final database state with an annotated goal state. Source
Tell us what data you need
Share scope, volume, format, timing and licensing needs. Datasets are sourced on request.