Slashdot Mirror
Errors in Spreadsheets are Pandemic
G Roper writes "Studies show that most spreadsheets have critical errors in one percent of their cells, well beyond a permissible level. Here are some news stories about spreadsheet errors. Spreadsheets won't protect a firm from liability when they are audited and spreadsheet errors found: spreadsheets are not secure, provide no audit trail and won't pass HIPAA or Sarbanes-Oxley auditing. How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"
"How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"
With a pencil. haha.
Well as one of my bosses says, "We need more Double E masters." :P.
Alas he doesnt mean Electrial Engineers, but "Excel Experts."
He's very bitter about his education
j^2
From the abstract: "Although spreadsheet programs are used for small "scratchpad" applications, they are also used to develop many large applications. In recent years, we have learned a good deal about the errors that people make when they develop spreadsheets. In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists. "
I think "how many errors, not whether an error exists" is just as true for applications and programs written in any language or using any technology. What's so insidious about spreadsheets is their integrity and the difficulty to maintain that.
Once you start changing any complex spreadsheet you risk and almost guarantee corrupting other parts of the spreadsheet ostensibly okay. The spreadsheet is so inextricably integrated to itself, you pull one string, and some widget a million miles away suddenly misbehaves, though, you're unlikely to notice until later, if at all.
IT should be strict about policy around spreadsheets... spreadsheets are great powerful tools, but they shouldn't be anointed as applications.
I worked on a team that created a large software development workbench. A critical piece of this workbench included a suite of spreadsheets with amazingly complex macros and formulae hidden way out of the casual users' sight. Immediately upon release (and much aligned with my warning and prediction) the workbench fell apart on a daily, even hourly basis, among many teams out in the field. Turns out users were deleting rows in the template spreadsheets deemed irrelevant and unnecessary to their work. Guess what got deleted along with the "unnecessary rows"? Yep, chunks of macros critical to the proper function of the workbench.
Ok, slightly off topic, but why is this posted in Hardware?
"Nobody owns the fucking words man." - James Dean
How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"
My guess it they're not. I've met FIERCE resistance in the past from accounts trying to reform their spreadsheet ways. Every accountant understands the spreadsheet. The Financial Director understands the spreadsheet. If you can't get the Financial Directory to back your plans then any reform is dead in the water.
The problem is born out of bad communication skills. IT generally assumes that just because the FD doesn't understand C++ he is stupid. We see this kind of behavior all the time on Slashdot:
No fucking shit he understood the GPL. Let's see he probably got a 1st class degree in Law, Passed his BVC with flying colours. He then probably got his pupillage with ease (there are twice as many students each year as there are pupillages) and then rose to the Bar. After that, he'd have spent 15 years working cases in the Crown Court. If he didn't understand the GPL he would have fallen at the first hurdle. My brother is a lawyer and understood the GPL before he even took his LPC. By comparison, you're average IT guy is a mere peon. I'd wager that given your average programmer with no C++ experience, the Judge could beat the programmer hands down in a programming contest. These people are very, very smart.
The same is true of Financial Directors and their ilk. They have to take years of qualifications and have decades of experience before they're allowed to do their job. Talking down to them is a recipe for marginalization. So the solution is to talk to them in clear language. None of this bullshit bingo that seems to be infesting every cranny of IT - clear, plain language.
Explain the problem, then explain the solution. They don't want or care to hear about LAMP, AJAX or Web 2.0. This like a builder telling you the type of screws he's going to use to build your house. All that you care is that your house is well built and will last a long time without significant maintenance. All they want to know why they need your solution, how much it will cost and the consequences if they don't do it. Anything else is a waste of their time and will lower the amount of time they have for you.
Simon
For god's sake, please, PLEASE let them not cram yet another change tracker into a format that shouldn't support it. Change management already exists in so many forms it's not even funny (cvs, svn, source safe, etc), and works off the shelf with any document format. If people would just learn to put existing tools together instead of shoehorning all functionality into every application, things would be a lot simpler.
Slashdot needs a "-1, Wrong" moderation option.
The Urban Hippie
"How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"
I don't know about you, but I actually check my work and co-workers cross-check each other's work. Any spreadsheet whose numbers can't easily be checked out on a calculator should be designed such that the information generally flows in one direction and each step of a calcuation is broken out into separate rows whenever possible to make "debugging" easier.
Bill Clinton: Pimp we can believe in. - The Shirt!!!
provide no audit trail
... )
You can provide an audit trail in Excel:
Tools->Share Workbook->click "Multiple Users"->click "Advanced"->select how many days you want to keep a history for.
(It might not be good enough for HIPAA or SA but there is an audit trail
Sounds like you're advocating the wrong policy. How about locking the cells so users don't screw things up? You wouldn't let non-programers alter code, why would you let them alter the spreadsheet?
The world is made by those who show up for the job.
Yet another example of the truth of "those who do not understand UNIX are bound to reinvent it, poorly."
"[Regarding the 'cloud,'] ownership was what made America different than Russia." -- Woz
I'm not sure where you got the %1 idea from, but in one of the linked articles there was a a $50 million dollar spreadsheet error (spending bugeted money that did not exist). There was also an error in a spreadsheet that miscalculated natural gas reserves that causes a BILLION DOLLAR rise in the commidity value (aka speculators) which was not real.
and lastly, who cares? Think Sarbanes-Oxley, if your a CEO, you care, alot.It is difficult to eliminate the errors, so a better solution is to minimize them. The easiest way to do this is to add extra workbooks named "sheet2" and "sheet3" with thousands of extra cells in them. Then, the percentage of error is 3 times lower. Example:
Before: "sheet1" has 50x50 cells, with 25 errors. That's 25 / 50^2 = 1% errors.
After: Add "sheet2" and "sheet3" with another 50x50 cells. Now, the error rate is 25 / 50^2 / 3 = 1/3 % error.
According to my spreadsheet, that is a much better error rate!
I agree. Spreadsheets are quite powerful, but I'm often frustrated with them. Since I have programming experience, I can see the ways that spreadsheets could be made more robust and more powerful.
The first thing to change is what you alluded to: code should not be duplicated, but linked instead. When you drag a formula, it should really just fill those cells with references to the formula to be used. When you try and edit any one of those cells, you are given a popup where you can edit the master equation used in that range. This would make it so much easier to fix spreadsheets. With fewer points of failure, it is much easier to find bugs or add functionality.
A related point is that the way a single cell is designed makes it hard to read complex equations. A complex operation should generally be split across multiple cells, as this makes debugging and understanding workflow easier. However sometimes you need a single cell to be quite complex, and the way most spreadsheets display the cell contents (as a single long line) makes it difficult to understand. Again the cell contents should appear in a pop-up, where proper indenting, bracket-balancing, comments, and color-coding can occur (i.e.: everything that a normal programming IDE gives you).
Another thing that would make spreadsheets more useful/powerful would be the ability to COMPILE them into another form. I often use spreadsheets for prototyping a new analysis, and then re-code it into another form (Java, C++, Matlab, etc.) for efficiency purposes. In many cases this is a good idea, since it makes sure the programmer understands the problem fully. However in other cases it is wasted effort. A spreadsheet is slow to calculate but sometimes it provides the best layout for coding a solution. What I would like to see is a spreadsheet program that converts the entire spreadsheet into some kind of human-readable linear code (C++ style syntax or whatever). This would involve converting blocks of numbers into vectors, arrays, or matrices, automatically naming them (based on the column header, for instance), and creating loops to account for iterative operations, and translating all the spreadsheet functions into other types of syntax. Having this human-readable version of the code would be great. It could be fixed and improved (for efficiency or interacting with other programs), commented, and so on.
This human-readable code could then (obviously) be compiled into an efficient binary form. This would make spreadsheet concepts of workflow applicable to more demanding applications.
Lastly, I think spreadsheets need to learn what other programming forms already know: comments are important! The spreadsheet should strongly encourage the user to enter an explanation for every formula they write. Everything should be commented. This is the only way for future people to fix or modify the spreadsheet. Plus, accountability and traceability are easier.
Perhaps I'm asking for too much... but I think if spreadsheets evolve in this direction (towards being a more rigorous programming environment), the benefits would be huge. People are now (more or less) used to using a spreadsheet. This kind of "programmer's spreadsheet" would be great for people who know programming (it becomes more powerful) and also for casual users (some rules enforce better practices).
I'm amazed at the rut Excel traps IT-based businesses in. Excel was once the best thing computers did, apart from screensavers and ahead of email. But by the late 1990s Excel should have become merely the GUI for relational databases. Even cheap/free ones like MS-Access and MySQL, if not Oracle, Postgres, SQL-Server. Excel should have had macros programmable in the exact same language as actual databases, like VB (not VBA), Perl or something unique to its vertical integration. Upgrading from the starter DB to the enterprise DB should have been a matter of installing the new backend on the network, and configuring the Excel client.
If that path were taken, Excel would be a manageable platform. Instead, it's trapped in the early 1990s desktop, with all its limitations to collaboration, performance, maintenance and dataflow. Every improvement in those areas is a one shot deal, a hack on a once-elegant app now hacked to death.
Maybe the new generation of open formats and distributed computing services offer a chance to try again. Excel will probably include those, just diluted by all the wrong ways retained as its "legacy".
--
make install -not war
About 10 years ago, a Silicon Valley manufacturer of medical imaging equipment hired me to do accounting work for them. Among my many tasks for this firm was the weekly generation of a report based on the company's current accounts receivable balance. I was told that this report was very important since it was used by one of our execs. during his weekly 'power breakfast' meetings with the other heads of the company.
A month after I arrived at the company, I noticed that the numbers didn't look right when I generated this weekly report. I started examining the spreadsheet formulas and soon found a small error in one of the calculations we used to derive our total balances. I notified my manager and we both agreed that the original spreadsheet wasn't giving accurate results. I corrected the formula and then patted myself on the back -- after all, I'd uncovered an error that many people, including my manager, had missed for months. I thought I was in good shape at the company after that because I'd done the right thing. I'd fixed a problem. Yay for me.
However, a week later, my manager brought me into his office to talk about the issue. I was more than a little surprised when he asked me to go to my desk and change the formula back to what we'd used before. I asked my manager if he still agreed with me that the old formula was giving incorrect data. He just smiled and said yes, he agreed with my original assessment. I was right, he told me, but our exec. had still asked him to revert to the old formula, no reasons given.
Shortly after this incident, my manager again brought me into his office. He had a pained look on his face as he began to tell that the company wouldn't be needing my services anymore. My manager never gave me an explanation as to why, but I didn't really need an explanation. Even though I'd uncovered an error in the company's accounting procedures, I'd made an even bigger error in the process -- I made our exec. look bad when he handed out the correct report during his power breakfast meeting. It turned out that the numbers weren't so rosy that week as they'd been in previous weeks. The other company heads wanted to know why. I'm not sure what our exec. told them then, but I can't imagine it made him look good no matter how he tried to spin it.
I suppose, if the numbers had looked better using my correct spreadsheet calculations, maybe I'd have received a raise from that exec. In this particular case, and much to my surprise, the wrong answer was the right answer in his method of bookkeeping. Frustrated by this incident, I left the accounting business soon afterwards. As it later turned out, the company went belly-up years later. Looking back, I like to imagine that reason was that the company's bankers were using spreadsheets based on mathematics instead of wishful thinking. Then again, after seeing what happened with Enron, I wonder if the bankers were in on it too.