Forgot your password?
typodupeerror

Errors in Spreadsheets are Pandemic 322

Posted by timothy
from the obscure-but-significant dept.
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?"
This discussion has been archived. No new comments can be posted.

Errors in Spreadsheets are Pandemic

Comments Filter:
  • by Anonymous Coward on Monday June 05, 2006 @03:42PM (#15474706)
    "How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"

    With a pencil. haha.
  • Yeah.... (Score:2, Funny)

    by Anonymous Coward
    Back to ledgers and slide rules I say!
  • by j2crux (969051) on Monday June 05, 2006 @03:43PM (#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.
    • by BMonger (68213) on Monday June 05, 2006 @04:21PM (#15475031)
      I hear they have some great "Excel Expert Masters" in the department of redundancy department.
    • No kidding. I had to compute 5 year straight-line depreciation for accounting records going back to 1992, to insure our estimates were correct. I wasn't going to compute 10 different formulas for each year that also omitted equipment sold during those years. The result was a 20 line excel formula that required a little programming prowess. I'm the IT guy here and don't normally do accounting stuff, but it wouldn't have gotten done by our deadline without me. Most accountants do not have the programming knowledge to use spreadsheets effectively. And most schools do not teach spreadsheets at the level that is needed in the real world. It really is a forgotten realm in the industry. Oh yeah, and F1 really helps when you get stuck.
  • by yagu (721525) * <yayaguNO@SPAMgmail.com> on Monday June 05, 2006 @03:43PM (#15474717) Journal

    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.

    • by morgan_greywolf (835522) on Monday June 05, 2006 @03:52PM (#15474793) Homepage Journal
      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.

    • by jbeaupre (752124) on Monday June 05, 2006 @03:56PM (#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?
    • >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 guy
    • 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?

    • 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.
    • 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
      • the real problem (Score:3, Interesting)

        by petermgreen (876956)
        is that spreadsheets blur the lines between program (it does something), data store (it stores data), and report (it formats the data for printing) in such a way that someone eliminating unwated stuff from the report (say to make a printout neater) can in the process screw up the program.

    • by bill_kress (99356) on Monday June 05, 2006 @04:50PM (#15475308)
      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.
    • by crovira (10242) on Monday June 05, 2006 @10:09PM (#15477163) Homepage
      Spreadsheets are the triumph of the presentation of data over common sense.

      Quite apart from the fact that some of the calculations are just plain wrong (and lack precision and are subject to systemic errors, such as rounding) the 'matrix' is often abused into serving as a data table.

      Dan Bricklin may have latched onto a good thing for Apple but the unleashed idiots who used VisiCalc (and have continued through to Excel where the spreadsheet metaphor finally died,) have never had more than a clue as to what it really was.

      ANYONE who trusts it to be any more than a "what-if" exercise is an IDIOT.

      ANYONE who trusts it even as a "what-if" exercise without backing up the calculations with some sound math is an IDIOT.

      ANYONE who uses it to store tabular data is an IDIOT.

      Basically, its a fucking nuisance.

      I once had to verify some calculation routine that we were using because 'the user said it was wrong.'

      I spent two friggin' weeks going over code, trying it over and over again until I could prove that the code was accurate to fifteen digits on either side of the decimal place with ALL the friggin' math equations.

      I finally asked my boss where the error was supposed to be. How did the user know that the calculations were wrong?

      I was told to wait and he'd go and ask... The end result was that the IDIOT was actually using Excell to calculate yields on some very large and very long term bonds that they were trading.

      The IDIOT was actually expecting my software to give him the same results as Excell.

      Never mind that Excell is a fuckin' toy with rounding errors on nine digits (I said these were LARGE bonds) and the calculations used Newton's method of approximating integrals.

      I felt like killing them all for wasting my time like that.

      If they'd have told me what I was REALLY trying to prove (that Excel is a piece of shit) I could have done that in a minute just sitting down with the user and letting try things (that I knew weren't going to work) to try to prove to me that Excell was an accurate calculator.

      Thank God he wasn't trying to use it to store his data. (I'd have ripped him a new ass-hole with a rusty can opener.)
  • Hardware? (Score:5, Insightful)

    by punkass (70637) on Monday June 05, 2006 @03:43PM (#15474720)
    Ok, slightly off topic, but why is this posted in Hardware?
    • Re:Hardware? (Score:3, Insightful)

      by hackstraw (262471) *
      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.

      • 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.
      • 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.
      • Re:Hardware? (Score:4, Informative)

        by Darkman, Walkin Dude (707389) on Monday June 05, 2006 @04:38PM (#15475186) Homepage

        I also demand to put the scores near the comment title.

        I think the thinking there is to cut back on the karma whoring and make comments stand on their own merits. Also it should help keep groupthink under control, and is more indicative of the fact that moderation really only represents the opinions of one, two or maybe five basically random people out of all the thousands that read slashdot. To whit, its not terribly important.

      • Re:Hardware? (Score:3, Insightful)

        by caluml (551744)
        I also demand to put the scores near the comment title.

        Yep. On a 1600 pixel wide monitor, it's a long way to look over to see the rating of a comment.

      • Re:Hardware? (Score:4, Interesting)

        by _xeno_ (155264) on Monday June 05, 2006 @05:44PM (#15475711) Homepage Journal

        Assuming you're using Firefox 1.5 or higher (or Seamonkey 1.0 or higher?), I've created some CSS rules [xenoveritas.org] to make Slashdot use a Serif-style font and move the comment score below the title. I would have just copy-pasted the rules directly into the comment, but Slashdot's stupid broken <ecode> tag bravely mangled all attempts. So instead you'll have to live with a link to the rules [xenoveritas.org] on my personal site - no, I'm not spying on you. :)

        (Why move the scores below the title and not next to the title? Because my attempt to move it next to the title didn't look quite as nice as I'd like thanks to the current setup. At some point in the future I may create a set of rules to move it to the right of the title, but it's going to be a long set of rules.)

  • by Hawat (266650)
    Blame it on the users of course, especially the accountants.
  • ODF (Score:4, Insightful)

    by From A Far Away Land (930780) on Monday June 05, 2006 @03:45PM (#15474730) Homepage Journal
    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 (Score:5, Insightful)

      by Skreems (598317) on Monday June 05, 2006 @03:47PM (#15474745) Homepage
      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! (Score:5, Insightful)

        by mrchaotica (681592) * on Monday June 05, 2006 @03:57PM (#15474848)
        Yet another example of the truth of "those who do not understand UNIX are bound to reinvent it, poorly."
      • 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).
      • 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

      • by Fourier (60719)
        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 luc
        • 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.
          • by Fourier (60719)
            I will definitely agree that reimplementing version control within an office suite is asinine. Better to leverage one of the existing VCSs, and almost certainly end up with a more powerful and more robust solution as a result.
    • Store them in a versioning system (e.g. Subversion).
    • 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!

    • 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
  • by Ckwop (707653) * <Simon.Johnson@gmail.com> on Monday June 05, 2006 @03:45PM (#15474731) Homepage

    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

    • by pla (258480) on Monday June 05, 2006 @04:02PM (#15474883) Journal
      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.
      • by crankyspice (63953) on Monday June 05, 2006 @04:40PM (#15475205)

        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.

        The 'problem,' as I see it, is that the law demands exactingly precise use of language. (I've personally witnessed multi-million dollar litigation over the position of a comma, because it changed the meaning of a sentence.) The legal use of language tends to be unerringly precise -- as precise as, say, C demands you to be. Most English speakers use English more fluidly; think "Perl," to continue my programming language analogy.

        If you can give me a term (or terms) that you've encountered that has a 'different' legal meaning than it does in common conversational English usage, I could speak more intelligently to this point. I suspect, however, that an analysis of the true definition and etymology (check with Black's Law Dictionary -- 6th Edition if you can find it, though even the 8th has merit -- and the Oxford English Dictionary) will reveal that the legal usage is the proper usage, at least historically. (As to why legal terminology doesn't change to reflect common usage -- I'd guess stare decisis; it's not uncommon to cite to legal opinions or treatises that are a hundred years old or more; the words have to have the same (legal, not conversational) meaning today as they did then, or the whole mess gets way, way too confusing.

        • The 'problem,' as I see it, is that the law demands exactingly precise use of language.
          The law demands precision not available from natural language. Laws should be written in symbolic logic, either math-style or philosophy-style.
        • Can't resist (Score:4, Insightful)

          by isotope23 (210590) on Monday June 05, 2006 @05:22PM (#15475550) Homepage Journal
          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 [findlaw.com] trial. [findlaw.com]

        • by DragonWriter (970822) on Monday June 05, 2006 @05:27PM (#15475581)
          The 'problem,' as I see it, is that the law demands exactingly precise use of language. (I've personally witnessed multi-million dollar litigation over the position of a comma, because it changed the meaning of a sentence.) The legal use of language tends to be unerringly precise -- as precise as, say, C demands you to be.
          The problem -- and I say this as a law student and hobbyist programmer -- is that lawpeople are more able to isolate themselves from C, and that C (unlike, say, COBOL) doesn't borrow as heavily from everyday English, so that more people can avoid dealing with C's quirks than can avoid dealing with "Lawspeak", and if you run into a pile of C code, it won't look like a bunch of English that you should be able to understand. Plus, usage in law is a lot more fuzzy and subjective than that in any programming language, since courts generally are not Turing machines. (Plus, legislators don't tend to have much of a structured design methodology, or even much of a concept of a "system" that is being modified, and there is virtually no analog to "testing", so most law is really bad "code" that implementors -- courts as well as executive agents -- are forced to rationalize on the fly.)
          If you can give me a term (or terms) that you've encountered that has a 'different' legal meaning than it does in common conversational English usage, I could speak more intelligently to this point.
          There's a lot -- "person" can be a fun one. Of course, lots of terms don't just have a distinct "legal" definition, but they have very specialized and conflicting definitions in the context of particular laws, such that (to mix usages from different fields) PatriotAct::terrorism may not be defined the same way as AntiTerrorismActof1996::terrorism. Of course, this shouldn't be hard for those familiar with programming to understand, though the fact that the resolution of these kind of conflicts is often implicit, and relies on familiarity with a "code base" of law that most people aren't that familiar with, plus there is a lot of misleading information out there which says "In the law, X means Y", when that's only true in a particular context within the law, not in "the law" more generally.
          As to why legal terminology doesn't change to reflect common usage -- I'd guess stare decisis; it's not uncommon to cite to legal opinions or treatises that are a hundred years old or more; the words have to have the same (legal, not conversational) meaning today as they did then, or the whole mess gets way, way too confusing.
          Plus, like any jargon, its pretty clear -- at least moreso for the concepts involved than "everyday English" would be -- to the people who are deeply involved in it, and for them the context switch is fairly seemless, so there is no real need to change. Meanwhile, well-meaning attempts to force "common use" into legal language end up with results that confuse everyone involved, and usually result in creating more specialized legal jargon terms that are subtly different from the old ones, have to be used along with the old ones, and still don't correspond to common usage.
      • "You compain about IT playing "bullshit bingo", compared with judges and financial guys?"

        As a finance guy, I understand what you're saying -- but nuanced definitions are extremely important for communication in any specialty field. The problem is that a lot of people (especially those who are just pretending) use nuanced words as buzzwords or to sound knowledgeable, without understanding the full meaning. This dilutes the value of the very specific definitions for everyone else, as well as makes other p
    • 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

  • by spentrent (714542)
    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.
  • by Rude Turnip (49495) <valuation@@@gmail...com> on Monday June 05, 2006 @03:48PM (#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.
    • 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 pull
    • You do this.

      I do this.

      If you work for a company, however, chances are their finance department does not do this.
    • I use a different technique because a lot of my spreadsheets (each of which I could probably remake in a day at most) are too complicated to simply check all of the data behind with a calculator. It would take you weeks to do it, and the report has to go out every day.

      Instead I made it so that if the spreadsheet has a problem at all, that it fails catastrophically and visibly, and contains an indication of where the problem was. It also compares sections of the output to related other outputs to be sure
  • 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.
    • by PlusFiveTroll (754249) on Monday June 05, 2006 @04:01PM (#15474877) Homepage

      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.
      • 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 co
    • 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 dam
    • 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 audito
    • The error rate is 1%.

      Some errors are a few cents. Some are easy to see. Some are very large dollar amounts and not easy to see.

      For example- say you have a government withholding table built into your spreadsheet and "underwithhold" taxes by 200 grand during a year.

      For example- say you are calculating your profit loss but omit some major cost (like your electric bills).

      The *amount* of some errors has been large enough to destroy small businesses in some cases.

      ---

      Businesses used to spend a lot of money chec
  • by earthstar (748263)
    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."
  • 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 softw
  • "...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.
  • by sfraggle (212671) on Monday June 05, 2006 @03:52PM (#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.
    • 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 Jav

    • by kebes (861706) on Monday June 05, 2006 @04:27PM (#15475091) Journal
      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).
    • 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.
  • 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 stu

    • 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 that the 1% is an accurate figure? Word is, spreadsheets aren't totally reliable. :)
  • 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,
  • 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
  • Auditing in Excel (Score:5, Informative)

    by everphilski (877346) on Monday June 05, 2006 @03:55PM (#15474817) Journal
    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 ... )
  • 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

  • by realmolo (574068) on Monday June 05, 2006 @03:58PM (#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.

  • by exp(pi*sqrt(163)) (613870) on Monday June 05, 2006 @04:03PM (#15474887) Journal
    You should see the error rate among people who still use pen and paper to make vital calculations.
  • In one ear... (Score:3, Insightful)

    by HangingChad (677530) on Monday June 05, 2006 @04:05PM (#15474901) Homepage
    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.

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

  • by nuggz (69912) on Monday June 05, 2006 @04:07PM (#15474920) Homepage
    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 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!
  • by MobyDisk (75490) on Monday June 05, 2006 @04:18PM (#15475009) Homepage
    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!
  • Wait, do you mean to say that there is a use for spreadsheets beyond football pools and Project Managers printing schedules?
  • 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! (Score:2, Insightful)

    by MikeyTheK (873329)
    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 in
  • Are spreadsheets, on average, more or less accurate than Slashdot article summaries?
  • by PhYrE2k2 (806396) on Monday June 05, 2006 @04:31PM (#15475119)
    A speadsheet is just like a blank word document or piece of paper. You put stuff on it. Any stuff in fact. Right or wrong, it's just data.

    Doing accounting on paper leads to hard-to-read or misread digits, space considerations, inverting numbers, aligning numbers improperly and other key problems. A spreadsheet fixes many of these problems, but when it comes down to it, what's on the spreadsheet is what you put there [or what auto-correct put there]. Same thing- a calculator adds what you enter (or mis-enter). If you entered the wrong thing in a spreadsheet, at least it's easy to spot.

    The answer? check your work. Go back and verify the numbers there. Go back and make sure things balance. Have the hard receipts of what you're totalling as a good copy of anything you do.

    Why is this even a question on Slashdot? Make a formula to total and check sanity of numbers, which may help. When it comes down to it though, just take care in what you enter and make sure it's right afterwards.

    -M
  • Use a database for storing your important data and audit all updates/changes.

    The real value of spreadsheets anyway are more for formatting or prototyping. You can get any reporting you need out of a databse -- you just have to have it defined before you start.

    Spreadseets are to accounting what IM conversations are to literature.
  • by Doc Ruby (173196) on Monday June 05, 2006 @04:44PM (#15475256) Homepage Journal
    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".
  • by dpbsmith (263124) on Monday June 05, 2006 @04:48PM (#15475293) Homepage
    RISKS was talking about this in 1997, and I clearly recall discussing this with colleagues in the late 1980s, probably as a result of stories about it in ComputerWorld. Pre-Excel, for sure; it was when Lotus 1-2-3 and Lotus 1-2-3 macros written by amateur macro writers had become endemic in the business world.

    Nobody has ever solved the problem of people becoming confused by the rules as to when inserting a row or column expands the range references in formulas that refer to it. Like memory leaks or buffer overflows, everybody gets all macho and implies that competent people never experience these problems. The syllogism seems to be "Truly competent people do not experience these problems. The computer industry is populated by practitioners of average competence. Therefore, it is not a problem."

    In the computer industry, any problem that has existed for more than about five years is no longer seen as a problem and nobody is interested in solving it.

    Oh, here's the 1997 reference.

    Date: Tue, 8 Jul 1997 17:29:14 -1000
    From: "Ray Panko"


    Subject: Website on Spreadsheet Research

    In recent years, there has been a considerable amount of research on
    spreadsheets, including error rates. The Spreadsheet Research (SSR) website
    summarizes data from field audits of more than 300 operational spreadsheets
    and from experiments involving almost a thousand subjects ranging from
    spreadsheet novices to long-time spreadsheet professionals. The results are
    pretty chilling. Every study that has tried to measure spreadsheet error
    rates has found them and has found them at levels that are deeply
    disturbing. The URL is:
        http://www.cba.hawaii.edu/panko/ssr/ [hawaii.edu]

  • by rmckeethen (130580) on Monday June 05, 2006 @07:49PM (#15476552)

    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.

If at first you don't succeed, you must be a programmer.

Working...