Spreadsheet errors: why nobody notices until they cost you
Spreadsheet errors go unnoticed because a spreadsheet has no review, no change trail and no separation between data and rules. Whoever typed the number checks what they saw, and the rest surfaces only when the figure becomes an invoice, a commission or a decision. It is not a lapse of attention, it is an architecture problem: the fix is to move the rule out of the cell.
How common are spreadsheet errors?
More common than routine suggests, and usually small per cell. Powell, Baker and Lawson audited real operational spreadsheets and found errors in 0.8% to 1.8% of formula cells, depending on how an error was classified (arXiv). In a follow-up study of 25 spreadsheets from five organizations, the same authors saw that many errors had negligible impact, but some affected critical parts of the calculation.
The number looks low until you do the volume math. A spreadsheet with 2,000 formula cells, across that range, would carry between 16 and 36 cells with errors. That is an illustrative application of the rate, not a measurement of any specific spreadsheet. The point is that every wrong cell is a chance for the final result to come out off, and nobody knows which one.
A second data point comes from a context unrelated to business, which is what makes it useful. Ziemann, Eren and El-Osta scanned the supplementary files of 3,597 papers from 18 genomics journals. In 704 of them (19.6%), gene names had been converted into dates by Excel, and the published list was wrong (Genome Biology). These were peer-reviewed papers written by trained researchers using the usual tool.
Why does nobody notice the error?
Because the spreadsheet shows the result and hides the rule. The rule lives inside a cell, formatted as a number, next to 1,999 others. If the value is plausible, nobody opens the formula.
There are three structural reasons.
The first is that data and rules occupy the same place. Someone changing an input value can overwrite a formula by accident, and the file accepts both the same way.
The second is that there is no trail. The spreadsheet does not record who changed the formula in column F, when, or why. When the result looks off, the history is not there to consult.
The third is copying. A spreadsheet that circulates by email or shared folder becomes several versions, each with its own adjustments. An error fixed in one stays alive in the others.
What does the JPMorgan case show about a spreadsheet that decides things?
The same mechanism, at bank scale. The internal report JPMorgan Chase published in January 2013 on the 2012 losses in its investment office describes a risk model that operated "through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another." One of the formulas divided by the sum instead of the average, as the modeler had intended, which cut the calculated volatility by about half and made the risk look smaller than it was (Society of Actuaries summary citing the report).
What matters is not the size of the bank. It is the combination: manual copying between files, rules inside cells and no independent review. Many companies with a commission, pricing or month-end spreadsheet have the same three ingredients, with a smaller loss and the same invisibility.
How does this show up at a distributor?
A hypothetical scenario, to make the mechanism concrete. The numbers below are illustrative, not a client measurement.
A distributor with 40 sales reps calculates monthly commission in a spreadsheet. There are six rules: a percentage per tier, a target bonus, a deduction for returns, and others. The sales base comes out of the ERP as a CSV, gets pasted into one tab, and a second tab applies the rules. In March someone adjusts the tier for one product and pastes the formula into only part of the column.
Nothing breaks. Commissions come out with plausible values, 3 reps get slightly more, 2 slightly less. The error surfaces only when one of them checks the math on their own, two months later, and the question becomes how many months are wrong and who is owed an adjustment.
At that point the problem is no longer about a formula, it is about architecture. The company cannot say which rule was in force in each month, because the spreadsheet kept only the last state.
When has a spreadsheet gone too far?
When its result turns into money or a decision and more than one person edits it. Three questions help.
Does anyone copy data from one file to another every month? If so, the copy is the point of failure.
Can you say, today, who changed a rule and when? If not, there is no way to investigate an error.
Does the result feed billing, payment or commission? If so, the error has a recipient.
Two bad answers already justify moving the rule out of the spreadsheet. That does not mean abandoning it: it stays good for analysis and exploration. What leaves is the calculation the company needs to be able to audit.
In practice the change has four parts. The rule lives in one place, versioned. Input is validated at the source instead of pasted. Every change is logged with author and date. And the calculation has a test: a set of cases with known results that runs on every change. The path to get there without stopping operations is in how to migrate off spreadsheets without stopping the business, and the criterion for when to make the switch is in when to replace spreadsheets with custom software.
In the Reatop case, Bumerangue tracked hospital waste in spreadsheets and paper notes, a method prone to error and consolidation delays. The system now has an integrated back office and a mobile app that works offline, so the record is created in one place.
Frequently asked questions
My team is used to spreadsheets. Will they accept something else?
They tend to when the first delivery fixes a pain they already feel, such as manual checking at month-end. Start with a single flow, the one with the greatest financial consequence, and keep the spreadsheet running in parallel for a short period with a cutover date. Teams start defending the system when they see the number close without the checking round.
How do I know the investment pays off, or that a return exists?
Measure the cost of the error and of the checking first. How many hours a month go to verifying and redoing, how many retroactive adjustments have happened, what the last one cost. If a spreadsheet result has already caused a dispute with a customer, a rep or a supplier, that number exists. If it has not and the spreadsheet only serves analysis, it may be fine where it is.
I don't have a technical team to build and maintain this. Does that stop me?
No, as long as the decision about the rules stays with you. Nobody outside knows which commission tier applies to which product, and that has to be written down. Building and maintenance can be contracted, with documentation and access to what was delivered written into the contract.
The next step
If there is a spreadsheet in your company that closes payments, commissions or prices, it is worth mapping where the rule lives and who can change it, and the Operational Architecture Diagnostic is a 30-minute conversation to start that mapping.
Related case studies
Did you recognize your operation in this article?
Book the Operational Architecture Diagnostic: 30 minutes to map where your operation's bottleneck is. No proposal in the first conversation.
Book a diagnostic