Skip to content

Home Guides Magic numbers

Rule R5 (magic numbers)

Magic numbers: the invisible assumptions hiding in your formulas

A model you cannot audit is a model you cannot trust. Hardcoded thresholds and multipliers hide assumptions and make scenario changes dangerous.

Install Free Back to the 5 rules
On this page

The clean fix (template)

Create an Assumptions section and reference cells instead of hardcoded numbers:

GrowthRate
Threshold
CommissionRate

Then reference those values in formulas (or use named ranges).

Examples of magic numbers

=Revenue * 1.05     // what is 1.05?
=IF(Sales > 50000, Amount * 0.15, Amount * 0.10)

These formulas work — but they hide business logic inside numeric literals.

Why it is risky

  • Assumptions are invisible: reviewers cannot tell why a constant exists.
  • Scenario changes are dangerous: “did we update all instances?” becomes a risk.
  • No single source of truth: the same constant gets duplicated across tabs.

Best practice: externalize assumptions

Move constants into labeled cells (or a dedicated “Assumptions” tab), then reference those cells everywhere.

  • Improves auditability and clarity
  • Makes scenario analysis safer
  • Reduces duplicated logic

How SheetSage flags this (R5)

SheetSage looks for high-signal numeric literals used as thresholds, multipliers/divisors, or structural index arguments. R5 is guidance-only (no auto-fix), because meaning is contextual.

FAQ

Which numbers count as “magic”?

The risky ones are thresholds and multipliers that represent business assumptions (rates, caps, bands), especially when repeated across a workbook.

How do I build an assumptions table that scales?

Create a single Assumptions tab with clear labels, units, and owners. Reference it everywhere; avoid duplicating constants.

Should I use named ranges?

Yes, when it improves readability and reduces the chance of referencing the wrong cell. Named ranges work well for shared assumptions and stable tables.