Skip to content

AI uses for records

One formula, two verdicts in a finance spreadsheet period error dataset

By SourceX Editorial · Reviewed by Noah Loul ·

10 sections

Short answer

A finance spreadsheet period error dataset has to store, for every labeled cell, the period the cell is meant to measure and the record that says so. Formula text cannot carry that intent: in a recalculated 14-cell test, identical formulas were wrong in one row and correct in another, and the checks that caught every wrong-period cell without an intent label also flagged all five deliberate cross-period references.

Agent run

One formula

This page covers one formula, two verdicts in a finance spreadsheet period error dataset.Illustrative

Key takeaways

  • A wrong-period formula computes correctly on the wrong rows, so syntax checks and error scans pass it.
  • Identical formula text can be wrong or correct depending on the period its row is meant to report.
  • In a constructed 14-cell test, no cell returned an error value, and header-only checks flagged all five correct cross-period cells.
  • With a written intent, a period footprint and a control total separated the eight wrong cells from the correct ones.
  • Label the intended period, the observed footprint and the verdict separately, and keep undetermined cells when no intent record exists.

What separates a period error from a broken formula?

A period error is a formula that calculates without fault on rows from a period other than the one it claims to measure. Nothing in the syntax is broken. The workbook opens, the cell returns a number of the right size, and the only thing wrong is the relationship between that number and the label above it.

Accounting already draws this line. FASB Statement 154 defines an error in previously issued financial statements as one arising from mathematical mistakes, mistakes in applying GAAP, or oversight or misuse of facts. A wrong-period formula belongs to the last group: the arithmetic is right and the facts it was pointed at are wrong. A dataset that grades only whether a formula computes will score these cells as correct.

Spreadsheet error research explains why they survive review. In his revision of the Panko-Halverson taxonomy, Raymond Panko writes that "Mechanical errors are typing errors, pointing errors, and other simple slips" and that they have a high chance of being caught by the person who made them, while logic errors come from choosing the wrong algorithm or writing the wrong formula for it. A reference copied one column too far is a pointing slip; a fiscal-year formula built on calendar months is a logic error. Both leave a plausible number behind, and a finance spreadsheet period error dataset should record which kind each case is, because a model has to learn different things from each.

The decision this page supports is narrow: what evidence a labeled example needs before it can teach or test whether a formula reads the intended period. The short answer is that the intended period has to be written down outside the formula, because, as the test below shows, identical formula text can be wrong in one row and correct in the next.

Casebook: eight wrong-period cells and two look-alikes that are correct

Each wrong-period case below was built into one test workbook and recalculated in LibreOffice Calc; the returned values are from that run. Every cell is meant to report expense activity for September 2026 unless its row says otherwise, and September's correct total in the test ledger is 3,755.15. The last two rows use formula text identical to two wrong cases and are correct, because their rows ask for a different period.

Two mechanisms deserve a note. W3 fails because Microsoft documents that when match_type is 0, MATCH finds the first value exactly equal to the lookup value, and the header row lists last September before this one. W7 and W8 return the right months for a different question, which is why the row label, not the formula, decides the verdict.

Casebook: eight wrong-period cells and two look-alikes that are correct
Case and mechanismFormula patternReturned in the testEvidence that settles the label
W1: locked start-date anchor copied from the August columnSUMIFS with the start date fixed on August's settings cell3,463.25, August's totalFootprint shows August only; formula text names August's cell; version history shows the copied column
W2: date literals left in last year's templateSUMIFS between DATE(2025,9,1) and DATE(2025,10,1)3,128.05, September 2025Footprint shows September 2025; template history; row intent names 2026
W3: month label matched without its yearINDEX with exact MATCH on the text Sep3,128.05, the first Sep columnHeader row holds two Sep labels; exact MATCH stops at the first
W4: fixed range sized at a mid-month preliminary closeSUMIFS over ledger rows 2 to 95 only1,534.58, four of ten September rowsFootprint looks right; control total and appended boundary rows expose the gap
W5: period index input left at AugustStart date chosen by an index cell still set to 23,463.25, August's totalSettings cell value and its edit date; close checklist step that should update it
W6: cross-sheet reference left on the prior month's tabPoints at the August trial balance tab3,463.25, August's totalReference text names the August tab; tab naming convention
W7: calendar year to date where fiscal year to date was intendedSum from January 1 to September 3031,787.95, nine monthsFiscal calendar starting July 1; footprint January to September
W8: year-to-date cell placed in a one-month rowPoints at the September fiscal year-to-date cell10,889.75, three monthsRow intent asks for one month; footprint July to September
L2, correct: prior-year comparativeSame text as W23,128.05Row intent names September 2025
L5, correct: fiscal year to dateSame text as W810,889.75Row intent names fiscal year to date from July 1

