Errors in Spreadsheets are Pandemic

G Roper writes "Studies show that most spreadsheets have critical errors in one percent of their cells, well beyond a permissible level. Here are some news stories about spreadsheet errors. Spreadsheets won't protect a firm from liability when they are audited and spreadsheet errors found: spreadsheets are not secure, provide no audit trail and won't pass HIPAA or Sarbanes-Oxley auditing. How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"

Comments

and the error rate before the computer age....

[pxuongl]

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.

bad choice for applications, but...

[Beryllium Sphere(tm)]

>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

[Hao Wu]

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:Spreadsheets fundamentally flawed

[kebes]

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:This should be obvious

[Ignignot]

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 they maintain a known relationship. This seems like a ton of work to put together, but once you do it you don't have to worry nearly as much about what you are putting out as a finished product to your coworkers.

Re:ever heard of locking cells?

[baldass_newbie]

Even then you're still subject to VBA rounding issues which even most programmers are woefully ignorant of.
I mean locking cells is nice. Understanding the functions you're using is even more important.

so true

[jbeaupre]

Or to borrow a rule from finite element analysis: don't run the analysis unless you know what answer to expect. i.e. Know your stuff and don't blindly trust a computer.

Then again, CS has had a more succinct version for years: garbage in, garbage out.

Re:Probably not very well..

[Doctor Faustus]

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.

Re:Probably not very well..

[DragonWriter]

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.

Re:Hardware?

[_xeno_]

Assuming you're using Firefox 1.5 or higher (or Seamonkey 1.0 or higher?), I've created some CSS rules 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 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.)

the real problem

[petermgreen]

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.

WebDAV + SVN

[CrazedWalrus]

I think you're looking for Subversion + Apache with mod_dav_svn. You can mount your subversion repository as a filesystem, and all of your changes will be transparently tracked via subversion.

I've never done this myself, but I was looking for exactly the same thing a while ago, and stumbled on this (rather amazing) bit of setup. Perhaps someone with a little more knowledge of it can fill you in, but I know it's possible, and Google is probably your friend.

This wheel has already been invented, and extremely well by the looks of it. It's even HTTP-based, so it should work through proxies.

Sometimes, it's *not* an error...

[rmckeethen]

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.

Re:This should be obvious

[Ray Panko]

Cross-checking is nice, but the research on spreadsheet errors has shown that cross-checks have to look at every single formula and even then will miss about half of all errors if done by an individual. If you ain't doing that kind of cross-checking, you have a whiskey cure. My grandfather used to note that of all things that do not cure the common cold, whiskey is by far the most popular. Makes you feel like you are doing something. You aren't.

Anybody who trust spreadsheets is a fool

[crovira]

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