R023 — Aggregation Range Gap

Rule-Reference • — • 5 min read

Audience: Technical Reviewers • Auditors • Model Developers
Last Reviewed: July 2026
Updated: Version 1.0

Executive Summary

R023 (Aggregation Range Gap) flags a formula cell that sits immediately adjacent to an aggregation formula's range — one row above or below a SUM, SUMPRODUCT, or SUBTOTAL range — but is not itself included in that range. The adjacent cell may represent a line item that was silently excluded from the total. R023 is the sole member of its own taxonomy category (Aggregation Logic) and, despite the category's narrow scope, is critical severity and one of five critical-override rules under the SM-2.0 scoring methodology.

Key Takeaways

✓ R023 flags a formula-bearing cell sitting immediately adjacent to, but outside, an aggregation range — one row above or below a SUM, SUMPRODUCT, or SUBTOTAL formula's range.
✓ It is the only rule in its taxonomy category (Aggregation Logic) but is critical severity and one of five critical-override rules — a small category is not a signal of low materiality.
✓ The rule suppresses an adjacent subtotal cell that is itself an aggregation, since a subtotal sitting next to another aggregation range is an expected, legitimate pattern rather than a gap.
✓ A triggered finding does not assert the excluded cell should definitely be in the total — it asks a reviewer to confirm whether the adjacent cell represents a line item that belongs in the aggregation.

Purpose¶

R023 detects a formula-bearing cell that sits immediately adjacent to — one row above or one row below — the range referenced by an aggregation formula (a SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL, or AGGREGATE formula) elsewhere on the same sheet, where the adjacent cell is not itself included in that range. The adjacent cell may represent a line item that was never brought into the total it should logically belong to.

Detection Logic¶

For every aggregation formula referencing a single-column range, R023 checks the cell immediately above the range's start and the cell immediately below the range's end. If either adjacent position holds a formula cell, it is a candidate gap — unless that adjacent cell is itself an aggregation formula, in which case it is suppressed: an aggregation formula sitting next to another aggregation range is the expected shape of a cascading subtotal structure (a subtotal formula immediately below a range of subtotals, for example), not a gap. The rule also suppresses a candidate gap that is already captured indirectly — where the adjacent cell falls inside the range of an inner subtotal that the outer aggregation range already includes.

This is a conceptual description of what the rule looks for; the specific geometry and suppression logic are implemented against the workbook's actual formula structure and are not disclosed at the level of exact internal thresholds.

Why It Matters¶

Financial models change over time — a line item is added, a row is inserted, a cost category is split into two. When a new row lands immediately adjacent to an existing total's range but the total formula's range is not updated to include it, the total silently continues to reflect only what it summed before the change. Nothing about the model visibly flags this: the total still calculates, still shows a plausible number, and gives no indication that a line item sitting directly next to it was left out. Because this failure mode directly understates or overstates a headline total with no visible symptom, R023 is one of five rules capable of capping a model's overall letter grade under the SM-2.0 Scoring Methodology, regardless of how high the model's numeric score otherwise is — despite Aggregation Logic being the narrowest of the six FMAE rule categories, with R023 as its only member.

Evidence Produced¶

A triggered R023 finding's evidence identifies the specific sheet and cell address of the adjacent formula-bearing cell, its formula text, and a description naming the aggregation formula and range it sits next to but is excluded from, together with whether the gap sits above or below that range — so the finding is traceable to exactly which total the adjacent cell may be missing from. See the Finding Data Model & Confidence page for the full evidence and provenance model this finding is built on.

Severity¶

Critical. Weight 10.0. R023 is one of five critical-override rules under SM-2.0 (alongside R001, R002, R004, and R006): if it triggers even once, the model's overall letter grade is capped below A or B regardless of the numeric score the weighted formula produces.

Example¶

Trigger. A cost schedule sums rows 5 through 10 with =SUM(B5:B10) in cell B11. A new cost line is later inserted in row 11, immediately below the total, containing its own formula rather than a typed value — but the original SUM range was never extended to include it. R023 flags cell B11's adjacent row: a formula-bearing cell sits directly below the aggregation range and is not included in it.

Non-trigger. The same schedule, but the row immediately below the SUM(B5:B10) range is itself another SUBTOTAL or SUM formula rolling up a separate section — an expected cascading-subtotal structure. R023 suppresses this case rather than flagging it, since an aggregation formula adjacent to another aggregation range is the expected shape of the pattern, not a gap.

Recommended Remediation¶

The excluded adjacent cell is reviewed to confirm whether it represents a line item that belongs in the total. If it does, the aggregation formula's range is extended to include it. If the adjacent cell is intentionally excluded — a memo line, a check formula, a value that should not be part of the total — this should be documented so the exclusion reads as deliberate rather than as an unresolved finding on subsequent review.

R023 is the sole member of the Aggregation Logic taxonomy category — it has no sibling rules by category. The five rules published so far in the Rule Reference are cross-linked here for context: R001, R002, R004, and R006 — all four in the Structural category, distinct from R023's own Aggregation Logic category. See the Rule Taxonomy page for the full classification.