Five deliberate references to another period, and how a label tells them apart

A reference to another period is often exactly what the business asked for, so a dataset that treats every cross-period reference as an error teaches a model to break good workbooks. The test included five such cells: an opening position carried from the August close, a prior-year comparative, a trailing three-month average, a change from August to September, and fiscal year to date.

What separates them from the wrong cases is a written statement of intent. In the test it sat in the row label, for example "Prior-year comparative, September 2025". In real close workbooks it can live in a column header, a named range, the close calendar, the fiscal calendar, a reporting policy or a reviewer's note. A label is only as good as that record, so each labeled cell should cite where its intent came from and when that record was dated.

Where no such record exists, the honest label is undetermined. A cell headed September that sums July to September may be a mistake or an unlabeled year-to-date figure, and a reviewer guessing between the two creates exactly the noise the dataset exists to remove.

What six checks caught in a recalculated 14-cell test

No check that reads only the formula output and the column header caught every wrong-period cell without also flagging deliberate ones. The test workbook held 14 graded cells: eight wrong-period cells, one correct September reference and five correct cross-period cells. Every cell was recalculated in LibreOffice Calc 24.2.7.2 from a file with no stored results, then graded six ways.

Not one cell returned an error value. Comparing each cell with August's total caught the three cases that reused August (W1, W5 and W6) and nothing else. Boundary rows read against the September header flagged all eight wrong cells and all five correct cross-period cells, and the period footprint read against the header flagged seven wrong cells and the same five correct ones. Once the written intent was supplied, the footprint flagged seven of eight wrong cells and no correct cell, and a control total recomputed for the intended period flagged all eight and no correct cell.

These counts follow from how the 14 cells were built, so they describe how each check behaves on known mechanisms, not how often period errors occur or how well any tool performs. The pattern is the finding: the checks that catch wrong periods without an intent label also flag correct cross-period work, and the intent label removes those false alarms. The table lists each check:

What six checks caught in a recalculated 14-cell test
CheckNeeds the intended period?Wrong-period cells flagged (of 8)Correct cross-period cells flagged (of 5)
C1: error value scanNo00
C2: equals the August totalNo30
C3: boundary rows against the column headerNo85
C4: period footprint against the column headerNo75
C5: period footprint against the written intentYes70
C6: control total against the written intentYes80
Paired bars for six checks showing how many of eight wrong-period cells and how many of five correct cross-period cells each flagged in the constructed test; header-only checks flag the correct cells too, intent-based checks do not.
Constructed 14-cell workbook recalculated in LibreOffice Calc 24.2.7.2. Counts show check behavior on built-in mechanisms, not error rates.

Why the period footprint probe still misses a short range

The footprint probe answers which months a cell reads, not whether it reads all of them. It works by recalculating masked copies of the workbook: in each copy every ledger amount outside one month is set to zero, and a month joins the cell's footprint when the cell is nonzero in that month's copy. Eleven masked copies, one per month in the ledger, produce a footprint for every cell without parsing a single formula.

That independence from formula text is its strength. W3's INDEX and MATCH, W6's cross-sheet link and W5's index input all reduce to the same evidence: August or September 2025 in the footprint where September 2026 was intended. A grader does not need a parser for every function a workbook might use.

