# AI spreadsheet anomaly detection — six fictional reproduction cases

Version 1.0 · September 4, 2026 · OpenMax content team

These are stipulated teaching inputs, not customer data. This Markdown file is a reproduction guide, not an XLSX workbook. Work only in a new, disposable workbook. Do not paste the deliberately incomplete formula into an operational report. Formula names below use English Excel notation; your application may localize function names or argument separators.

The expected arithmetic can be checked independently. Hidden-row behavior in case B follows Microsoft's cited documentation; it is not a claim that this packet was executed in desktop Excel. Record your actual application, version and observations if you reproduce it. No macros, scripts or external-data refresh are required.

## A. A valid SUM formula omits the final record

Create sheet `A_Range`. Populate these exact cells:

| Cell | Value or formula | Meaning |
|---|---|---|
| A1 | Job | Column label |
| B1 | Units | Column label |
| A2 | J1 | First job |
| B2 | 120 | Units |
| A3 | J2 | Second job |
| B3 | 80 | Units |
| A4 | J3 | Third job |
| B4 | 150 | Units |
| A5 | J4 | Fourth job |
| B5 | 50 | Units |
| A7 | Reported total — intentionally incomplete | Teaching label |
| B7 | `=SUM(B2:B4)` | Intentionally excludes J4 |
| A8 | Expected total | Teaching label |
| B8 | `=SUM(B2:B5)` | Complete four-job population |
| A9 | Omitted units | Teaching label |
| B9 | `=B8-B7` | Difference |

Rule A1: all four jobs belong to the report. There are no exclusions, hidden rows, filters or adjustments in case A. Expected results: B7=350; B8=400; B9=50. The omission is 50/400=12.5% of the complete total. The proposed correction is to include B5 in B7's range, subject to the owner confirming the stated population. No error code is needed for this defect to exist.

## B. Manually hidden rows versus an intended visible population

Create a separate sheet `B_Hidden`, with A1:B5 identical to case A. Do not copy B7:B9. Enter B7=`=SUBTOTAL(9,B2:B5)` and B8=`=SUBTOTAL(109,B2:B5)`.

1. Start with all rows visible and no filter applied. Both expected totals are 400.
2. Manually hide worksheet row 4, which contains 150. Do not use a filter for this step. Expected B7=400, B8=250.
3. Unhide row 4. Both expected totals return to 400.
4. As a separate experiment, apply a filter to A1:B5 that excludes J3, with no manually hidden rows. Both expected totals are 250.
5. Remove the filter and restore the sheet before another experiment.

Record each state separately. A visible-jobs report and an all-jobs report have different populations. Neither expected number is inherently an error without that requirement. Do not use the numbers from a filtered state to describe the manually hidden state.

Source: [Microsoft SUBTOTAL reference](https://support.microsoft.com/en-us/excel/functions/subtotal-function). This short case concerns vertical ranges and ordinary source values, not nested subtotals.

## C. Two constants, different dispositions

Create sheet `C_Overrides` with the following layout:

| Row | A: Job | B: Quantity | C: Standard minutes per unit | D: Standard total formula | E: Recorded total | F: Exception evidence |
|---|---|---:|---:|---|---|---|
| 2 | J1 | 4 | 10 | `=B2*C2` | `=D2` | None needed |
| 3 | J2 | 3 | 10 | `=B3*C3` | 24 (constant) | Exception EX-01 |
| 4 | J3 | 2 | 10 | `=B4*C4` | 25 (constant) | No exception |

Put column labels in row 1. Expected standard totals D2:D4 are 40,30,20; recorded E2:E4 are 40,24,25. Standard sum90; recorded sum89.

**Stipulated approval record EX-01:** the fictional operations owner approves a six-minute service credit for J2 in this report only. Therefore the allowed total for J2 is 30−6=24. No approval or alternative rule exists for J3 in the supplied packet. Its expected total remains20, while recorded25 is an unsupported positive-five-minute difference.

Review E3 and E4 separately. E3 is a valid documented exception under these stipulated facts. E4 is a mismatch with the supplied rule; do not invent a similar credit or overrun. The bridge is 90−6+5=89. A total difference of −1 does not justify closing the +5 finding. This is an educational approval condition, not an authentic signature or customer authorization.

## D. A join with the same row count but missing and repeated jobs

Create sheet `D_Jobs`: headers A1=`Job`, B1=`Units`; data A2:B4 are A/10, B/20, C/30. Create `D_Reference`: headers A1=`Job`, B1=`Owner`; data A2:B4 are A/Team1, A/Team2, B/Team3.

The intended relationship is many jobs to one authoritative owner per job; this particular fixture has one source row per job. The reference table violates that expectation: A has two owner rows, and C has none. No rule in this packet selects Team1 over Team2.

An ordinary inner join on `Job` with both matches retained yields these three rows:

| Job | Units | Owner |
|---|---:|---|
| A | 10 | Team1 |
| A | 10 | Team2 |
| B | 20 | Team3 |

Source count3 and joined count3 happen to agree. Source sum60; joined sum40. The explanation is +10 from repeated A and −30 from missing C, giving a net−20. Required disposition: retain both findings; obtain the authoritative A mapping and C mapping; repeat the join. Deduplicating the output is not a supported fix and cannot restore C.

## E. A unit conversion must be supplied, not guessed

Create sheet `E_Units`: A1=`Item`, B1=`Quantity`, C1=`Unit`, D1=`Individual units per source unit`. Row2: Part-X/12/box/10. Row3: Part-X/18/individual unit/1.

The fixture explicitly defines one Part-X box as ten individual units. Normalized values are12×10=120 and18×1=18; total138 individual units. The raw numeric sum12+18=30 is not a meaningful combined physical quantity.

To reproduce the normalization, put E1=`Normalized units`, E2=`=B2*D2`, E3=`=B3*D3`, E5=`=SUM(E2:E3)`. Preserve B:D. This conversion applies only to the stated product and version; an absent or conflicting conversion in real data remains unresolved.

## F. A large weekly value is not a daily observation

Create `F_Periods` with columns A=`Record`, B=`Observed units`, C=`Covered days`, D=`Observation class`. Rows2–6:

| Record | Observed units | Covered days | Observation class |
|---|---:|---:|---|
| D1 | 120 | 1 | Single-day total |
| D2 | 80 | 1 | Single-day total |
| D3 | 150 | 1 | Single-day total |
| D4 | 50 | 1 | Single-day total |
| W1 | 1400 | 7 | Separate seven-day total |

W1 is stipulated to be a separate record, not the sum of D1:D4. Dividing1400 by7 gives an arithmetic daily average of200. Do not add W1 to D1:D4 to create a meaningful operational total: the packet supplies no common population or coverage relationship for doing so.

This tiny fixture lacks comparable team, workload and service conditions. It supports a period-mismatch warning, not a statistical claim that W1 is erroneous or that200 per day is normal. A real outlier review needs a defined baseline and source context.

## Review record to return

For each case, retain the case ID, application/version if used, input cell/range, observed output, expected rule, evidence source, finding state, owner and retest result. Distinguish arithmetic checked outside a spreadsheet from workbook behavior actually reproduced in that application. Do not convert this teaching exercise into a claim about detector accuracy or OpenMax feature availability.