×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Errors in Spreadsheets are Pandemic

timothy posted more than 7 years ago | from the obscure-but-significant dept.

322

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?"

cancel ×
This is a preview of your comment

No Comment Title Entered

Anonymous Coward 1 minute ago

No Comment Entered

322 comments

Easy question. (5, Funny)

Anonymous Coward | more than 7 years ago | (#15474706)

"How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"

With a pencil. haha.

Yeah.... (2, Funny)

Anonymous Coward | more than 7 years ago | (#15474710)

Back to ledgers and slide rules I say!

I hear we need: (5, Funny)

j2crux (969051) | more than 7 years ago | (#15474716)

Well as one of my bosses says, "We need more Double E masters."
Alas he doesnt mean Electrial Engineers, but "Excel Experts."
He's very bitter about his education :P.

Re:I hear we need: (5, Funny)

BMonger (68213) | more than 7 years ago | (#15475031)

I hear they have some great "Excel Expert Masters" in the department of redundancy department.

spreadsheet errors are hard to fix (5, Informative)

yagu (721525) | more than 7 years ago | (#15474717)

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.

Re:spreadsheet errors are hard to fix (5, Insightful)

morgan_greywolf (835522) | more than 7 years ago | (#15474793)

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.

ever heard of locking cells? (4, Insightful)

jbeaupre (752124) | more than 7 years ago | (#15474823)

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?

bad choice for applications, but... (2, Interesting)

Beryllium Sphere(tm) (193358) | more than 7 years ago | (#15474870)

>they shouldn't be anointed as applications.

Everything about them is wrong for being an enterprise application, but sometimes they're better than nothing.

I know a company where the finance rollups and planning are done with a mountain of Excel spreadsheets and scripts. Only one person understands it. Sick, but every attempt to do it right with a real database has failed after burning millions of dollars. It's been like this for at least ten years.

Advice? Fall back on the ancient wisdom of the finance guys and put cross-checks in place so you can at least detect the errors.

Re:spreadsheet errors are hard to fix (1)

earthstar (748263) | more than 7 years ago | (#15474879)

Guess what got deleted along with the "unnecessary rows"? Yep, chunks of macros critical to the proper function of the workbench.

Thats why there is a facility to lock the cells is available - atleast in Lotus 123.
It was a commom practice at my office- many would get spread sheet that had to be filled in with the appropriate data and sent ; The sheet will usually be locked to prevent change of any formula/columns/rows...Although it could be disabled by the user,it offered some level of safety.
Ofcourse password protection could be implemented.

Re:spreadsheet errors are hard to fix (2, Interesting)

Hao Wu (652581) | more than 7 years ago | (#15474985)

So is the fundamental problem from user mistakes? Type-o's? Or is it data corruption caused by.. I dunno, noisy signals or whatever... some natural hardware problem?

Re:spreadsheet errors are hard to fix (2, Informative)

Red Flayer (890720) | more than 7 years ago | (#15475024)

I've developed some spreadsheet mini-applications for various employers, and there are two basic rules I swear by about both distributed and centralized spreadsheet apps:
1. Black box. Users should see input and output, that's it. Especially wth Excel, a user with a little bit of knowledge is VERY dangerous.
2. Lock it down. Every cell that's not an input should be password-protected. This would have prevented the deletion problem your team experienced.

Re:spreadsheet errors are hard to fix (2, Insightful)

Jason1729 (561790) | more than 7 years ago | (#15475066)

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.

Then the problem is with the users.

You don't edit the source of websites to delete chunks you have no interest in. You don't delete chunks of the windows code that are irrelevant and unnecessary to you. You don't delete chunks of any program. Why should people think this is okay in a spreadsheet?

Hardware? (5, Insightful)

punkass (70637) | more than 7 years ago | (#15474720)

Ok, slightly off topic, but why is this posted in Hardware?

Re:Hardware? (1)

punkr0x (945364) | more than 7 years ago | (#15474742)

I was just thinking that! Closely followed by "Why am I reading a paper on spreadsheets" and "How is this news?"

Re:Hardware? (5, Funny)

j0e_average (611151) | more than 7 years ago | (#15474780)

Actually, the editors had all the stories queued up for the day in Excel. They managed to sort the list incorrectly, which caused this story to be posted under Hardware by mistake.

Re:Hardware? (1)

PhoenixK7 (244984) | more than 7 years ago | (#15474971)

Actually, if they used spreadsheets every day to handle the queue it'd explain a lot of those other typical errors.

Meh, who needs excel when you can do your finances in MATLAB :-D

(As a serious aside, I don't do it myself, though I would if I weren't lazy.. but I know PIs that actually DO use matlab to handle their finances)

Re:Hardware? (0)

Anonymous Coward | more than 7 years ago | (#15474866)

Well it's Excel we are talking about, and Slashdot editors aren't reknown for their intellectual prowess. So it's not going to be posted in Easyware, is it?

Re:Hardware? (2, Insightful)

hackstraw (262471) | more than 7 years ago | (#15474937)

Ok, slightly off topic, but why is this posted in Hardware?


Its entirely ontopic. The slashdot editors were cleverly illustrating how easy it is to make a simple mistake, like in spreadsheets.

Now, offtopic!

I demand to have serif fonts again here on slashdot. I also demand to put the scores near the comment title.

Re:Hardware? (1)

Control Group (105494) | more than 7 years ago | (#15475013)

Huh

I've got serif fonts right now.

Of course, prior to the change, I was set to the "low graphics for lynx users" style (or whatever it's called now, I set it to that years ago and haven't looked since). The new version of that sure looks to be using a serif font.

Running IE 6 on Win XP.

Re:Hardware? (1)

caseih (160668) | more than 7 years ago | (#15475041)

I demand to have serif fonts again here on slashdot. I also demand to put the scores near the comment title.

I agree, particularly about the scores stuff. Time to hack the CSS and use a local style sheet.

Spreadsheets (2, Funny)

Hawat (266650) | more than 7 years ago | (#15474725)

Blame it on the users of course, especially the accountants.

Re:Spreadsheets (1)

celardore (844933) | more than 7 years ago | (#15475061)

I totally agree! I work in accounting, and as a general rule everything is checked at every stage of authorisation. Usually it's a pretty good system, except once when I had a formula error that went unchecked. Everyone looked at the print and just saw that excel had added it up and assumed it to be right. Whoops.

I was the one that got in trouble, as we paid this company a bit more than we should have. We held the amount back the next week, but my boss was still a little cross with me as it was clearly the original figures that were wrong. He couldn't be too mad though, as he should have checked them before signing them. Then another manager signed them, and then someone else paid them... My point is, rely on checking everything before you sign it. Ensure authorisation levels are kept, and double check before you present any figures to anyone!

ODF (3, Insightful)

From A Far Away Land (930780) | more than 7 years ago | (#15474730)

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.

Re:ODF (4, Insightful)

Skreems (598317) | more than 7 years ago | (#15474745)

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.

Mod Parent Up! (4, Insightful)

mrchaotica (681592) | more than 7 years ago | (#15474848)

Yet another example of the truth of "those who do not understand UNIX are bound to reinvent it, poorly."

Re:Mod Parent Up! (1)

susano_otter (123650) | more than 7 years ago | (#15474988)

"those who do not understand UNIX are bound to reinvent it, poorly."

Those who do understand UNIX being responsible for inventing it poorly in the first place, I take it?

Re:ODF (1)

AuMatar (183847) | more than 7 years ago | (#15474894)

I think revision control should exist in the filesystem (and be capable of being turned on/off per file/directory). Think about it- never having to worry about overwriting a file with bad data again, or not having backups of a file someone deleted. The ability to turn it off for files and directories would stop it for being a space hog for rapidly changing files (like logs or temporary directories for builds).

Re:ODF (0)

Anonymous Coward | more than 7 years ago | (#15475005)

Although not a filesystem feature...it is an Operating System feature and has been around since the 1980s in the OS called VMS.
I create my file, it's called FOO.BAR;1
I open my file, make some changes, close it, it's now called FOO.BAR;2
I open my file, make some more changes, close it, it's now called FOO.BAR;3
I open my file, make some more changes, close it, it's now called FOO.BAR;4
etc. etc.
All versions are available so I can revert back up to 32767 times if I want to

Re:ODF (1)

AuMatar (183847) | more than 7 years ago | (#15475111)

I'm a bit too young to remember VMS, but I have heard of this before. My idea would be similar, but drop the naming convention in exchange for some new file system calls to open and read old versions, and to revert the current version.Still, an idea that never should have been dropped for as long as it has been. I've actually considered hacking this into Linux a time or two, but my eyes glazed over when I saw the VFS code.

Re:ODF (1)

Ana10g (966013) | more than 7 years ago | (#15475034)

They already did that, actually. Remember an ancient (okay, start the flamewar ;) OS called VAX? IIRC, every new version of the file appends a .number indicating the version of the file. So, just switch to VAX, and run Excel on there (HA!).

Re:ODF (0)

Anonymous Coward | more than 7 years ago | (#15475062)

Ironically enough, Microsoft is hyping this ability for one of their upcoming products...though I don't remember whether it's part of WinFS, Vista, or Office 12 (I think it's part of O12's new XML-based .docx format).

Re:ODF (1)

AuMatar (183847) | more than 7 years ago | (#15475098)

If itsd part of Office, its not what I'm talking about. It would need ot be part of Vista/WinFS. But since WinFS got dropped from Vista, I rather doubt it.

Re:ODF (1)

Homology (639438) | more than 7 years ago | (#15475003)

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.

Most version/revision control applications does not work very well with "any document format". The reason is that so many documents formats (expand this to office document formats in general) are binary, so the version control software does not understand it and must treat it like a binary blob. Of course, this makes diffing a moot point. Then we have Open Document formats that is automatically compressed, and not easily handled by either. See? The present situation really sucks, even with open document standards.

Re:ODF (1)

Fourier (60719) | more than 7 years ago | (#15475040)

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.

You make it sound like version control is a solved problem--it's not. A typical version control system will treat a spreadsheet as binary data and do nothing more than archive copies of each version. In contrast, applying version control to source code provides a lot of semantic information from each patch, due to the use of context diffs. If you're very lucky, maybe your spreadsheet application saves in some XML-like format that you can make a diff from... but even then, typical diff tools don't have any understanding of the document model, and consequently won't provide terribly useful changesets.

There's an argument to be made for domain-specific version control that applies to this sort of document (or at least domain-specific plugins for existing VCSs). A well-designed system could potentially assist in the task of merging changes from many authors into a single document.

Re:ODF (1)

Skreems (598317) | more than 7 years ago | (#15475107)

To respond to both you and your previous sibling:

You're right, binary file formats present a problem. It still seems to me, though, that it would be simpler for the application to provide a diff utility for its custom file format, than to reimplement version control all over again. Archiving binary blobs along with change comments seems like all you'd need, provided the authority on the format could show you the differences between two documents.

Re:ODF (1)

Neil Watson (60859) | more than 7 years ago | (#15474808)

Store them in a versioning system (e.g. Subversion).

Re:ODF (0)

Anonymous Coward | more than 7 years ago | (#15475070)

Suuure, becauseusers that can't even use Excel will be able to figure out subversion.

Re:ODF (1)

biobogonics (513416) | more than 7 years ago | (#15474975)

If every change even a correction needs to be audited save-to-save of a file ... The mountains of next-to-worthless data the piles of auto-saves would generate is mind boggling.

Bring back VMS!

Re:ODF (1)

hackstraw (262471) | more than 7 years ago | (#15474976)

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?

It appears that document formats are 10-20 years away from what we want. Maybe never.

Things that would be cool are versioning like the parent mentioned. Also things like comments and the ability to do stickies or post-it style notes embedded in the document would be killer. You know like those stick on arrows that say, "Sign here".

It kills me that we are still stuck in the "WYSIWY_M_G" domain. The _M_ is for "May".

Re:ODF (1)

trigeek (662294) | more than 7 years ago | (#15474984)

One problem I see with using version control on a spreadsheet is that the data is not kept separate from the "code". Thus, every time they update the data in the spreadsheet, they create a new version. That's probably not that interesting or useful. Is there a way (outside of Visual Basic) to separate the data in a spreadsheet from the "code"?

Probably not very well.. (5, Insightful)

Ckwop (707653) | more than 7 years ago | (#15474731)

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:

"What amazed me is that the Judge really understood the GPL."

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

Re:Probably not very well.. (1)

DAldredge (2353) | more than 7 years ago | (#15474835)

At what point in the process is the soul signed over to Satan? :)

Re:Probably not very well.. (0)

Anonymous Coward | more than 7 years ago | (#15475069)

Too bad you don't have a soul or a brain (and your penis is tiny).

Re:Probably not very well.. (4, Insightful)

pla (258480) | more than 7 years ago | (#15474883)

None of this bullshit bingo that seems to be infesting every cranny of IT - clear, plain language.

I largely agreed with you right up until that line...

You compain about IT playing "bullshit bingo", compared with judges and financial guys?

IT may overnominalize, but (unlike law and accounting), we tend not to completely redefine perfectly good words for our own uses. Learning what a TCP/IP stack does takes some effort, but once you know the phrase, you know the phrase.

By comparison, every time I get into an argument with a law-geek and they play the "but that word doesn't mean the same legally as it does in English" card, I just want to serve up some serious hurting.



Now, I agree that judges and CFOs most likely understand the apparent BS they speak fluently. But don't try to complain about geek jargon as magically worse.

Re:Probably not very well.. (1)

fistfullast33l (819270) | more than 7 years ago | (#15474964)

I've met FIERCE resistance in the past from accounts trying to reform their spreadsheet ways.

Well, since they've been using these apps for more than 20 years and they resemble a balance sheet which is to accountants as computers are to software developers, it seems logical they wouldn't want to leave the spreadsheet behind. However, having used excel to do a little bit of change management tracking this year, I think it's safe to say that MS Sharepoint has versioning built-in to its interface, and you can even save from Excel and have the document update on the web. The result is that the users can continue to use their spreadsheets without even having to change their habits. I think it's a great solution, even though sharepoint is a bit overkill for just this one application. But hey, if your company has spare cash and needs a solution, this is the most seamless I've seen so far.

Two words (0)

Anonymous Coward | more than 7 years ago | (#15474735)

Excel Server.

and two more: Sharepoint.

MS has it in the bag.

One percent? COME ON! (2, Insightful)

spentrent (714542) | more than 7 years ago | (#15474749)

At the end of the day, a human is responsible for ensuring that the data in a spreadsheet is kosher. It's been that way since Bob Cratchet scribbled in his notebook under the light of a candle. If anything, this will make a company think twice before replacing an accountant with a secretary.

For one thing, don't use Excel (-1, Flamebait)

Anonymous Coward | more than 7 years ago | (#15474751)

Use something which actually calculates things correctly, not a broken implementation like Excel.

Re:For one thing, don't use Excel (1, Flamebait)

Luscious868 (679143) | more than 7 years ago | (#15474789)

Were you born a douchebag?

Re:For one thing, don't use Excel (1)

Zephyros (966835) | more than 7 years ago | (#15474836)

While I think he may have some natural talent, douchebaggery like that takes practice.

Re:For one thing, don't use Excel (0)

Anonymous Coward | more than 7 years ago | (#15474849)

or did you have douchebag thrust upon you ;-)

This should be obvious (5, Insightful)

Rude Turnip (49495) | more than 7 years ago | (#15474756)

"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.

Re:This should be obvious (1)

JohnsonWax (195390) | more than 7 years ago | (#15474981)

And with multiple sheets per workbook, why not just add validation layers to check for errors? Range checking and such is easy in Excel and it's equally easy to put a reference on each worksheet to report any errors from the validation layers. The problem I've discovered is that people don't build spreadsheets like they would applications - separating data and business logic, etc. - they just jump in and start whacking away. The downside to this is that Excel becomes rather slow as almost everything is pulled by calculation, but the upside is that the liklihood that you'll get the results you expect and not need to go trace back a bunch of errors is much greater.

Re:This should be obvious (1)

jimicus (737525) | more than 7 years ago | (#15475102)

You do this.

I do this.

If you work for a company, however, chances are their finance department does not do this.

Spreadsheet errors are a doddle (0, Flamebait)

Anonymous Coward | more than 7 years ago | (#15474767)

You should see what the "business" jackasses do with MS Access at the bank of the northern hemisphere! It's like watching three monkeys trying to fuck a football.

I despise Access...bringing poorly implemented database technology to the untrained masses.

and the error rate before the computer age.... (2, Interesting)

pxuongl (758399) | more than 7 years ago | (#15474768)

and what was teh error rate in spereadsheets back when they were done by hand?

and did this study take into account the error in their very own spreadsheets?

and how about error on the error?

and lastly, who cares? get over it. If whatever you're designing, whatever economic model, or budget can't sustain a 1% hit due to error, then you need to really rethink your design and ideas.

Re:and the error rate before the computer age.... (4, Informative)

PlusFiveTroll (754249) | more than 7 years ago | (#15474877)

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.

Re:and the error rate before the computer age.... (2, Insightful)

Morinaga (857587) | more than 7 years ago | (#15475052)

To be perfectly honest, the blame isn't with the spreadsheet in those cases. There are (or should be) a million and one checks and balances for this data. From the data entry to the manager to the controller to the CFO. Beyond that any organization that's dealing with those kinds of numbers are large enough (or should be) for a solid internal audit team. If they are a public company they will of course be subject to a public auditing firm in addition to their own interal controls. I've yet to meet a controller that didn't corraborate every major number in their various spreadsheet "books". Almost every important number is cross-referenced from other areas in other "books". It's the basic principles of balancing, something that's done for every major fortune 500 company down to the family owned bank my mother works for. These all sound like human errors, not spreadsheet errors.

Re:and the error rate before the computer age.... (1)

slackmaster2000 (820067) | more than 7 years ago | (#15475021)

The problem is that there's too much *trust* in the spreadsheet application. I see this on a frequent basis. When people do things by hand, they are already in the frame of mind that they could have made mistakes, and they're more likely to double check their work and they're not as eager to dismiss the potential for error. When people do things with spreadsheets, they're much more likely to completely accept whatever answer it spits out.

This is why we have software validation. As developers we know damn well that errors are bound to crop up because we appreciate the complexity of software. End users writing macros very seldom have this appreciation. After all, they don't check their hand held calculators for errors, why should a spreadsheet be any different?

That 1% of cells have an error figure is kind of silly, but if it's taken at face value, it has much more of an impact than you claim. So for every one hundred cells in a spreadsheet, one of them is bound to have an error. This number is low enough to where people aren't going to be on the lookout for errors, and high enough as to have a major impact on the decisions that are made based on the spreadsheet output.

I once dealt with a spreadsheet application that had an error in a macro that would only show itself on very specific data sets that only occured about 5% of the time. The numbers that were coming out *looked* about right, and with the spreadsheet able to correctly calculate values on 95% of the data coming in, it was highly trusted since every time it was verified, it turned out to be correct. Clearly the testing on it was piss poor, and it wasn't "verified" frequently enough for this error to surface. People started trusting this spreadsheet so much that if somebody did try to do something by hand and it didn't match the output from the spreadsheet, the person was very likely to just assume they had made a mistake, and the calculation was complex enough that people often did make mistakes when performing it. The end result wasn't good, and had a direct impact on quality and reporting that wasn't found for a couple of years. Nothing terribly critical, but still costly. I can certainly see how big, very costly mistakes could be made without much effort at all. What's really interesting is that two individuals performing a complex calculation and comparing results is more reliable than trusting the results from a macro which is supposedly doing everything that the people are doing by hand.

So who cares? I guess anybody using spreadsheets. Spreadsheets are a disaster waiting to happen when they're misused, and they frequently are. This isn't news, it's an old problem and I'd be suprised if this is the first time you've heard of it.

Re:and the error rate before the computer age.... (2, Informative)

Fatchap (752787) | more than 7 years ago | (#15475088)

Here's why: Section 404 of The Sarbanes Oxley Act.

Requires each annual report of an issuer to contain an "internal control report", which shall:

(1) state the responsibility of management for establishing and maintaining an adequate internal control structure and procedures for financial reporting; and

(2) contain an assessment, as of the end of the issuer's fiscal year, of the effectiveness of the internal control structure and procedures of the issuer for financial reporting.

Each issuer's auditor shall attest to, and report on, the assessment made by the management of the issuer. An attestation made under this section shall be in accordance with standards for attestation engagements issued or adopted by the Board. An attestation engagement shall not be the subject of a separate engagement.

In a nutshell if you are covered by the Act (basically you have any debt raised in the US or are listed on a US Exchange), you will need to have an external audit sign off on your internal controls around your finacial statement. This means you are asking an auditor (noramlly very risk adverse people) to say that you have a good set of internal control, including that your all your IT applications, including any spreadsheets you use. With a large ERP such as SAP you can create good controls, such as access controls like segregating duties, relatively easily. With a speadsheet this can be very hard. How do you have an good, testable control in this area? If you don't have a testable control how can you expect your auditor to sign off on it? If your auditor can't sign off on it then you are really in trouble!

Misleading Headline ? (2, Insightful)

earthstar (748263) | more than 7 years ago | (#15474777)

This story's headline makes it seem as though there is a inherent fault in spread sheet software....
Rather,its about how people make mistakes when working on spread shit.
Sample:

The researcher who worked on that report just made a mistake in the formula in the spreadsheet. He feels bad about it."

Accountability (1)

truthsearch (249536) | more than 7 years ago | (#15474779)

won't pass HIPAA or Sarbanes-Oxley auditing

Not exactly true. By themselves they won't, but within a version-controlled system it's fine. SOX has nothing to do with errors. It only has to do with accountability and the law is very vague. As long as your company can say a particular copy of a spreadsheet was digitally signed by an employee on a certain date there's nothing to worry about (IANAL, of course, but I've taken the corporate training on the subject). And every company dealing with SOX has software in place for maintaining relevant documents with employee names and times attached. So far every company I know of has custom in-house workflow software for SOX compliance, all of which allow document attachments.

Re:Accountability (0)

Anonymous Coward | more than 7 years ago | (#15474929)

True, every SOX compliant company has some sort of document management and version control. True, you can apply some sort of release management on Excel spreadsheets and password protect them.

This is not the issue. The issue is the fact that Excel is a tool and people have learned to use it. People will create spreadsheets to do simply or complex calculations to help them achieve their job objective. These documents will not go through a change process because they are not developed centrally by IT, but instead by a regular user.

The challenge is get people to understand and appreciate what SOX is about and why these spreadsheets (commonly refered to as User Developed Application) are a problem and need to handled properly.

You can either get people to submit all their Excel spreadsheets to Change Management (yeah, have fun) or build in some controls at a later process stage to cover for whatever evil can happen through the use of undocumented Excel documents.

The key is to keep the goals of SOX and the average user in mind and create a process with controls that will assure compliancy with SOX without being too much of a pain for the user or the organization. With SOX the rule is: just enough is good enough - don't gold plate your processes and rule set.

Re:Accountability (1)

Fatchap (752787) | more than 7 years ago | (#15475109)

That is fine until someone in finance creates another spreadsheet in excel that contributes to the financial statement and they don't enter it into the version controlled document managment system. Then you are in trouble!

large spreadsheets? (2, Funny)

goodminton (825605) | more than 7 years ago | (#15474795)

"...they are also used to develop many large applications."
You think they're large now? Wait 'til the million row version of Excel is phased in and you'll see some LARGE spreadsheets.

Spreadsheets fundamentally flawed (3, Insightful)

sfraggle (212671) | more than 7 years ago | (#15474799)

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.

Re:Spreadsheets fundamentally flawed (1)

DragonWriter (970822) | more than 7 years ago | (#15474955)

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.

I don't think that's really true; current releases of, say, excel or OOo's spreadsheet software are as different than, say, VisiCalc or 1-2-3 Release 1A, the first two spreadsheet systems I used as the version of BASIC that used to be bundled with DOS is from Java. Heck, the spreadsheets around now have most of the facilities you need to avoid code duplication -- just most users are either unaware of or intimidated by the programming environment for VBA/StarBasic/etc.

I don't think the problem is that there is something wrong with spreadsheets, its that spreadsheets are frequently used to develop critical applications without the attention to design for reliability, accountability, and maintainability that the use of the application demansd. This isn't unique to spreadsheets, but perhaps particularly a problem with them because users who don't have the background to understand the need for that kind of design have spreadsheet systems around that they are familiar with using for smaller tasks, and are prone to apply the same techniques to more complex and critical uses.

Re:Spreadsheets fundamentally flawed (4, Interesting)

kebes (861706) | more than 7 years ago | (#15475091)

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).

Re:Spreadsheets fundamentally flawed (1)

belg4mit (152620) | more than 7 years ago | (#15475100)

Actually, I was just reading about some alternative spreadsheet technologies the other day over at
http://cbbrowne.com/info/spreadsheets.html [cbbrowne.com] which I cam across when looking for
a console spreadsheet app (was looking for sc for linux); I'd prefer something that understands
xls so I can read frickin' attachments easily.

Real Life... (2)

thehubbell (928572) | more than 7 years ago | (#15474800)

Most companies still use databases to store their data. You use your fancy queiries to pull your data into a spread sheet from there you make a fancy pivot table that you use to prepare you financials. The point (or part of) of the audit to to follow your work papers from financial statements to your database and to reality. The auditors are likely to catch your "Material" mistakes. Sox doesn't mandate that a firm provides complete assurance that there are not material mistakes.

I wonder what the study used to keep track of data. I bet for sure that they used Excel or SPSS.

Re:Real Life... (1)

Dan Ost (415913) | more than 7 years ago | (#15474994)

Most statistical studies dump their data into a database of some sort.

However, results are very often viewed from within a spreadsheet.

How can we be sure? (2, Funny)

ehlertjd (694587) | more than 7 years ago | (#15474805)

How can we be sure that the 1% is an accurate figure? Word is, spreadsheets aren't totally reliable. :)

Thank goodness! (1)

TheOtherChimeraTwin (697085) | more than 7 years ago | (#15474811)

Studies show that most spreadsheets have critical errors in one percent of their cells

I'm glad I never let my spreadsheets grow to over 99 cells!

In other news... (1)

jambarama (784670) | more than 7 years ago | (#15474812)

users mess stuff up. And they can't get excel formulas right all the time.

Seriously, I bet you would find word processor kludges far more common (like carriage returns rather than a page break, spaces rather than tabs, and periods rather than leaders). AND word processors are used for important stuff, like spreadsheets.

And again, why are "slashdotters" responsible for what the finance guys are doing with spreadsheets? Of course they could use a versioning system, a real database, or a pen and paper, but I wouldn't take IT advice from them, I see no reason they should take auditing advide from me.

There are many... (1)

thebdj (768618) | more than 7 years ago | (#15474815)

things that can be used to control the changes made to documents and that will do enough control for most companies. At my office, we use a web-based front-end for what I am pretty sure is CVS. It manages are various projects and their associated documents. It logs updates people make and saves the old revisions as well, so we can always go back and determine what prior versions said versus the now.

Granted, this does require people to actually use the system. We have a long list of policies regarding the system, including when important documents should be updated back to the server. Considering that I have seen many places where people still insist on saving important documents to their desktop with no backups when they have ample file storage on a server that is backed up (and in one instance was doubly backed up to a remote location), I really do not expect your average user to be able to cope with this sort of system.

Auditing in Excel (4, Informative)

everphilski (877346) | more than 7 years ago | (#15474817)

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 ... )

You know the party is rocking (0)

Anonymous Coward | more than 7 years ago | (#15474825)

Man, you know that a party is just about to break out from the seams when you see a line like this in a story post:
Here are some news stories about spreadsheet errors.

Slow down, Cowboy! I don't know if my old heart can take that much excitement!

Warning: Shameless self plug (1)

Fahrvergnuugen (700293) | more than 7 years ago | (#15474843)

spreadsheets are not secure, provide no audit trail and won't pass HIPAA or Sarbanes-Oxley auditing

DocLink [port80ware.com], a document management solution that I developed helps to solve this problem. All revisions to a document are stored in a secure vault and all activity (changes, views, assignments, etc) is audited. Rather than storing files on a network share, our customers store them in a DocLink vault. Suddenly they are compliant with the auditors.

Granted, there are a lot of process improvements that need to take place, but having the proper tools in place is the first step in the right direction.

Spreadsheets are NOT made for "real" stuff (2, Insightful)

realmolo (574068) | more than 7 years ago | (#15474852)

Don't get me wrong, Excel is pretty damn great.

BUT...no business should be "running the show" on something like Excel. For serious stuff, you need a dedicated (possibly custom-made) application that does all kinds of sanity checks. A properly coded Excel spreadsheet can do a lot of that for you, but it's not really meant for that.

That's easy ... (1)

Keyslapper (852034) | more than 7 years ago | (#15474888)

> rm *.xls

Seriously though, I'm not surprised. It's not just financial spreadsheets that are going to cause these kinds of problems. All manner of financial data can introduce problems, and even things not normally thought of as financial data, like technical specs for the implementation of transactions and accounts in any revenue generating business. If the transaction has an obscure flaw that gets by testing, no amount of accuracy will overcome it.

The fact is, these data are ultimately generated and transcribed by human beings, and that's one thing humans always have and always will do very well - screw up. So you handle it the way any successful person, place or thing would. You double check, make a second person jointly responsible for accuracy and have them double check, and change the transcription process on a regular basis so that it stays intellectually engaging but not too challenging - after all, boredom with a process and complexity of a process will introduce far more errors than "bad information".

Next up: Bugs in Computer Programs are Pandemic (3, Funny)

Shimmer (3036) | more than 7 years ago | (#15474889)

Film at 11.

Re:Next up: Bugs in Computer Programs are Pandemic (1)

Aqua_boy17 (962670) | more than 7 years ago | (#15474949)

In other news: People also reported to be human and occasionally make mistakes.

Um, this is news? Using pen/paper or a PC, you still have to check your work.

In one ear... (2, Insightful)

HangingChad (677530) | more than 7 years ago | (#15474901)

How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?

I keep trying to warn my business customers, one of which uses linked spreadsheets for their quarterly accounting (backed up by an auditing firm), that linked spreadsheets are not intended as an enterprise application. But do they listen? Tried to get them to look at alternatives but they keep saying, "It does what we need it to do." But it's always breaking, usually at the worst possible time, and the auditors are constantly pointing out errors.

You can only go so far in protecting customers from their own determined stupidity.

adgadgadg (1)

matt me (850665) | more than 7 years ago | (#15474904)

Excuse me, I'm high but: Studies show that most spreadsheets have critical errors in one percent of their cells, well beyond a permissible level. What the fuck does that actually mean? A critical error is by definition badness beyond a permissible level. What is a critical error? Diviiding by zero? Oh fucking no.

And yes here's a fucking comment to you sir: this ain't On The Hour.

uh -- WTF? (0)

Anonymous Coward | more than 7 years ago | (#15474915)

See the beauty of web-based apps here?

1. Data theft concerns. Why would you use a technology that allows a user to take a full copy of the data home?
2. Concurrency issues. The worst data I've ever seen in spread sheets is "stored on the shared drive..." People save updates Friday afternoon and erase a week's worth of changes.
3. Audits. If it can exist on a single PC walking about, NO AUDIT WILL EVER COME OUT CORRECTLY! It is like voting without a paper trail. If an individual takes a copy of the data home on a laptop, they can alter anything they want (up to and including the changelog).

And lastly, FDLFSCDIE (Friends Don't Let Friends Store Critical Data in Excel). You will be beholden to a mighty company in Redmond...

Treat critical data just as you would any critical resource. Put it behind something solid and substantial, and log everybody that touches it.

Control like any other calculation (2, Insightful)

nuggz (69912) | more than 7 years ago | (#15474920)

In programming, we have learned to follow strict development disciplines to eliminate most errors.
Uhh no we haven't, most software like most spreadsheets have lots of bugs.

Personally I construct my spreadsheets in small logical steps, with comments using clearly laid out input and outputs to each formula and portion of the sheet.
I then hide these sheets or portions of the sheet.
I then run a few test cases testing nominal and boundary value performance, including invalid input (which should be rejected)

Sort of like many other programs actually.

Just say No (1)

ThinkWeak (958195) | more than 7 years ago | (#15474927)

Spreadsheets were nice once. I personally am trying to eliminate them one person at a time. In an age where reporting databases are expansive, and information is being stored on record levels - why would you opt for something manual, when you could just automate a report?

Erorrs is no newws! (1)

VincenzoRomano (881055) | more than 7 years ago | (#15475000)

Just like any other Human activity and just like any other computer program, spreadsheets tend to have errors inside!
It's not news at all!
Of course I mean both raw data errors, due to copy/paste errors, and formulae, due to thinkos [wikipedia.org] or distraction.
Humman erorrss in anny casse!

Minimize the errors (5, Funny)

MobyDisk (75490) | more than 7 years ago | (#15475009)

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!

Colored, Collated, Stapled (1)

cloudscout (104011) | more than 7 years ago | (#15475019)

Wait, do you mean to say that there is a use for spreadsheets beyond football pools and Project Managers printing schedules?

The most awesome thing about spreadsheets is... (0)

Anonymous Coward | more than 7 years ago | (#15475039)

The whiteout capabilities of remove hidden data and who changed the data. A traditional accountant was forced to draw a line through changes and initial them. The changes had to be legible even with a line through them. Now I can change thousands of Macro enabled documents in the blink of an eye and remove any hidden data regarding those changes, a mandatory software enforced view/no print or print screen policy. I can switch from LIFO, FIFO, to Weighted Average depending on it's current value to the company in the blink of an eye.
I can manipulate the entire companies bottom line in an instant.
Bwhahaha.
Signed
Accountant with a computer science degree.

Never had a problem with it. (2, Funny)

zwilliams07 (840650) | more than 7 years ago | (#15475048)

I just don't know. I've never had a problem with keeping track or accuracy of any of my spreadsheets. But then again I use Subversion to keep backups and logs on who uses what and when. Anyways back to waiting for my $2.1 billion tax return.

Yowza! (2, Insightful)

MikeyTheK (873329) | more than 7 years ago | (#15475051)

I checked out the article, and the examples, and I'm impressed. Unfortunately this is the same method used in climate modeling, economic forecasting, genetic engineering, and human drug trials.
Did you check out the original article? Were those studies cited put just in a straight table for illustration, or were they tabulated first in...a spreadsheet?
I have to say, though, that some of the studies are rather dated, and the data isn't all similar. However, the example of "whoops"'s that people have run into were frightening, and those were just financial spreadsheets.
I guess that just goes to show you that spreadsheets are good modeling tools, but they shouldn't be in the hands of everyone in the office preparing the reports. Instead the IT department should be writing permanent applications to make the computations, so then at least it's harder to make changes, so it's harder to accidentally replace a value in a cell or a formula that ultimately costs you $1 billion or so...

The question is... (1)

rthille (8526) | more than 7 years ago | (#15475053)

Are spreadsheets, on average, more or less accurate than Slashdot article summaries?
Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Sign up for Slashdot Newsletters
Create a Slashdot Account

Loading...