W4 shows the limit. Its fixed range stops at the fourth September row, so it reads September and only September; the footprint is correct and the total is low. Two further records catch it: a control total for the intended period computed outside the workbook, and boundary rows appended below the existing data. The test planted twelve rows, one on each side of six period edges, with amounts of 1, 2, 4 and so on up to 2,048 hundredths, so every combination of landed rows produced a different total. W4's total did not move when the rows were appended, which is how a range that was never extended reveals itself.

One practical warning for anyone repeating the probe: the LibreOffice help pages describe a Recalculation on File Load setting for Excel 2007 and newer files with three choices, never recalculate, always recalculate and prompt the user. A grader that opens workbooks with stored results must force recalculation and record that it did; the test sidestepped the question by writing files with no stored results.

Reviewer rubric: the fields and verdicts each labeled cell needs

Label every cell on three separate questions: which period it was meant to measure, which period it actually reads, and whether the two match. The fields below are the minimum record for one labeled cell; the verdict at the end is chosen from four values, and the reviewer who chooses it signs the record.

  • Cell identity: workbook version, sheet, address and the formula text as saved.
  • Written intent: the period the cell claims to measure, stated as dates, plus the record it came from (header, row label, named range, close calendar, fiscal calendar, policy or reviewer note) and that record's date.
  • Observed footprint: the months the cell reads, from masked recalculation, and the calculation engine and version used.
  • Control total: the intended metric recomputed from the ledger extract outside the workbook, with the difference from the cell.
  • Boundary result: which planted rows landed in the cell and which should have.
  • Mechanism code and kind: one of the casebook mechanisms or a new one, marked as a slip or a logic error.
  • Effect: the periods misstated, the amount misstated in each, and whether the cell feeds a reported figure.
  • Verdict: wrong period, correct same period, correct deliberate cross-period, or undetermined because no intent record exists.
  • Review: the reviewer's accounting qualification, the date, and any disagreement with an automated result kept beside the final verdict.

How severe is a reference to the wrong period?

A wrong-period reference usually misstates two periods, not one, so the severity field should measure both. When September's cell shows August's total, August is counted twice across the summary and September's activity is counted nowhere, so any year-to-date figure built from the monthly columns is wrong as well.

The SEC staff faced a similar choice for financial statement misstatements in Staff Accounting Bulletin 108. It describes a rollover approach that sizes an error by the amount originating in the current year's income statement and an iron curtain approach that sizes the misstatement in the year-end balance sheet, and the staff asks registrants to quantify errors under both and evaluate each. SAB 108 governs financial statements, not workbooks; borrowed as an analogy, it suggests the dataset record both the period-flow effect and the effect on any balance the cell feeds.

Severity then depends on reach. A wrong cell in a scratch tab that nothing references is low. The same cell feeding a variance column or a board pack is high, and highest when the result looks normal, as every case in the test did. Record the reach as a list of dependent cells rather than a single score, so a buyer can set its own threshold.

Does a correct period make the cell correct?

No. A cell can read exactly the intended period and still be wrong, because the period label answers only one question. Whether an invoice belongs in September at all is a cutoff and accrual question for the ledger, decided by the company's close policy. Whether the person who changed the formula was allowed to change a reported figure is an authorization question. Neither is visible from the workbook.

Keep those questions in separate fields, judged by people qualified to judge them. A qualified accountant should confirm the intent records and any treatment notes; the period label itself can often be produced by the footprint and control-total checks once intent is written down. A dataset that folds all three into one correct or incorrect flag will teach a model that a matching period means a sound number.

The same caution applies to the test above. It shows how to establish which period a formula reads. It does not show that the September ledger rows were posted to the right month, which is a separate dataset question about expense timing.

Sourcing real period errors with their intent records

Real cases come from close and reporting workbooks kept with their surrounding records: saved versions, the ledger extract each version summarized, the close and fiscal calendars, and reviewer notes that state what each schedule was supposed to show. Those records let a reviewer write intent from evidence rather than from memory, and saved versions show both the wrong reference and the correction a person made.

