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?"
Ok, slightly off topic, but why is this posted in Hardware?
"Nobody owns the fucking words man." - James Dean
If every change even a correction needs to be audited save-to-save of a file, then why don't we implement a Wiki style log of changes to the file? I wonder if Open Document Format would easily support this.
The mountains of next-to-worthless data the piles of auto-saves would generate is mind boggling.
Oh You POS
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
"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!!!
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.
Well, as you alluded to earlier in your post, whether a spreadsheet has errors in it depends on how it was made.
This also goes for maintaining integrity of the spreadsheet. Both OpenOffice.org and Microsoft Excel offer the ability to protect cells from modification. If you design your spreadsheet application in a certain way, you can prevent corruption to the spreadsheet through modification. It's tricky and it often requires a lot of macros and workarounds to make it happen, but it can happen. Also both Excel and OOo offer the ability to track changes made by users, so there is some level of built in accountability -- but not much.
One of the main points of TFA, I think, is that spreadsheets are good for quick-and-dirty scratchpad applications, but really fail to complex applications that require maintainability, documentability, and good authentication and security surrounding changes.
If you need that, you need a database application. This is what I've been telling people for YEARS -- don't use Excel for what you really need a database app for, and, conversely, don't write a database app for what you could easily just as easily do in Excel.
My blog
Spreadsheets are basically a form of visual programming language, so it is unsurprising that bugs occur. They are basically designed so that ordinary people can use them, which means that they lose some of the strictness that is enforcable in a normal programming language. More worringly, I'd say that some of the properties of spreadsheets naturally encourage bugs. For example, when programming, code duplication is considered bad, and shared common code good, because it encourages simplicity and when bugs are found, they can be fixed in a single location. Conversely, in spreadsheets, the user is actually encouraged to duplicate code, with tools that let you "drag down" equations into neighbouring cells. Perhaps we should be wondering if it would be a good idea to create some kind of "next-generation" spreadsheet system that addresses these problems. Whereas programming languages have evolved constantly over the years, spreadsheets remain unchanged.
were you expecting to see a sig here? perhaps you'd rather see the inside of an ambulance!
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 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
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.
The answer to that question is that Spreadsheets are not designed for maintenance like most languages are. The difference here is HUGE, in fact, there is no comparison whatsoever.
Applications designed by real teams in real languages involve some absolute requirements:
Here are some bare minimal language issues that most decent developers wouldn't question for work on a team (Individual developers/web developers are often a bit more loose)
Data hiding... CRITICAL! Any language that defaults to global data would be ludicrous. To even allow data access beyond the smallest boundaries is frightening.
Code organization--you should be able to group common functions with the data that they represent.
Code History--the ability to compare code changes to a previous version.
Highly documented code/self documenting code--duh
Some form of design--many large projects are in/moving to OO code, it's difficult to handle the design of a large application without it.
I'm not saying these things are impossible to see in a spreadsheet, but pretty unlikely. On top of that, the level of freedom given to the user of a spreadsheet makes the data environment of the program extremely difficult to control.
Absolutely opposite ends of a spectrum.
I will give you what I consider the most blatant and insane example.
U.S. Constitution article III
The trial of all crimes, except in cases of impeachment, shall be by jury; and such trial shall be held in the state where the said crimes shall have been committed; but when not committed within any state, the trial shall be at such place or places as the Congress may by law have directed.
U.S. Constitution: Sixth Amendment
Sixth Amendment - Rights of Accused in Criminal Prosecutions
In all criminal prosecutions, the accused shall enjoy the right to a speedy and public trial, by an impartial jury of the State and district wherein the crime shall have been committed, which district shall have been previously ascertained by law, and to be informed of the nature and cause of the accusation; to be confronted with the witnesses against him; to have compulsory process for obtaining witnesses in his favor, and to have the Assistance of Counsel for his defence.
Now to me and I think to 99.999% of americans the phrase ALL clearly means every single instance.
To judges and lawyers however this is apparently different. As it currently stands you do not have the right to a jury trial.
Service guarantees Citizenship! Questions Guarantee GITMO.... Amerika Uber Alles!