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

Hardware?

[punkass]

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

Probably not very well..

[Ckwop]

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

[pla]

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

[crankyspice]

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.

Re:ODF

[Skreems]

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.

This should be obvious

[Rude Turnip]

"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:spreadsheet errors are hard to fix

[morgan_greywolf]

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?

[jbeaupre]

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?

Mod Parent Up!

[mrchaotica]

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

Sum of Misspent Years

[Doc Ruby]

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