No existing Knowledge Centre technical guide addresses aggregation-range gaps specifically as of this writing; this section will be updated with a link in the same edit that publishes one.

RP-003: Aggregation Logic Errors in Financial Models — a methodological account of this failure mode, grounded in this rule's detection logic.

Version History¶

No version-specific changes to R023's severity, weight, or category are recorded as of engine version 2.1.0. This section will be updated in the same edit as any future change to the rule's declared attributes.

How OXXON tests thisRun a free structural check with FMAE

Frequently Asked Questions

What does FMAE rule R023 check for?

A formula-bearing cell that sits immediately adjacent to — one row above or below — a SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL, or AGGREGATE formula's referenced range, but is not itself included in that range.

Why would an adjacent cell being excluded from a total matter?

If the adjacent cell represents a line item that was inserted after the total formula was written — a new cost category added below an existing SUM range, for example — the total will not include it, and nothing about the model visibly indicates the omission. The total simply understates or overstates what it should represent.

Does R023 flag every cell next to a SUM formula?

No. It specifically excludes adjacent cells that are themselves aggregation formulas — a subtotal formula sitting next to another aggregation range is an expected, legitimate pattern (a cascading subtotal structure), not a gap, and is suppressed from this rule's findings.

Is R023 the only rule in its taxonomy category?

Yes — Aggregation Logic is the narrowest of the six FMAE rule categories, with R023 as its sole member. Despite that narrow scope, R023 is critical severity and one of five critical-override rules, documented on the SM-2.0 Scoring Methodology page.

R001 — Hardcoded Cells

R001 (Hardcoded Cells) flags a formula cell that has been overridden by a typed numeric value, in a row where the surrounding cells otherwise share a clear, dominant formula pattern. It is critical severity, carries the highest weight of any FMAE rule (15.0), and is one of five critical-override rules under the SM-2.0 scoring methodology — meaning a single R001 finding caps the model's overall letter grade below A or B regardless of its numeric score.

R002 — Broken Links

R002 (Broken Links) flags a formula cell whose reference has broken — pointing at a deleted named range, a deleted worksheet, a reference-error cell, or an external workbook link the parser could not confirm resolves. It is critical severity and one of five critical-override rules under the SM-2.0 scoring methodology, meaning a single R002 finding caps the model's overall letter grade below A or B regardless of its numeric score.

R004 — Formula Inconsistency

R004 (Formula Inconsistency) flags a formula cell whose logic differs from the dominant pattern the rest of its row otherwise follows — an extra term, a different function, or a shifted reference. It is the general case of R001, catching a different formula rather than a typed constant, and deliberately excludes hardcoded cells from its own sample so the two rules never double-count the same cell. R004 is critical severity and one of five critical-override rules under the SM-2.0 scoring methodology.

R006 — Propagated Errors

R006 (Propagated Errors) flags any cell — formula or hardcoded — whose last calculated value is an Excel error (such as #REF!, #VALUE!, or #DIV/0!). Because an error value is a deterministic, unambiguous fact already present in the workbook, every R006 finding carries high confidence. R006 is critical severity and one of five critical-override rules under the SM-2.0 scoring methodology, meaning a single R006 finding caps the model's overall letter grade below A or B regardless of its numeric score.

FMAE Rule Taxonomy

Every rule in the FMAE structural rule pack declares a category attribute at the point it is defined in source — this is not a classification imposed on the rules afterward for documentation purposes, it is the classification the engine itself uses. Six categories cover all 26 rules — Structural (18 rules), Assumptions Governance (1), Integrity Controls (2), Structural Hygiene (1), Aggregation Logic (1), and Model Governance (3). This page publishes that taxonomy as the FMAE equivalent of a control catalog's classification scheme, cross-linked to the Rule Reference page for each member rule.

FMAE Scoring Engine — SM-2.0 Methodology

FMAE computes a model's risk score as 100 minus the triggered rules' combined weight, normalized against a fixed basis, currently 207.0 under the active SM-2.0 methodology. SM-1.0, the engine's original scoring basis (170.0, covering R001–R021), is retained as a frozen historical reference rather than deleted from the record. Five rules — R001, R002, R004, R006, and R023 — are critical-override rules. If any of them triggers, the resulting letter grade is capped so a model cannot be graded A or B regardless of how high its numeric score is. This page documents the exact formula, the versioning between SM-1.0 and SM-2.0, and a fully worked example.

Finding Data Model & Confidence

Every FMAE finding carries the same fixed structure — which rule triggered it and that rule's identity at the time it ran, its evidence, a narrative explanation, and a confidence label of high, medium, or low. Confidence is a deterministic categorical label derived from how strongly a row's cells agree on a pattern, not a machine-learning probability estimate. Stating this plainly matters, since describing FMAE's confidence as AI-derived or probabilistic would overstate what the engine actually does.