Packets have to be authorized and de-identified before anyone labels them. Account names can usually stay; customer, vendor and employee names in the ledger lines should be replaced with stable pseudonyms so control totals still reconcile. Keep cells labeled undetermined in the set: they show a model when to ask for the intent record instead of guessing.

A team that needs such packets can describe them to SourceX, which looks for datasets at U.S. businesses once a buyer request arrives rather than selling from stock; the request names scope, volume, language, format, timing and licensing terms. A useful request names the acceptance test as well, for example that every cell labeled wrong period arrives with its intent record, footprint and control total, so the buyer can recompute each verdict. On the supplier side, the company signs off on scope and terms before any records leave it.

What the constructed test does not establish

The workbook is constructed: one expense account, eleven months of ten rows each (Sep 2025 and Jan to Oct 2026, dated days 1, 4, 7 and so on to 28) with amounts in cents of 10,000 + ((7,919m + 104,729r) mod 50,000) for month position m (0 to 10) and row r (0 to 9), 14 graded cells written to cover known mechanisms. Its counts show how six checks behave on those cells. They are not error rates, detection rates or evidence about any company's workbooks, and a different mix of cells would produce different counts.

Other limits matter for anyone reusing the method. LibreOffice and Excel can differ on some functions and date handling; Microsoft notes that Excel stores dates as sequential serial numbers, which is why a period boundary inside a formula is just a number compared with other numbers, and an engine that handles serial dates differently would need its own boundary rows. Masked recalculation assumes ledger amounts do not cancel to zero within a month, which holds here because every amount is positive.

The next evidence needed is real: version-controlled close workbooks with written intent records, labeled by qualified accountants, with agreement measured between two reviewers. The script that built and graded the test, period_probe.py, records its versions (openpyxl 3.1.5, LibreOffice 24.2.7.2) and needs no random numbers.

Frequently asked questions

What is a wrong-period reference in a finance spreadsheet?

It is a formula that calculates correctly but reads rows from a period other than the one its label claims, such as August's total under a September heading. The workbook shows no error value. The mistake is only visible when the cell's sources are compared with the period it was meant to measure.

Can Excel error checking or a formula linter find these errors?

Usually not on their own, because the formula is valid and returns a plausible number. Checks that trace which months a cell reads help, but without a written record of the intended period they also flag correct comparatives, opening balances and year-to-date figures. The intended period has to be part of the data.

Why include correct cross-period formulas in a period error dataset?

Because many finance schedules reference other periods on purpose: opening balances, prior-year comparatives, trailing averages and year-to-date totals. Without them, a model learns that any reference to another period is a mistake. Pairs with identical formula text and different verdicts are the most instructive items.

Is a spreadsheet period error the same as a cutoff error?

No. A cutoff error posts a transaction to the wrong period in the ledger itself. A spreadsheet period error reads correctly posted ledger rows from the wrong period. A dataset should label the two separately, since one is fixed in the books and the other in the workbook.

Who should label the intended period of a cell?

A reviewer with accounting knowledge of the company's close, working from dated records such as the close calendar, fiscal calendar, headers and reviewer notes. Footprint and control-total checks can then produce the verdict mechanically. Where no intent record exists, the label should say undetermined rather than guess.

Sources

  • FASB Statement 154 defines an error in previously issued financial statements as resulting from mathematical mistakes, mistakes in applying GAAP, or oversight or misuse of facts. Source
  • Panko's revision of the Panko-Halverson taxonomy describes mechanical errors as typing, pointing and other simple slips that are often caught, and logic errors as wrong algorithms or wrong formulas. Source
  • Microsoft's documentation states that with match_type 0, MATCH finds the first value exactly equal to the lookup value. Source
  • Microsoft's documentation notes that Excel stores dates as sequential serial numbers so they can be used in calculations. Source
  • LibreOffice help describes a Recalculation on File Load setting for Excel 2007 and newer files with never, always and prompt choices. Source
  • SEC Staff Accounting Bulletin 108 describes rollover and iron curtain approaches to quantifying misstatements and asks registrants to quantify errors under both. Source

Tell us what data you need

Share scope, volume, format, timing and licensing needs. Datasets are sourced on request.

See if your company qualifies