Study Finds 94% of Business Spreadsheets Have Critical Errors (phys.org) 124
A recent study reveals that 94% of spreadsheets used in business decision-making contain errors, highlighting significant risks of financial and operational mistakes. Phys.org reports: Errors in spreadsheets can lead to poor decisions, resulting in financial losses, pricing mistakes, and operational problems in fields like health care and nuclear operations. "These mistakes can cause major issues in various sectors," adds Prof. Pak-Lok Poon, the lead author of the study. Spreadsheets are crucial tools in many fields, such as linear programming and neuroscience. However, with more people creating their own spreadsheets without formal training, the number of faulty spreadsheets has increased. "Many end-users lack proper software development training, leading to more errors," explains Prof. Poon.
The research team reviewed studies from the past 35.5 years for journal articles and 10.5 years for conference papers, focusing on spreadsheet quality and related techniques across different fields. The study found that most research focuses on testing and fixing spreadsheets after they are created, rather than on early development stages like planning and design. This approach can be more costly and risky. Prof. Poon emphasizes the need for more focus on the early stages of spreadsheet development to prevent errors. The study suggests that adopting a life cycle approach to spreadsheet quality can help reduce errors. Addressing quality from the beginning can help businesses lower risks and improve the reliability of their decision-making tools. The study has been published in the journal Frontiers of Computer Science.
The research team reviewed studies from the past 35.5 years for journal articles and 10.5 years for conference papers, focusing on spreadsheet quality and related techniques across different fields. The study found that most research focuses on testing and fixing spreadsheets after they are created, rather than on early development stages like planning and design. This approach can be more costly and risky. Prof. Poon emphasizes the need for more focus on the early stages of spreadsheet development to prevent errors. The study suggests that adopting a life cycle approach to spreadsheet quality can help reduce errors. Addressing quality from the beginning can help businesses lower risks and improve the reliability of their decision-making tools. The study has been published in the journal Frontiers of Computer Science.
Spreadsheets suck, pencil and paper suck more (Score:4, Insightful)
The question isn't if spreadsheets invite mistakes. The question is if they *result* in more mistakes than the most likely alternative: pencil and the back of an envelope.
And taxes suck the most. (Score:3, Insightful)
The question isn't if spreadsheets invite mistakes. The question is if they *result* in more mistakes than the most likely alternative: pencil and the back of an envelope.
Given the complexity of business tax code, let’s stop bullshitting ourselves. The question isn’t whether or not any process invites financial mistakes. The question should be, how many of those “mistakes” are by design.
Billionaire accountants, don’t say “oops, did I do that?” every time they “accidentally” build a tax shelter. They didn’t slip and fall on a tax loophole.
Re: (Score:2)
If you think the subtle loopholes and contradictions are by design then you have a much higher opinion of the lawmakers than I think is warranted.
Re: (Score:3)
If you think the subtle loopholes and contradictions are by design then you have a much higher opinion of the lawmakers than I think is warranted.
Of course they’re by design. Otherwise we would have stopped the taxpayer bleeding long ago and probably learned after the first not-so-happy “accident” on the tax code books. You know, before allowing Greed N. Corruption to create another 2,876 more of them. Accident my ass. Why do you think they literally call them a “loophole”?
Besides, the design part isn’t the law itself. It’s more getting all of society to go along with it and not charge you with a crime an
Re: (Score:3)
Of course theyâ(TM)re by design.
No, not of course. Some, sure, but most, no way.
Designing laws is incredibly hard. It's more or less impossible to write code without bugs, never mind making it future proof to new conditions and writing it in English.
Why do you think they literally call them a âoeloopholeâ?
Because a loophole is a small hole in an otherwise solid wall.
You are saying they intentionally design in subtle bugs which benefit them. This means the politicans in question muse be incred
Re: (Score:3)
Of course theyâ(TM)re by design.
No, not of course. Some, sure, but most, no way.
Designing laws is incredibly hard. It's more or less impossible to write code without bugs, never mind making it future proof to new conditions and writing it in English.
Why do you think they literally call them a âoeloopholeâ?
Because a loophole is a small hole in an otherwise solid wall.
OK, let me clarify my point here. Once they discover said hole in wall, WHY do you think no one bothers to fix it?
A unknown loophole, could very easily be dismissed as an accident. Truly.
The dozens of known loopholes, still exist by corrupt design and nothing more. Otherwise, we would fix them. A hole in a wall is either a problem, or a window.
Re: (Score:2)
That is a very big difference, though. By design assumes these law makers are uber genius level guys. Extremely unlikely.
Not fixing a loophole that crept in ... that assumes these law makers are just corrupt little shits. Extremely likely.
Re: And taxes suck the most. (Score:3)
Oh they don't have to be uber geniuses to design in loopholes. They have experts in the affected industry that would benefit from such loopholes do that for them.
Re: (Score:2)
Please think of it like a zero day. Nobody knew it was there. Once they DID, they took advantage. We don't put bugs in our code on purpose. It just happens.
Re: (Score:2)
And if the zero day comes out, there is a clear fix, and the developers refuse to patch it, what would you call it then?
Re: (Score:2)
Well there's a mix of reasons. Sometimes it's lobbyists, but not always.
There are various non nefarious reasons. One is that thetev is limited legislative capacity, and fixing a loophole means something else doesn't get done.
The other problem is that there are consequences. Take the VAT loophole in Europe, for example. It was designed to be friction free, which is especially important to small businesses. Unfortunately inveterate shitweasles like Amazon and Apple exist. The EU had to figure out a way of cl
Re: (Score:2)
Yes, bugs happen. If you know the bug is there and do nothing to fix it, it may be considered a design decision.
Re: (Score:2)
In the real world bugs don't necessarily get fixed, because they're are other higher priority things going on and because fixing one bug risks breaking something even worse.
Exactly the same is true for bugs in the law. But they're in English, not code, it's impossible to do CI and the black hat hackers are doing their job completely legally for large salaries.
Re: (Score:2)
Many if not most lawmakers are playing the role of useful idiot. They incorporate bits handed to them into the law.
Another stupid, click-bait title (Score:4, Interesting)
This time, the stupid headline isn't slashdot's fault, but phys.org's. The spreadsheet quality study [springer.com] is a literature review, not an actual test of spreadsheets. The claim that "94% of spreadsheets contain errors" comes from two of journals it reviewed:
- McDaid K, Rust A. Test-driven development for spreadsheet risk management. IEEE Software, 2009, 26(5): 31–36 11.
- Panko R R. Two experiments in reducing overconfidence in spreadsheet development. Journal of Organizational and End User Computing, 2007, 19(1): 1–23 12.
The study is a really terrific review, but the conclusion is not news. Of course an end-user program will "contains errors". ALL software contains errors. I wrote software for 49 years and never once wrote something with zero errors, nor have I ever seen anyone else do so either. Errors are baked in, because software is a translation.
Re: (Score:2)
ALL software contains errors. I wrote software for 49 years and never once wrote something with zero errors, nor have I ever seen anyone else do so either.
Then you did it wrong. To write perfect error-less code just tell the end user that it's not an error, they're just doing it wrong. I also suggest putting your fingers in your ears when told about a bug.
Re: (Score:2)
> The question isn't if spreadsheets invite mistakes. The question is if they *result* in more mistakes than the most likely alternative: pencil and the back of an envelope.
The problem is spreadsheets live forever ("there is nothing so permanent as that which is temporary"). Someone knocks together a quick spreadsheet to see if something is the way they think it is - it turns out they're right, so they present it up the chain and BOOM - that spreadsheet gets used in every monthly update forever more. No
No Git (Score:4, Insightful)
Number one issue for spreadsheet reliability (other than Microsoft) is lack of any reasonable source management. I assume most spreadsheets offer some basic change auditing but nothing like the pipelines for code management, feature development, merging, deployment and automated testing you would have in any large software project hosted on Github/Gitlab/CodeCommit etc.
Frankly until you solve that you're not going to make any inroads into quality control and preventing garbage edits.
Re: (Score:2)
Also, no data migrations. If you want to rollback "schema" changes but keep the data (or vice-versa) I assume you're SOL
Re: (Score:2)
If you want to rollback "schema" changes but keep the data (or vice-versa) I assume you're SOL
theres nothing keeping you from separating the data from the logic, keep the data in single column(s) so you can easily manage it
Re: (Score:3)
Spreadsheets are not databases. Stop treating them like such.
Au contraire: xkcd.com/2180/ [xkcd.com]
Re: (Score:2)
Re: (Score:3)
Re: No Git (Score:2)
Re: (Score:2)
It's missing backups, schemas, revision history, etc
Spreadsheets can be backed up, just like any file can.
Many spreadsheets, including Google Sheets, include revision history.
Re: (Score:2)
Spreadsheets are not databases. Stop treating them like such.
No they are not, the problem is that most corporate employees only have access to a spreadsheet and don't have a database or a programming language able to interface with it.
If the only tool you have is a hammer, you're going to build everything with nails.
Re: (Score:2)
Re: (Score:3)
Number one issue for spreadsheet reliability (other than Microsoft) is lack of any reasonable source management. I assume most spreadsheets offer some basic change auditing but nothing like the pipelines for code management, feature development, merging, deployment and automated testing you would have in any large software project hosted on Github/Gitlab/CodeCommit etc.
Frankly until you solve that you're not going to make any inroads into quality control and preventing garbage edits.
Well, nowadays it would be easier to get all that working since most spreadsheets are now just text (usually xml), same for word documents or else.
I played with that a little a while ago and putting those documents in git worked amazingly well compared to older binary proprietary formats where the data simply gets duplicated and merges and diffs are useless.
Problem is most modern document formats (example a spreadsheet) are now a zip file you can simply unzip then you get a bunch of files that git plays wel
Re: (Score:2)
Re:No Git (Score:5, Insightful)
Number one issue for spreadsheet reliability (other than Microsoft) is lack of any reasonable source management.
No, it's not.
First, 90% of spreadsheet users have no idea what "source management" even is and wouldn't use it if it were available.
Second, the main issue is no mechanisms for consistency and integrity. Nothing ensures that changes are properly propagated, and while in general Excel does a good job at updating related cells if you insert or delete a row or column, every now and then this causes things going wrong elsewhere. Most spreadsheets in use also don't even utilize the protection of cells to ensure that someone doesn't overwrite a formula with a value, etc.
Third, the real main issue is that in at least half of the cases where Excel is being used, a database with a frontend would be the better solution, and lots and lots of fragile hacks are used to implement DB-functionality in a spreadsheet. MS has abandoned Access which could've been a solution, and the Apple-world alternative FileMaker isn't very popular, same for LibreOffice Base.
Excel is a perfect storm. It puts powerful abilities into the hands of people unable to handle them. It's like fighting school shootings by giving all those teenagers assault rifles - at first you might think "oh cool, that'll give them the tool they need" and then you realize just how many accidents are bound to happen.
Re: (Score:2)
FileMaker is still around (Score:2)
FileMaker is still around, although it's grown into a kind of application server thing, priced at USD21 per month per user or USD43 per month per user for their "cloud" plans, or USD16.50 per user per month to run it on your own servers. The single-user FileMaker Pro (equivalent to the thing you'd remember as FileMaker is USD594 now.
Re: (Score:2)
MS has abandoned Access
Nope [microsoft.com].
Re: (Score:2)
Who needs source management. This isn't a constantly changing code base. In most cases spreadsheets are developed once for a purpose and that's it. No need for source management beyond throwing it in a document management system so it can't be changed (or use Excel's change management feature to see who last edited something, or restrict it).
The issue is more fundamental: Does this big spreadsheet do what is needed to do, and do it with the required accuracy. Did someone mean to use median instead of mean.
Re: (Score:3)
What do you mean? I totally have version control! It's all right here in business_calcs.revB.03-04-2023.not_ready.use_this_one.ver16.jon_
Gotta dumb-down those job functions. (Score:3, Insightful)
"Many end-users lack proper software development training, leading to more errors," explains Prof. Poon.
This is probably because many businesses see spreadsheets as a fairly mundane task any of their employees should be familiar with. Treating something as not a special skill is how you justify not having to hire someone with special abilities to do a task.
See also: "We ask our employees to wear a lot of hats"
Re: Gotta dumb-down those job functions. (Score:3)
Re: (Score:3, Insightful)
Re: (Score:3)
For anything beyond basic spreadsheets, you shouldn't be using a spreadsheet.
You should be using a real database combined with a real programming language, with documented schema, structured code, and source control.
Of course, but the real world tends to be a bit messy.
Example #1 is the most obvious: a spreadsheet starts out as the sort of thing precisely described: something basic, and for which a spreadsheet is a perfectly acceptable tool for the job. Say, a pricing list. It's got four columns: item, wholesale price, markup, and the calculated retail price. The company sells 30 SKUs, it's super simple, and super easy. Then, things morph. The company starts selling more items, they introduce seasonal pricing, a distri
Re: (Score:2)
Re: Gotta dumb-down those job functions. (Score:2)
It's good you don't care about enshitification: The rest of us want quality, particularly in a UI that doesn't track changes, and quality is an uncommon skill.
Re: (Score:2)
You would be surprised to find out just how much more you can do with spreadsheets if you have that "special training" under your belt.
Re: Gotta dumb-down those job functions. (Score:2)
Re: (Score:2)
If you need special training to use spreadsheets then you probably shouldn't be using them.
Therefore no one should be using them. QED.
Re: (Score:2)
^ ^ ^ ^ This. A thousand times this!
It is not a matter of managing a spreadsheet's life cycle.
If a job has a "life cycle" then a spreadsheet is NOT the right tool.
OTOH the manager might be a "right tool".
Re: (Score:2)
Re: (Score:2)
I see this as a unrealistic research based bias. 95% of spreadsheets used to make business decision have ZERO software development in them. They are tables of data with a bit of filters and some coloured highlights to please the decision makers.
Now I fully buy the fact that 94% of spreadsheets with advanced macros have errors in them for the reasons you mention, but the reality is no one should need any special ability to use a spreadsheet and the majority of the world's spreadsheets should have zero softwa
Re: (Score:2)
get rid of VBA! (Score:2)
get rid of VBA!
Re: (Score:3)
VBA is pretty much gone already. You can't use it without disabling several layers of protections that hide the feature, and you have to enable it with a local admin account. I haven't seen a VBA app in years.
Even if there is still VBA around, this article isn't talking about VBA, but ordinary formulas.
Re: (Score:2)
Granted, a better language might have been better, but VBA was pretty good for what I needed. (though it would be better if it had a more fine-tunable security other than just allow macros or don't)
The best part (Score:2)
Re: (Score:2)
Re: (Score:2)
If Lester took some math classes, he might be better at programming than many professional programmers.
Programmers don't take math classes anymore? It was a requirement back then, what a shame!
Re: The best part (Score:2)
Re: The best part (Score:2)
Re: (Score:2)
"The best part is that the people writing spreadsheet macros arent professional programmers. "
Most people writing *any* program aren't professional programmers. Most programs are tools, not products.
I am an integrated circuit designer, and my humorous description of hardware design is that it is 50% software and 50% PowerPoint.
A spreadsheet is.. (Score:5, Insightful)
..a programming language
A really bad programming language with no debugger and poor visualization of code structure
I'm surprised that there hasn't been real improvement, given how old the tech is and how many critical systems depend on it
Re: (Score:2)
And it's even a declarative, functional programming language, which is supposed to be provably correct!
Re: (Score:2)
Plus bad management of the file. Each month someone clears out the cells they were told to reset new month and they do a save as. Then the excel file one day just randomly blows up... Look at the create date and wonder how was an xlsx file created in 2005...
Re: (Score:2)
A spreadsheet is a programming language. A really bad programming language with no debugger and poor visualization of code structure.
What? I'd say a spreadsheet is an AWESOME debugger. Things are already broken up into small computations, and you see the result of every intermediate calculation right there in the spreadsheet. If you want to add a "watch" it's easy -- just write it in any other cell. It's like the best of Hypercards and Swift Playgrounds.
I think it also has good visualization of structure, although what's important in a functional paradigm is DATAFLOW not code structure. Excel visualizes this by coloring the inputs to a c
A fool with a tool is still just a fool. (Score:2)
Use a database! (Score:2)
Pet peeve of mine where "business types" make up spreadsheets without any data protection, formula protection, all thumbs protection, etc.
A database with a query language has all of these things built in but spreadsheet power users are clueless.
Spreadsheets (Score:3)
Re: (Score:3)
Business spreadsheets used to be made up by trained accountants, who presumably knew what they were doing.
About 40 years ago, I had a summer job assisting the head accountant of a moderately sized manufacturing company. My task was to enter or update the detailed materials and dimensional info on hundreds of manufactured parts in a large Lotus-123 spreadsheet that he had created, so they could compute what it cost to make each one. I assume so they could figure out taxes, profits, etc. (It was a really fun job because it was my first chance to use a semi-serious computer: An IBM PC with a real Model M keyboard.
Re: (Score:3)
Indeed. All spreadsheets should include a global fudge factor variable for tweaking results so they look good.
That's such an obvious solution to so many problems.
Re: (Score:3)
You've got to give accountants some slack here. They need to tweak knobs so they can have time to spend on important things like worrying whether IEEE floating point values round their results in the wrong direction when they try to truncate all of their intermediate calculations to nice round numbers.
Re: (Score:2)
Re: (Score:2)
Correct (Score:5, Insightful)
And 78.6% of all statistics are made up.
Re: (Score:2)
Yip, I bet this "study" was done on a dodgy spreadsheet also.
Plus, if they are selling spreadsheet analysis tools, there's a 104% chance they are biased.
I'm not an exec, but... (Score:4, Insightful)
Every time I've been peripherally involved in the generation of these decision documents... they've been massaged by underlings to show what the higher-ups want to see, because if they don't get what they're expecting, they get pissed off.
They don't generally work from evidence to conclusion, they work from the conclusion and have people manufacture evidence to support it. Keep in mind being an exec doesn't make you rational, or a genius. It means you've been successful at acquiring and holding on to power (so far). Regrettably this is often useful and effective, which is why it happens so often, but it can also fail terribly.
This is obvious nonsense (Score:2)
The one thing I have learned is that nowadays absolutely fucking everything is a grift.
Re: (Score:2)
Have you looked outside the front door recently?
Re: (Score:2)
Re: (Score:2)
Well, I don't think that is how they defined critical. I don't think they mean that 94% of spreadsheet lead to incorrect mission critical decisions.
More likely that in 94% of spreadsheet there is a mistake that possibly compromise the integrity of the calculation. But if your rocket building company orders three too many packs of toilet paper, the rockets are not gonna fall.
35.5 years of spreadsheets? (Score:2)
So this study went back all the way to 1988? Back then, few people had even heard of spreadsheets, and absolutely *nobody* knew anything about good spreadsheet structure. What does it even mean to include such old spreadsheets in this review?
Re: (Score:2)
Nonsense. In 1988..90 I assisted on a spreadsheet that managed the transfer pricing for a multinational firm, 18 operating entities in 15 countries, that cumulatively had ~US$1.4B in Sales. A team of 12 in Corporate did the care and feeding of the beast. Re-Calc times were on the order of 40 minutes. In Engineering, we had dozen of large Spreadsheets: Theoretical MTBF calculations, Budgets for multi-million USD$ Departments & Projects, etc. Cumulatively 100's of people at this one business used
Re: (Score:2)
Funny, 100s of people. You're right, clearly knowledge existed at the time!
On the other hand, a "large" spreadsheet can be just as bad as a small one, and every less understandable.
Whenever I see a spreadsheet at my company, in my head I think, "There's a process that probably should be automated or moved to task-specific software."
"Critical" errors? (Score:3)
The critical errors listed in the article:
- Formula integrity
- Semantics
- Extendibility
- Logic transparency
Well duh, spreadsheets were never intended to create robust applications. They are a useful sketch pad where you can dump stuff, work with it for a bit, and move on. If you try to turn it into an application, well yes, it's going to be problematic.
But for most things, who really cares? These spreadsheets aren't generally used to replace people's brains, they are used to quickly sort, filter, and calculate sums or averages. If the semantics aren't quite right, nobody cares.
Re: (Score:3)
The problem is, they are being used as total process tools for anything from pricing models, cash flow forecasting, salary budgeting to even more business critical things. They start out as simple "sketch pads" that people promise themselves they'll do properly next year, and suddenly it's been a decade, the original author of the file has left and there's a mess of a web of interconnected files on Sharepoint.
While I agree 94% is probably a fairly made-up number if it pertained to ALL spreadsheets, it might
Re: (Score:3)
I don't disagree with you on how spreadsheets tend to outlive their usefulness. That's not a spreadsheet problem, that's an inertia problem. If your business is so married to the way they've always done things, that they can't move their critical processes off spreadsheets, then the "critical error" is that the company refuses to invest in process improvement. Spreadsheets are a symptom, not the cause.
Re: (Score:2)
Mo. But they are frequently used by the brainless to replace the brainless.
Re: (Score:2)
Good point! These days, I'm hearing the same concerns about AI! History does seem to repeat itself, doesn't it. But still the brainless ones seem to find places to work.
meh (Score:2)
how MBA-like (Score:2)
Was it a bug or a feature?
Garbage in = garbage out. (Score:2)
"t 94% of spreadsheets used in business decision-making contain errors, " Absolutely. People that make these entries, error, consistently. That data is never directly used to make mission critical decisions. Industries that depend upon clean data also employ data analysts, to assure individual entries are clean, anomalies are investigated and a pure dataset is recorded.
So yea, users fuck up data entry due to mistakes, errors or just plain laziness but data management is responsable for presenting cl
Oh my god (Score:2)
Is this the beginning of endless daily scrums, agile process and other silly time wasting software engineering minutiae for spreadsheets? Now I have to have a scrum master, a software lead, and endless peer review cycles before I can make a change to my spreadsheet?
Might as well put a bullet in the head of productivity.
The âoecurrencyâ format (Score:2)
Each user will trust the numbers they see. And two see information that is completely wrong.
Re: (Score:2)
A full 6% are error-free ??? (Score:2)
Are they telling us that the error-free fraction of spreadsheets has reached 6%? In this case I have a hew-found respect for all the spreadsheeters out there.
Or just that they do contain errors, but non-critical ones?
Investment banking runs on excel (Score:2)
35.5 Years Back? (Score:2)
I remember some really dumb attempts at spreadsheets I made in the 90's before I learned some good tricks to simplify the logic. I even did a company-wide timesheet spreadsheet for ~500 people that was just... bad.
I am sure if neuroscientists are creating spreadsheets the issues persist today; people like that (including my dear cousin and godmother) seem to have much more of chaos mindset and structure things in odd ways.
That said, I doubt many significant business decisions rely solely on one spreadsheet.
Skill issue (Score:2)
A lot of people use spreadsheets like they shouldn’t be used. And Excel encourages bad practices.
Also just in: MBAs (Score:2)
Also just in: 94% of MBAs think that working with a spreadsheet is how you manage a business.
Just as an example: a friend of ours manages a national franchise. The mothership regularly issues decrees like "all franchises must grow by 10% this year". For new markets, that's kind of anemic. For mature markets, it is impossible. But the pin-headed managers added a column to their spreadsheet that multiplied last year's results by 1.1, and they are excited, so...
So, why believe models are any more accurate? (Score:2)
This goes for climate and economic models. All data and forecasts are flawed.
Only the ones made with... (Score:2)
Doesn't make sense (Score:2)
If you're going to make the effort to define and architect your spreadsheet application, step one would be to decide to not use a spreadsheet in the first place. Spreadsheets were always intended to be useful for calculating against rather limited sets of tabular data. Sure, they've become ubiquitous and repurposed for far greater functionality because their cost of entry is so low, but they really shouldn't have. Same goes for MS Access.
lossage by losers (Score:2)
The problem with business spreadsheets is they involve programming by non-programmers in what is probably the world's worst most opaque programming language.
flamebait for facts (Score:2)
I'm just going to start calling these out every single time. This is one of today's three-fer downmods from my cowardly harasser. He got smart enough to stop modding down three posts in a row from my history when I called him on it, but not smart enough to stop doing all three downmods within the space of a minute so it's obvious.