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

Easy question.

[Anonymous Coward]

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

With a pencil. haha.

Re:Easy question.

[kiatoa]

With a pencil. haha.

Better solution. With ruby. Maybe not so easy for some but I find a ruby script easier to maintain and debug than a spreadsheet. Use the csv module and you can still use the spreadsheet for final manipulation of the data.

Re:Easy question.

[Splab]

When all you got is a hammer everything looks like a nail...

Why on earth use Ruby for doing spreadsheets? Use a proper accounting program and the problems would be far less.

Re:Easy question.

[Anonymous Coward]

Don't forget, he probably uses Ruby on Gentoo. Unless Ubuntu is now the one disto to rule them all these days? Gaa, I can't keep up on the end-all solutions of the week anymore.

Re:Easy question.

[jrockway]

> When all you got is a hammer everything looks like a nail...

When all you got [sic] is a lame catchphrase, every situation looks like a good place to use it.

You can take your hammer line pretty far, why use a computer when paper would work? Why use a calculator when perfectly good abacusses are around? After all, when all you've got is a computer, everything looks like a computer program :)

It might be weird to use ruby as a spreadsheet, but why not?

Yeah....

[Anonymous Coward]

Back to ledgers and slide rules I say!

I hear we need:

[j2crux]

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:

[BMonger]

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

Re:I hear we need:

[Anonymous Coward]

I believe you mean they have some great "Excel Expert Masters" in the department of redundancy department where the "Excel Expert Masters" are located.

Re:I hear we need:

[Sabaki]

I, for one, welcome our new Double E Masters.

Re:I hear we need:

[se7en11]

James,

What are you doing on here?? I needed that spreadsheet fixed over an hour ago! Stop goofing off and make those columns add up.

Sincerely,

Mr. Richards

Re:I hear we need:

[CrazyJim1]

I was thinking women.

Re:I hear we need:

[Anonymous Coward]

> I believe you mean they have some great "Excel Expert Masters" in the department of redundancy department where the "Excel Expert Masters" are located.

And that, also too as well.

Re:I hear we need:

[smokeslikeapoet]

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.

Re:I hear we need:

[I Like Pudding]

I had moderated this redundant, but then I felt bad. There needs to be a +1, Redundant (but only after adding +1, Flamebait).

Re:I hear we need:

[zippthorne]

Frankly, I think all the explanations should be decoupled from the point modifiers. I've seen plenty of trolls, flamebaits, and reduntants that i wish i could mod up as such, and a few funnys that really deserved to go down.

Fortunately, i believe there's a way around it. As long as one person puts in the redundant, the other mods can pile on the "underrated"s and it should work.. i think.

Re:I hear we need:

[dna_(c)(tm)(r)]

I was thinking women.

Try to focus, will you. All of us men must write our responses through a haze of testosterone...

Now, what was it you wanted to say?

Re:I hear we need:

[Anonymous Coward]

How much did your insure your estimates for? And who did you find to underwrite such a ridiculous policy?

Re:I hear we need:

[Balthisar]

The only program that I use F1 for! There are more formulae than a real programming language. I really do credit Microsoft for Excel, despite the fact that I'm a Mac user. ;-)

Re:I hear we need:

[tqk]

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

Ditto in the oxymoron dept.

Re:I hear we need:

[Flexagon]

... a 20 line excel formula that required a little programming prowess.

Exactly. Even without stepping into VBA or some other embedded scripting language, any spreadsheet beyond the toy level is basically an exercise in programming. And that is an exercise in mathematics. How many complex spreadsheet authors or users actually have the kind of experience to develop "software" like this? Very few, I'll wager. What's somewhat surprising is that there aren't more of these problems.

Re:I hear we need:

[eggsovereasy]

I just never look at spreadsheets so I can't be blamed when the shit hits the fan...

Re:I hear we need:

[adavidw]

I really do credit Microsoft for Excel, despite the fact that I'm a Mac user. ;-)

Remember, Excel actually appeared on the Mac first (if that makes you feel any better).

Re:I hear we need:

[LifesABeach]

I am amusingly reminded of those PHB's that have appeared from the vast waste land of spread sheet macros stating, "I've just done my first macro, now I know how to be a software engineer!"; The funny thing is, is that they believe it.

Re:I hear we need:

[smokeslikeapoet]

You shouldn't have been modded down. You have a valid point, but until last year I and the accountant weren't here. We're in the process of turning things around now.

spreadsheet errors are hard to fix

[yagu]

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

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

Re:spreadsheet errors are hard to fix

[earthstar]

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

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

[Red Flayer]

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

[Jason1729]

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?

Re:spreadsheet errors are hard to fix

[Anonymous Coward]

Why didn't the developers just protect the cells which were critical? It's easy enough to do. Excel is a very robust application and can be used, successfully, to model complex data and build hearty applications. However, certain rules and procedures should be followed to created said models and applications, just as when developing any application.

Re:spreadsheet errors are hard to fix

[heinousjay]

Type-o's?

Negative.

Re:spreadsheet errors are hard to fix

[bill_kress]

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.

Re:spreadsheet errors are hard to fix

[Anonymous Coward]

There is even an implementation of the elecronic tax return with Excel. Well, that is one hell of a maintenance project.

Re:spreadsheet errors are hard to fix

[mooncaine]

LOL. Good one.

Re:spreadsheet errors are hard to fix

[jacksonj04]

Because a spreadsheet isn't a program. No matter which way you slice it up, if you find your spreadsheet needing to do anything where deleting a row will nuke huge chunks of functionality, it needs to be a properly written application. Macros only take you so far.

And a database has the added bonus of easier interoperability, built in transaction logging, seperation of application from data etc.

Re:spreadsheet errors are hard to fix

[SgtClueLs]

I hate speadsheets. We have an epidemic here. The problem is, it seems that universities are teaching everything in Excel, so when people get into the real world, they use what they know. Excel. What we need to do is teach them how to, forgive me, "leverage" their ERP systems instead of exporting all the data out of the ERP System, then into Excel, to get their data. My wifes finishing up her MBA, and I keep wondering when they are going to get to the section on how to get what you need out of ERP systems instead of excel.

Re:spreadsheet errors are hard to fix

[bheer]

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.

I'm not sure how many other spreadsheet vendors are addressing this, but MS certainly is trying. You might find this whitepaper interesting reading in this regard. Scroll down to the bit where it says "How the 2007 Microsoft Office System Can Help Address Compliance Challenges", it's mostly about content management using Sharepoint 2007 and the server-side spreadsheets using the new Excel Services tool.

Re:spreadsheet errors are hard to fix

[srite]

If I can I will

Re:spreadsheet errors are hard to fix

[theBluesDog]

You don't edit the source of websites to delete chunks you have no interest in.

http://rip.mozdev.org/

You don't delete chunks of the windows code that are irrelevant and unnecessary to you.

http://www.litepc.com/

You don't delete chunks of any program.

http://www.chmaas.handshake.de/delphi/freeware/xvi 32/xvi32.htm

Re:spreadsheet errors are hard to fix

[RobertLTux]

the big problem is the "make a foolproof application and A Only a Fool Will Use it B Somebody will make a Better Fool" problem

Re:spreadsheet errors are hard to fix

[Ray Panko]

Spreadsheet errors look very much like software errors in both frequency and type. Spreadsheet errors are hard to fix in the same way that software errors are hard to fix. Cell protection is one good practice, but spreadsheet development needs good practice across the entire life cycle. Most significantly, spreadsheet developers need to spend about 30% of their time doing testing. The average is closer to 0% now. For research on spreadsheet errors, consider my website, http://panko.cba.hawaii.edu/ssr. I also have a companion website on human error, http://panko.cba.hawaii.edu/HumanErr. The human error website shows that human error is pretty much constant across application domains with comparable complexity and doesn't vary widely across people. Alexander Pope's dictim, "To err is human" is not only true. Today, we can quantify it. And, unfortunately, we can also ignore it.

Re:spreadsheet errors are hard to fix

[brickballs]

For a good laugh call (202) 456-1414

they just hung up on me. wasnt very funny at all.

Re:spreadsheet errors are hard to fix

[Balthisar]

I'm non-IT, and I'm the only person that understands the Virtues of Access (oh how I wish there were a Mac version). Spreadsheets are spreadsheets, damn it -- not an application rapid development system. If you need an application, build an application. I have the same lack of respect for Excel "applications" as I do VB applications (or in my case, "RealBasic" applications).

Re:spreadsheet errors are hard to fix

[Anonymous Coward]

Never. I am in a CMA program and the ERP systems are considered an implementation detail. Excel when you have to but mostly pencil paper and a finance calulator.

I am an 25 year IT guy working as (and training to be) an accountant. I want to use the full feature of the ERP system
and there exist people who are afraid that they will have to use something more complex than a adding machine
and a simple spread sheet. Not every is like that but the training level is limited by cost and time so getting
sophisticated (and maybe more secure) is not likely.

Re:spreadsheet errors are hard to fix

[inline_four]

And that is why I've been developing my own spreadsheet app. It's far from being a finished product and it's not targeted at Joe-the-accountant, but it does address maintainability by using XML for persistence. As such it lends itself very nicely to things like source control. It also blends features of traditional spreadsheets with object orientation and scripting to allow more sophisticated data modeling. I do admit that spreadsheets can expose too much to the user. But I look at it the same way I look at Perl allowing people to write unmanagable code -- you just have to put more thought into it and things will be okay. And of course, as others have pointed out, no one tool is right for every job. Use spreadsheets for modeling, reporting and such. Don't use it for huge data mining jobs. Not sure how I feel about complex apps though. I believe, if properly designed and tested, a spreadsheet can be a great way of representing complex relationships.

Hardware?

[punkass]

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

Re:Hardware?

[morgandelra]

that was my question also... Unless someone has written a spreadsheet in hardware... hahah

Re:Hardware?

[punkr0x]

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

Re:Hardware?

[j0e_average]

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?

[Anonymous Coward]

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?

[hackstraw]

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?

[PhoenixK7]

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?

[Control Group]

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?

[caseih]

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?

[Keeper]

The original Pentium floating point bug?

Re:Hardware?

[Darkman, Walkin Dude]

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?

[bcrowell]

Ok, slightly off topic, but why is this posted in Hardware?
Well, when you implement your spreadsheet in hardware, it makes debugging a lot more difficult. On the other hand, I find it's more fun to be able to access cell C3 directly through a soldering iron. Plus, it helps with documentation: I can put little a little sticky note on C3 saying "yearly gross" or "gambling losses."

Re:Hardware?

[metlin]

Now, offtopic!

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

Agreed!

And also making the number of comments link under each story on the home page bold, so that you don't have to find that tiny little spot to click on.

Re:Hardware?

[Anonymous Coward]

What a fucking jerk you are. You "demand," do you?

I demand that you either shut up or leave, and I doubt that your type has the ability to shut up.

Re:Hardware?

[mc6809e]

Given the content of the article, you're right. It should not have been posted to hardware. On the other hand, how do you know that you can trust your hardware not to corrupt your data?

Soft errors are a genuine problem. It's possible, though unlikely, that a cosmic ray might come whizzing by and flip a bit in DRAM, for example.

How do you know it happened? A '4' in your spreadsheet could become a '6' and you'd never know it. ECC can help, of course.

And while on the subject of trusting hardware not to muck up your data, consider important data sent over a network. This paper looked at how many damaged packets pass both the link level CRC and TCP's checksum. They found that up to 1 in 16 million packets was a damaged packet silently passed to the APP layer. That's good reason to have your app do one final check on the data before using it.

Re:Hardware?

[caluml]

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?

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

Re:Hardware?

[LunaticTippy]

It can be dozens of people. I've seen posts get modded up and down so much it's like sex.

It's somewhat exciting to have something down at -1 come back from the dead to hit +5 despite being 30% overrated. Sex is probably better, though.

Re:Hardware?

[mvdw]

I demand more than one-space tabstops. I can't tell which comments are replying to which.

Re:Hardware?

[hackstraw]

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

Stop maximizing under windows, eh? What year is this?

Re:Hardware?

[TangoCharlie]

Not only is it posted in the wrong section, and it's not news, it's a dupe too!

Re:Hardware?

[David Gould]

It can be dozens of people. I've seen posts get modded up and down so much it's like sex.

Really, it can be hundreds -- it does make sense to count all the people with points who read a post but don't decide to mod it. With so many people getting mod points, but the number of points per person still so limited, the Laws of large Numbers kick in, so the number of mods who decide to actually spend a point on a given post really should be a pretty decent indicator of the number of others who felt the same way but decided to spend their points elsewhere. So in that sense, I think the mod system actually works pretty well. The part of it that I can't be so charitable about, though, is the way it favors early posts.

It's somewhat exciting to have something down at -1 come back from the dead to hit +5 despite being 30% overrated.

I tend to get more excited when my posts trigger big sub-threads than when they get modded a lot. But then, like most of the old farts, I'm usually a late / low-volume poster. And I do see what you mean: the feeling of having said something controversial enough to draw conflicting mods is kinda nice.

Sex is probably better, though.

Yes, it is. Much.

Spreadsheets

[Hawat]

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

Re:Spreadsheets

[celardore]

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!

Re:Spreadsheets

[$RANDOMLUSER]

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

That's more true than you said. I've found through the years that accountants make lousy programmers:

"What's a corner case?"
"What's a fencepost error?"
"What's an infinite recursion?"

Re:Spreadsheets

[Hawat]

Yeah, well if you've ever tried to use an =if() in Excel for anything useful you'll know it is a leading cause of brain damage among accountants. Especially those who refer to the linked Tabs and other spreadsheets as "tables" - not to confuse cause and effect.

My company's CFO has a 15 megabyte spreadsheet (no graphics) with 50 Tabs, that controls a significant corporate function. There is only one other person in the company (me) who could even understand the mechanics, and I haven't got a clue about the undocumented relationships and dependencies.

I mean, I have a grudging admiration for someone who can make a screwdriver act like a light-sabre, but it is an incredible waste of his time and a danger to the company.

"What's referential integrity?"

He thinks crossfooting protects him. Thank God he hasn't discovered VBS. That I know of.

Re:Spreadsheets

[Hawat]

I don't mean this to be bash accountants day, but I've got another anecdote about our CFO (just posted the first) that seems relevant.

We feed an Oracle payroll system from what is basically a set of cvs data that flows through a spreadsheet. Now, Oracle is smart enough to reject overt errors when they creep in, but where do you think the overtime calculations take place? Right - in the spreadsheet. Oracle just gets a number that's been approved by the payroll supervisor's spreadsheet, untouched by human hands or minds.

The accounting department thinks of this as the way to "automate" things. What it actually is, is a method of retaining control of a process that increases the "value" of the accounting department. Why? Job protection and so that they can complain that DBA's are too expensive.

Re:Spreadsheets

[$RANDOMLUSER]

> My company's CFO has a 15 megabyte spreadsheet (no graphics) with 50 Tabs, that controls a significant corporate function.

Oh teh horror! Won't somebody puhleeeese think of teh children?

Seriously, I _do_ understand; my deepest sympathies.

ODF

[From A Far Away Land]

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

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

Re:ODF

[Neil Watson]

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

Re:ODF

[AuMatar]

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

[biobogonics]

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

[hackstraw]

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

[trigeek]

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

Re:ODF

[Homology]

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

[Anonymous Coward]

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

[Ana10g]

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

[Fourier]

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

[Anonymous Coward]

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

[Anonymous Coward]

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

Re:ODF

[AuMatar]

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

[Skreems]

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

[AuMatar]

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

[0racle]

Remember an ancient (okay, start the flamewar ;) OS called VAX

Hard to remember something that never existed.

Re:ODF

[$RANDOMLUSER]

PURGE /ALL

Is it true that VMS stands for "Vomit Making System"?

Re:ODF

[PB_TPU_40]

My editor for coding already does something like this. (SlickEdit) The key to having a decent system to do this, is have auto saves placed into a temporary file, and when you hit the save button the program asks you for revision information. Now this is only really necessary for released code/documents, to do this on a program you're working on currently that hasn't been released is pointless.

Most editors actually do this, for example if you open MS Word, type type and type, if it crashes it will bring up the last auto save, however if you close it and you say "do not save changes" the origional file is preserved.

Re:ODF

[Ana10g]

Yep. I'm stupid again... sheesh. VMS, not VAX. Dang.

Re:ODF

[Fourier]

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.

Re:ODF

[LurkerXXX]

MS has Shadowcopy now, which is a really really basic version of it.

Re:ODF

[budgenator]

yes but ODF is really a bunch of directories and text XML files so the problem of version control is actually slightly more than trivial, but not much, a couple lines of perl or python or php or bash or DOS batch ect would easily solve it.

anyways I allways thought that spreadsheets were more for playing what-if games with data than for real accounting

Re:ODF

[mrchaotica]

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.

Sounds like the solution is to make CVS pipe everything through zcat. It might as well be doing that anyway, to save space...

Re:ODF

[Anonymous Coward]

I know at least excel xp/2003 have a feature where you can add comments like post-it notes and such.

Re:ODF

[Sigma 7]

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

Even if you have a binary file, diffing two different versions can still be used to track down small changes in the binary data. While the stock version of diff does not handle this, programs similar to mdiff compare the raw bytes and check for insertions, deletions, and changes to the data (and can be configured to treat two files as completely different once a threshold is reached.)

However, the file format you wish to compare needs to be degined with mdiff in mind to be effective for tracking different versions. If adding a single character causes 25% of the file to change, mdiff is ineffective at best.

Re:ODF

[Homology]

Tracking down changes in binary data does not mean much since it's pretty hard to understand what the diff means in the first place, and merging changes is not exactly easy either ;-) As a programmer I'm used to have proper tools for version control, but when it comes to documents to be shared with non-programmes, the situation quite simply suck.

Re:ODF

[syousef]

The problem isn't keeping revisions. Yes CVS and other tools do that well for many applications. The problem is that the tools to compare say two versions of an Excel spreadsheet that live in CVS are poor. Same with any other specialised application. CVS only knows about CVS - it doesn't have tools for comparing Excel spreadsheets or anything else. Now Excel has the tools for doing so but they're not convenient. At some point it was decided it was easier to put a comparison tool of versions right into the program than to add integration for a set number of source control tools and force users to use those tools.

Put more simply, CVS is very very good for text comparison since it has good diff integration. (Consequently only bother to have a setting to keep a single backup file of your last revision in case you screw up). It's a nightmare for binary comparison of files of various formats. Think about small changes your 300 meg cad files, not just someone's toy spreadsheet. CVS and other non-application aware repository software store the whole file again (not just a couple of meg change log), and to compare the files you need to pull both versions out of CVS, then use an application capable of doing the comparison. What's more there's no possibility of undoing those changes one step at a time (whereas you can build the application to do that).

Now you may argue it makes life less convenient to have literally thousands of applications keeping their own histories, but there's definitely a need for it. I'd argue that we need better standards regarding how binary history is kept within an application.

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

[DAldredge]

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

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

[fistfullast33l]

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.

Re:Probably not very well..

[Anonymous Coward]

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

Re:Probably not very well..

[Anonymous Coward]

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

Sorry, but that is, in fact, a major legal issue that many of us non-lawyers just don't understand. Laws and precendent often define words to have specific meanings. Those meanings often have little to do with their meaning in normal usage.

For example, in most parts of Massachusetts law, the word "firearm" means what most of us think of as a handgun. Which is why you will often see the phrase "firearms, rifles, and shotguns" in Massachusetts law. When Massachusetts law just refers to firearms, that section generally applies solely to handguns, not to rifles or shotguns. Does that make sense? No. But that's how the idiots in the MA legislature defined the word "firearm" when they wrote the law. See MA General Law, Chapter 140 Section 121:

http://www.mass.gov/legis/laws/mgl/140-121.htm

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:Probably not very well..

[Red Flayer]

"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 people think all finanace guys or all IT guys or all lawyers are just tossing big words around.

So, I think the GP is correct -- don't use field-specific nuanced terms when speaking with someone not in the field. You'll be able to communicate much more clearly, since you're not depending on nuance that the listener won't get anyway.

Re:Probably not very well..

[The_REAL_DZA]

These people are very, very smart.

 
Yet O.J. still hasn't been able to locate the "real" killers anywhere on any of the golf courses he's scoured; I guess he's not "very, very smart."

Re:Probably not very well..

[smellsofbikes]

"He's not a hacker, he's a *cracker*! Hackers are the good guys!"

"Why does this ounce of gold weigh more than that ounce of iron?"

Or even: "when you push on the throttle, you're NOT actually 'giving it the gas'."

Mainstream communication relies on abbreviations, poor summaries, and outright incorrect definitions of words, that appall specialists. CS people do it, and law people do it. I think one difference might be that law people have been doing it for 2000 years, so they have a bit of a head start on specializing their language.

Re:Probably not very well..

[morgan_greywolf]

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.

We don't? So when I take my files and put them into a 'folder', I'm taking them and putting them into an actual (physical) file folder? Ooh, 'files'. There's another one.

And my 'mouse' refers to an actual rodent? Ewww. Next you're going to try to tell me that this 'page' I'm reading will actually peel off the monitor!

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:Probably not very well..

[maraist]

I'd say you are both missing something critical... Context. Firearm means the same in MA as it does in Florida, both to citizens and to the law.. The difference is context. If a lawyer in MA says to the judge or jury "this violates the firearm law XX", there is implicit context of "in this state".. To clearify context, the lawyer might say to the judge or jury "while the victim was in Delaware, he was in violation of the Delaware firearm law YY".

It's exactly the same as a local variable, local object, verses a global variable/object.. The context is often time implicit, and often times requires qualification.

In a programming language, we say "this is a keyword", or "this class extends that class".. But without knowning the particular programming language, those phrases may not have enough meaning.. A keyword in Lisp might have a completely different meaning than one in "c".

Re:Probably not very well..

[bheer]

...And if you thought Sharepoint support for Excel was cool, wait till you see the server-side Excel Services that Microsoft's introducing with Excel 2007.

Re:Probably not very well..

[Zangief]

Dude, I would like to support you, but we also use normal words to define concepts that have almost no relation to the original word.

You just need to look at Object Orientation (WTF does that mean to a non techie). We have Classes, Objects, abstract objects, etc.

Hell, in IT, I can think at least of three meanings of the word "interface", all different.

Re:Probably not very well..

[DragonWriter]

The law demands precision not available from natural language. Laws should be written in symbolic logic, either math-style or philosophy-style.

Neither style is actually all that precise in the key area for the law -- mapping abstract concepts back into concrete terms in the real world. Symbolic logic is great for dealing with abstract concepts, but that's about it. This is useful in the case of math, and keeps philosophers busy, but it doesn't really solve much in law.

Re:Probably not very well..

[Anonymous Coward]

"Firearm means the same in MA as it does in Florida, both to citizens and to the law"

No, it does not. If you had followed the link and read the MA law, you might have understood. Here's the appropriate definition in MGL Ch140 S121:

""Firearm", a pistol, revolver or other weapon of any description, loaded or unloaded, from which a shot or bullet can be discharged and of which the length of the barrel or barrels is less than 16 inches or 18 inches in the case of a shotgun as originally manufactured; provided, however, that the term firearm shall not include any weapon that is: (i) constructed in a shape that does not resemble a handgun, short-barreled rifle or short-barreled shotgun including, but not limited to, covert weapons that resemble key-chains, pens, cigarette-lighters or cigarette-packages; or (ii) not detectable as a weapon or potential weapon by x-ray machines commonly used at airports or walk- through metal detectors."

In other words, a standard length rifle or shotgun is not a firearm, according to MA law. So when Ch140 Section 128b reads:

"Section 128B. Any resident of the commonwealth who purchases or obtains a firearm, rifle or shotgun or machine gun from any source within or without the commonwealth,"

The word firearm in this sentence does not include a standard length rifle or shotgun. This becomes an issue where the MA law is different for "firearms" (i.e., handguns), than for rifles and shotguns. See Ch140 S131b, which reads:

"(b) A Class B license shall entitle a holder thereof to purchase, rent, lease, borrow, possess and carry: (i) non-large capacity firearms and feeding devices and ammunition therefor, for all lawful purposes, subject to such restrictions relative to the possession, use or carrying of such firearm as the licensing authority deems proper; provided, however, that a Class B license shall not entitle the holder thereof to carry or possess a loaded firearm in a concealed manner in any public way or place; and provided further, that a Class B license shall not entitle the holder thereof to possess a large capacity firearm, except under a Class A club license issued under this section or under the direct supervision of a holder of a valid Class A license at an incorporated shooting club or licensed shooting range; and (ii) rifles and shotguns, including large capacity rifles and shotguns, and feeding devices and ammunition therefor, for all lawful purposes; provided, however, that the licensing authority may impose such restrictions relative to the possession, use or carrying of large capacity rifles and shotguns as he deems proper."

As you can see from this text, a large capacity rifle or shotgun is not a large capacity firearm, according to MA law. This is distintcly different from the normal usage of the word firearm. I'm not familiar with FL firearms law, but I strongly doubt that FL law has the same meaning of the word "firearm."

Note that MA law is not consistent in its definition of the word "firearm." Specifically, Ch 269 S10 paragraph j uses a different definition:

"(j) Whoever, not being a law enforcement officer, and notwithstanding any license obtained by him under the provisions of chapter one hundred and forty, carries on his person a firearm as hereinafter defined, loaded or unloaded or other dangerous weapon in any building or on the grounds of any elementary or secondary school, college or university without the written authorization of the board or officer in charge of such elementary or secondary school, college or university shall be punished by a fine of not more than one thousand dollars or by imprisonment for not more than one year, or both. For the purpose of this paragraph, "firearm" shall mean any pistol, revolver, rifle or smoothbore arm from which a shot, bullet or pellet can be discharged by whatever means."

Re:Probably not very well..

[Watson Ladd]

Same thing in physics. Work and energy have very precise meanings in physics. A lot of 1st year students don't get them and get tripped up on questions that require you use the strict meaning on tests at my school.

Re:Probably not very well..

[Geminii]

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.

And as a result, no major business has ever collapsed from financial misdirection!

Two words

[Anonymous Coward]

Excel Server.

and two more: Sharepoint.

MS has it in the bag.

One percent? COME ON!

[spentrent]

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.

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

[JohnsonWax]

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

[jimicus]

You do this.

I do this.

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

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

All that VB?

[HDDdude]

All those VB macros and your telling me there are errors? NO Way! Couldn't be!

Spreadsheet errors are a doddle

[Anonymous Coward]

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

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

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

[PlusFiveTroll]

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

[slackmaster2000]

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

[Morinaga]

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

[Fatchap]

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!

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

[cheier]

That 1% has nothing to do with the percentage of financial loss or gain. That has more to do with the amount of errors on a spreadsheet. The value of that error could mean anywhere from hundreds of dollars of loss or gain according to the spreadsheet, to billions, depending on how critical the locations of that 1% of errors are.

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

[geobeck]

Section 404 of The Sarbanes Oxley Act.

99% of computer geeks can't find that section.

/obligatory

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

[Fatchap]

Why can't they use a search engine?

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

[geobeck]

Read the section number again, and you'll see why computer geeks assume it cannot be found. :)

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

[Fatchap]

Oh man, how did I miss that reference, mod me down as dumbass ;-)

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

[Fatchap]

Oh Man, how did I miss that reference? Mod me as dumbass ;-)

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

[dogbowl]

I'm currently auditing the IT operations of a large insurance company .. and guess what - Their data is all fed into custom built applications and SAP and then ultimately dumped into spreadsheets on open network shares for the officers to use and "clean up".

And guess what? They're failing!

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

[Maxo-Texas]

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 checking their results (and there were *huge* errors but businesses were a lot tougher in the old days because the margins were not so thin). The problem with spreadsheets is that people start to trust that they are correct and never perform a reality check until it is too late.

Misleading Headline ?

[earthstar]

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

[truthsearch]

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

[Anonymous Coward]

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

[Fatchap]

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!

Re:Accountability

[truthsearch]

No, then they're fired for not complying with the corporation's SOX rules.

Re:Accountability

[Fatchap]

So actually it is the rules and policies that are important not the software or the digital signatures you use.

Re:For one thing, don't use Excel

[Luscious868]

Were you born a douchebag?

large spreadsheets?

[goodminton]

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

Re:large spreadsheets?

[newt0311]

NO KIDDING. Talk about using the wrong tool for he task. Spreadsheets were never designed for data storage, databases were (and still are). Spreadsheets are designed for calculation, simple quick simulation type calculations. If the spreadsheet is going into more than ~100 lines, it si time to move to a database or dedicated application. I am ammazed people still use spreadsheets because with languages like python, scilab, bash etc. as in specialized scripting languages, designing algorithms is so easy that the calculation function of spreadsheets becomes useless and the data storage function was crap to begin with. So... why do people use spreadsheets anyway.

probably something with the learning curve but you'd think that accountants would be trained to use the best tools for the job (ie. data storage and management and thus databases).

Re:large spreadsheets?

[fishbowl]

>Spreadsheets were never designed for data storage, databases were

Spreadsheets don't save persistent data? Databases magically ensure data integrity?

Spreadsheets fundamentally flawed

[sfraggle]

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

[DragonWriter]

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

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

[belg4mit]

Actually, I was just reading about some alternative spreadsheet technologies the other day over at
http://cbbrowne.com/info/spreadsheets.html 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.

Re:Spreadsheets fundamentally flawed

[Anonymous Coward]

Couldn't agree more.

Spreadsheets boil down to 4 different parts.

- Data Input
- Data Store
- Data Manipulation
- Data View

In otherwords spreadsheets are just a form of database application. A spreadsheet's strength as a tool is its simplicity to grasp its use and function. People use spreadsheets over database apps such as Access because the latter require a much greater investment in time to learn and use than the former. The barrier of entry is too high.

However it must be possible to retain the simplicity of a spreadsheet whilst giving it more of the features that Access has. With a concept of views for the 4 different parts a spreadsheet would become a much more powerful and robust tool. There would be a clear separation beteen, entering data, manipluating it and viewing the results. This would reduce the number of casual errors casused by users changing parts of the spreadsheet that doesn't relate to their work such as deleting rows that containe formulas in, etc. Data could be seen and manipluated in terms of sets, which would allow you to write one formula and apply it to a set of data, rather than having to copy and paste the formula.

The spreadsheet paradigm is ripe for an update.

Re:Spreadsheets fundamentally flawed

[ps_inkling]

If you can get a user to understand the difference between absolute and relative cell addressing, the problem with editing individual formulas is made a copy-paste task. Instead of typing (or clicking) all the cell references each time, you write the formula pattern and fill it into the appropriate cells.

I agree that the single-line editor for spreadsheet formulas is archaic. Please give us indented spreadsheet formulas. The parenthesis highlighting is nice, but not enough.

If you want spreadsheets as a "rigorous programming environment," please use Visual Basic for Applications. It's there, and will do many nifty tricks. You even get the benefit of making "compiled" Excel spreadsheets.

Myself, I used an embedded Excel spreadsheet within a VB application to generate charts. Change the numbers in the spreadsheet (via VB), and the charts redrew with new numbers. Much easier to use than the Chart OCX.

Somewhat offtopic, but this is /. -- back in the mid-80s, there was a database program for the Mac called Double Helix. It used drag-n-drop, flowchart shapes to program how the database worked. As a programmer, it was frustrating to get the drags and drops of the arrows to work properly. As a non-programmer, users were still just as confused as to what the shapes were supposed to do.

Re:Spreadsheets fundamentally flawed

[kebes]

If you can get a user to understand the difference between absolute and relative cell addressing, the problem with editing individual formulas is made a copy-paste task. Instead of typing (or clicking) all the cell references each time, you write the formula pattern and fill it into the appropriate cells.

This is precisely what we do now, and it's inefficient and error-prone. We make nice formula with mixed relative/absolute addressing, and then copy it down the row of hundreds of cells. It works, but it's error prone. It happens very frequently that someone will forget to copy down the row (only the first cell is updated), or forget to make the change somewhere else (think of spreadsheets that have the same formula appearing in different rows, blocks, or worksheets). Expecting the user to remember to paste every place where a formula appears is silly. It should be easy to define some kind of function on-the-fly and use it wherever needed.

An added benefit is that it actually preserves the "play around" usefullness of spreadsheets. If you can modify a single equation, and see your whole spreadsheet (and graphs, etc.) update, it is much easier to get a handle on what's going (versus copying and pasting updates all over the place, and then comparing the before and after).

Re:Spreadsheets fundamentally flawed

[Anonymous Coward]

Check out Quantrix: http://www.quantrix.com/

Re:Spreadsheets fundamentally flawed

[petermgreen]

note that in excel you can write functions in VBA and use them as worksheet functions.

Re:Spreadsheets fundamentally flawed

[Flossymike]

An interesting observation. Would it then be a better idea to 'drag down' a 'is calcualated in the same manner as'?

for example could Exclel or Calc drag down ={a1} ?

Just an idea

Re:Spreadsheets fundamentally flawed

[Flossymike]

As a clarification the above example would be for a relative call and comething of the nature of ={a!1!} would be used for absolute resolution.

Just for 1c woth

Re:Spreadsheets fundamentally flawed

[Flossymike]

Good interesting thoughts. Glad I read them.

What I would same in responce is that if you have an experinced vba programmer then surely they normally write their spreadsheets as vba code and generate the spreadsheet it self on the fly. Much as my inherit distrust of excel spreasheets go, surely most of the points made are already potentially addressed? It would take it a step (much) futher to generate good vba code from a visually designed spreadsheet into vba code.

As far as debugging existing styles of spreadsheets go, see my previous post for a simple example (my appologies if curly brackets are used in other ways in excel)

Re:Spreadsheets fundamentally flawed

[TERdON]

Another idea - what about introducing the complete concept of functional programming to spreadsheets? AFAIK you can only use predefined functions in all of the spreadsheet applications that I know of, or you have to go with some kind of scripting (which really doesn't fit into the spreadsheet paradigm of computing).

The concept simply would be allowing you to implement your own backside functions in a simple imperative language (BASIC-like, c-like, Pascal-like, possibly choosable, functional languages would be possible too if you like those). This would be done outside of the actual spreadsheet in a special tab or something.

Basically, you get the possibility to define MySuperCoolFunctionThatDoesLoadsOfThings() and use that instead of writing 2-row unoverlookable formulas in each cell.

Re:Spreadsheets fundamentally flawed

[Anonymous Coward]

You tend to do both.

In general, you get Excel to do as much work as possible as VBA is often much slower. For example, one sheet I have needs to summarise data in different ways, and then have the results shown all nice and pretty.

You use pivot tables to crunch the numbers, but you use VBA to create the pivots in the first place, and to copy the data afterwards.

Real Life...

[thehubbell]

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

[Dan Ost]

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?

[ehlertjd]

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

Re:How can we be sure?

[Anonymous Coward]

It's OK, I read it on slashdot.

Thank goodness!

[TheOtherChimeraTwin]

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

[jambarama]

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

[thebdj]

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

[everphilski]

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

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?

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.

Re:ever heard of locking cells?

[bob_herrick]

Do you have any idea how long it takes a reasonably competent pratitioner with Internet access to unlock even a password protected Excel spreadsheet. I have seen it done, start to finish in under a minute. If you want it to be auditable and dependable, find another medium than a spreadsheet. Spreadsheets are about doing stuff quickly; apps written in spreadsheets have too much opportunity to run wild.

Re:ever heard of locking cells?

[jbeaupre]

Duh! I've done it too. But if someone is going to take the time to do that, they might just have enough sense to not delete rows willy nilly.

I've also put the password right on the spreadsheet for others, right under a notice not to do certain actions or risk certain doom.
It's all about risk management. But the original post was just bitching about users deleting when 99% of that could be prevented with just a moment's effort.

Re:ever heard of locking cells?

[smokeslikeapoet]

My dad uses an excel password cracker to routinely fix errors in locked formulas because the IT department has a one-week turn around on such problems. He hasn't gotten in trouble... yet.

Re:ever heard of locking cells?

[cahrichak]

I'm a computer geek, but my company pays me to be an accountant. :-P On many occasions I've had to protect sheets/cells from users who inadvertently delete formulas. We've also gone as far as color-coding the cells that ARE allowed to be written in by the end-users. Editing formulas seems to be far less of a problem than the "oops-deletion" in this case, anyway.

Re:ever heard of locking cells?

[Balthisar]

Well... any time I get a locked spreadsheet, I hit Ctrl-A, Ctrl-C, Ctrl-N, Ctrl-V, and work from there. Not a whole lot of protection.

Granted, the problem is that most of people that portend to develop "applications" with Excel don't know which cells to protect and not protect, causing my irritation with their stupid lock policies. This kind of harkens back to the RTFA wherein users break these so-called applications. I'm sorry -- write your application in Delphi or Cocoa; a spreadsheet is a document and maleable.

Re:ever heard of locking cells?

[Anonymous Coward]

You wouldn't let non-programers alter code, why would you let them alter the spreadsheet?

The problem is the users who think they know how to use spreadsheets when, in fact, they do not. Have you ever tried telling your boss that they can't have the password to the protected cells in a spreadsheet? I have a supervisor whom you would think had invented Lotus 1-2-3 herself to hear her talk. I tried saying I forgot the password once and she spent two hours recreating the whole spreadsheet by hand. Then she starts deleting formulas to manually enter numbers because the formula's numbers "weren't right". She cannot investigate the actual cause of the error because she does not have the expertise to understand what the formula is doing, and even if she did she doesn't really know what she's doing in the first place. It gets even better on the spreadsheets that I had to write custom VBA functions for. The whole point is that spreadsheets aren't the problem is idiots. GIGO always applies.

You know the party is rocking

[Anonymous Coward]

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!

Re:For one thing, don't use Excel

[winkydink]

What color is the sky on your planet?

Re:For one thing, don't use Excel

[Zephyros]

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

Warning: Shameless self plug

[Fahrvergnuugen]

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

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

Re:Warning: Shameless self plug

[Fatchap]

Actually I think the first step is understanding what the problem is and the working out how you can fix it. If you have the right tools in place first it is normally down to luck. More time than not you end up with a hammer looking for a nail to bang in when a screw would work better.

You don't need a DMS for this, you just need to look at the spreadsheets, analyse the logic and lock down the cells with formulae in. Then you need to restrict the access to it, only allowing authorised people to save changes to the data part of it. It can all be done within Excel and Windows.

Interactive fault localization?

[Anonymous Coward]

Maybe they need these error-checking techniques for spreadsheets (see also here)?

Mod Parent Up!

[mrchaotica]

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

Re:Mod Parent Up!

[susano_otter]

"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:For one thing, don't use Excel

[Anonymous Coward]

or did you have douchebag thrust upon you ;-)

Spreadsheets are NOT made for "real" stuff

[realmolo]

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.

Re:Spreadsheets are NOT made for "real" stuff

[Anonymous Coward]

As much as I would love to agree with you the vast majority of small businesses don't have the dedicated IT staff to come up with a dedicated application so they use what they can, which is the universal language of Excel. These businesses then grow bigger and when they finally do get the IT staff to work on things Excel or some other spreadsheet is so built into the infrastructure you don't have a hope to remove it no matter how hard you try.

Re:Spreadsheets are NOT made for "real" stuff

[King_TJ]

I don't think many businesses out there really *are* trying to run the whole place on Excel. Where it becomes more of a problem is by the nature of it being a format that's compatible with a lot of other software packages.

If you're trying to get data exported from proprietary app A and into proprietary app B, Excel is often used as the "man in the middle" format that both apps know how to work with.

Not only that, but in these situations, you often have some additional manipulation of the figures that needs to go on during the export/import process, so "app B" properly represents some of the data. (EG. Perhaps one program calculates taxes differently than the other, so you need to modify the exported tax numbers so they'll show equivalent amounts in the other program?)

Short of writing custom software for the task (probably the best solution, but also the most expensive one - and toughest to get approval for), custom macros in Excel templates are the likely solution.

Re:Spreadsheets are NOT made for "real" stuff

[totallygeek]

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.

I believe the point is that many business people do use something like Excel instead of these more serious, well-checked, supported programs. I have seen more than a few people working for banks singing the praises of something whipped up in Excel, saving their department thousands of dollars, only to find out later that some numbers are off. I know of one business that was using Excel for all their inventory instead of springing the money for a DB (even Access). Guess what -- data loss and no transaction accounting! It cost them a small fortune to redo the entries in a decent system.

As more people complete a 4-hour training session on Excel, or share a macro-filled spreadsheet, you end up with bad, unsupported applications. The idea that each employee of a company should unilaterally generate 'applications' is rediculous. But, more and more businesses are embracing this and the result is data being saved to a non-backed-up My Documents folder, work being incorrectly generated due to bad formulas, and a myriad of other issues.

Don't get me wrong: spreadsheets are a great asset to running a business. But, they are not a decent means to shortcut expenditures. And, just because someone figures out a few formulas, that doesn't mean they can design a fool-proof application.

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:bad choice for applications, but...

[mrchaotica]

Damn, now that's what I call job security! That one guy who understands it could ask for a half million per year, and they'd have no choice but to pay it.

Re:bad choice for applications, but...

[I Like Pudding]

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.

That isn't anywhere near enough to comply with SOX. I had my prod DB write access taken away, so, any time I need to run a DML for some random bit of data that needs changing, I need to open a ticket, spam both the DBA group and my boss, and have him run the dml. The DBAs then audit it later.

There goes my evil genius "transfer the rounded off parts of transactions into my account" plan.

You think that's bad?

[exp(pi*sqrt(163))]

You should see the error rate among people who still use pen and paper to make vital calculations.

That's easy ...

[Keyslapper]

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

Re:That's easy ...

[fishbowl]

"Seriously though, I'm not surprised. It's not just financial spreadsheets that are going to cause these kinds of problems. "

Am I the only one who remembers the bad old days before even Visicalc? People honestly think the current systems are more error-prone than their predecessors?

Re:That's easy ...

[colinrichardday]

Shouldn't that be del *.xls?

Re:That's easy ...

[epp_b]

> rm *.xls?

...

Shouldn't that be del *.xls?

For goodness sake, I expect Slashdotters to be held up to a higher standard!

# cd /
# rm -r *.xls

Re:That's easy ...

[colinrichardday]

But if one were running Linux/BSD, one wouldn't have *.xls files in the first place, and rm is not a Microsoft Windows command.

Re:That's easy ...

[nonlnear]

If one stores critical data on a linux file server...

Re:That's easy ...

[colinrichardday]

Since when do *.xls files constitute critical data?

Re:That's easy ...

[epp_b]

Ahem... http://www.openoffice.org/

Re:That's easy ...

[RevDobbs]

And one never use WINE on their *nix box, nor do they do ever SMB file serving...

Don't get me started on all the people that don't install Cygwin on their Windows machines.

Re:That's easy ...

[nonlnear]

Depends what's in them, and how stupid the data owner is.

I'd bet there's at least 100 000 companies in the USA that have an .xls file somewhere that has a list containing (at least some subset) of their customers' critical financial data. It may not be officially sanctioned, but somebody somewhere just needed to do a rough estimate for some middle management meeting.

Next up: Bugs in Computer Programs are Pandemic

[Shimmer]

Film at 11.

Re:Next up: Bugs in Computer Programs are Pandemic

[Aqua_boy17]

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

[HangingChad]

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.

Re:In one ear...

[fishbowl]

"Linked spreadsheets are not intended as an enterprise application. But do they listen?"

Of course not. You know why? Because you cannot tell them that what they are doing is illegal, that their data will not be accepted by their accounting firm, that it will not be accepted by the tax agencies of the government, that no excuse will be accepted, that the company will be de-listed as a result, or that they will be personally held liable for tax fraud as a result of this.

Why should they listen to your *opinion* when at the end of the day, to them, the system works?

At the end of the day, the company execs aren't going to prison, the corporate assets seized and liquidated, etc.

adgadgadg

[matt me]

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?

[Anonymous Coward]

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

[nuggz]

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

[ThinkWeak]

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!

[VincenzoRomano]

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 or distraction.
Humman erorrss in anny casse!

Minimize the errors

[MobyDisk]

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!

Re:Minimize the errors

[Cousin Scuzzy]

Why worry about adding all those extra cells? The "3 sheets" state can be achieved simply by leaving work and heading to the nearest bar.

Re:Minimize the errors

[Incadenza]

Man, haven't you read the the freakin' /. summary: spreadsheets have critical errors in one percent of their cells, !!!

In other words, if you increase the size of the spreadsheet, you will increase the number of cells with critical errors as well. Since the sheets you added are non-functional, they can't have critical errors, so all those new critical errors will be in your existing code. Way to go.

Only by inserting more than 1% of critical errors in certain well-known pieces of your spreadsheet, you can lower the amount of errors in other places of the sheet. Would have explained this better if I had the time, but I just have to talk to some financial people around here. The salary administration needs debugging, and they don't even know it yet.

Re:Minimize the errors

[Anonymous Coward]

don't worry, it was just another error in his spreadsheet. instead of copying sheet1 he can add sheets of 50x50 "1"s. that way they're surely correct.

Colored, Collated, Stapled

[cloudscout]

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

[Anonymous Coward]

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.

[zwilliams07]

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!

[MikeyTheK]

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

[rthille]

Are spreadsheets, on average, more or less accurate than Slashdot article summaries?

Paper is different? Check your work!

[PhYrE2k2]

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

reinvention...

[mengel]

Actually, the people who really do understood Unix, reimplemented it well.

Spreadsheet Errors

[Duaneg7872]

This is a complex problem. People prefer spreadsheets over almost all other types of applications because they can see what happens. In other applications, such as ERP, the numbers go in one side and financials stream out the other. To the average user, most applications are a "black box". I would suggest a size limit on spreadsheets, the huge multi-gigabyte ones are the real liability. If more complexity is needed, use a new spreadsheet for each step, and tie each one back to some benchmark number that is NOT spreadsheet based.

Simple - don't use spreadsheets

[poopie]

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.

Re:Simple - don't use spreadsheets

[slackmaster2000]

If you're an application developer, yeah that makes sense. There is a very narrow range of applications that are well-suited to be implemented on the framework of an existing spreadsheet application. The rest of the time it seems like developers are just trying to take advantage of the spreadsheet's interface, but it just ends up creating a whole lot of difficult macro work, especially if the macro code is supposed to interface with some external program. It all feels very shakey once you get more complex than the most trivial of projects. And users often hate it anyhow because installation is always more difficult, upgrading the spreadsheet application can break things, and it's hard to troubleshoot when you're not sure how things are interacting under the covers.

If you're an end user, however, creating a database solution can be much more difficult than creating a spreadsheet, especially for those who are unfamiliar with the concepts. Besides, a person using MS Access is just as dangerous as one using Excel when they start pumping out macros.

Also, if you've ever seen someone who is very proficient at the use of spreadsheets, they can do some amazing things very quickly. I've noticed that these people tend to use spreadsheets correctly, however, as a sort of "super advanced" calculator. They don't use them to keep track of everything that can be put into tabular format.

Been there, seen it, done it...

[Anonymous Coward]

I deal with this every day. It doesn't matter how many times you tell someone 'Excel is not a database', 'there are better ways to run an SQL query and return data to a dynamic report', etc., tech-nonsavvy managers do not listen.

What to do?

IMHO - take your most-competent web developer, or hire one if you don't have one, and sit them with the finance people who do the most Excel-abuse. Encourage them to discuss tasks and possible solutions. Then, get webdev to build small, fast, custom apps to create the output required. Think a small app in Ruby using Ruport or similar tool pulling data from the accounting/whatever DB. Usually, an Excel sheet that is part of the audit trail (I am an auditor) is a 'standard', rather than some sort of ad-hoc POS, so this would work. Plus, users couldn't mess with the formulas, etc., which is frequently where things go wrong.

I'd rather pay someone to write a well-engineered, flexible solution once that my analysts could use without breaking than have to maintain gigabytes of .xls files, all with holes and plugs in them, and worse, getting *recycled* - i.e., "I did some similar analysis for client XXX, I'll just copy their file and adapt it... what's the worst that could happen?"

Just my $.02 - as a geeky financial auditor.

Posting AC from work - OSXCPA is userid.

Science...

[electrosoccertux]

Remember from science class, accuracy and precision.

The secretary's account of travel expenditures is accurate. But the boss' is only precise. :o

A 1% error in a $2.5 billion transaction...

[Anonymous Coward]

A 1% error when you're dealing with a $2.5 billion transaction is still $25 million. In most industries, a $25 million shortfall or overpay isn't a small amount.

Even when you're only dealing with $1 million, a 1% error affects $10000. That's enough to pay certain workers for four months. And if it were going to you as an individual, I don't think you'd be happy if you were short-changed $10000 due to a mere 1% error.

Spreadsheets are a write-only programming language

[AJ Mexico]

Spreadsheets are a powerful programming environment. But, like some other "visual" environments, there is no way to get a listing of your code. You have to navigate around through all those rows and columns, looking at formulas one-by-one. The old, original idea of having the source code in human-readable form in a text file, where it can be reviewed easily, and checked by utilities like lint, has never been surpassed. Errors are probably no more likely in a spreadsheet than in other programming languages, but in textual languages, it is easier to find the errors. There are a few things that would help this problem, IMO: 1.) Assert statements for spreadsheets. Users can accomplish this with existing tools. Example: Generate an error if the row-wise sum isn't equal to the column-wise sum. 2.) Pre-defined spreadsheet templates for solving common types of problems. Most spreadsheets reinvent the same wheels over and over again, frequently using poor programming practices. Just like you get templates with Microsoft word for common memo formats, you should get templates with Excel for common spreadsheets. These should come with *gasp*, documentation, describing how to customize them.

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

Re:Sum of Misspent Years

[jjohnson]

Part of the reason spreadsheets are so commonly used is that they're flexible. The term "worksheet" has lost all meaning, since the fact that it's on a computer gives the false impression that it has some kind of authority.

At my last employer, finances were worked out on spreadsheets, and once approved, the totals were entered into a financial system that held them as approved figures requiring various permissions to change. Continuing to run your business on spreadsheets is like leaving your screenplay as a bunch of sticky notes on a whiteboard, never committing it to an approved draft.

Turning Excel into a frontend for a database (which we also did) has the drawback of killing the flexibility of the working surface that makes it so attractive in the first place. This isn't an interface issue, it's a procedures issue, and spreadsheet mistakes are nothing more than adding machine mistakes writ large. The solution to be had is a policy declaring spreadsheets as by definition non-final, and having your database form be the authoritative record.

Re:Sum of Misspent Years

[Doc Ruby]

Well of course any org (or person) needs a policy on record authority, on top of any tools - whether they're software, paper or wetware. Even using an accounting firm is no longer any guarantee of accountability, as Arthur Andersen and Enron proved for an entire industry.

There's no reason that turning Excel into merely the GUI for an RDBMS has to kill its flexibility, if the implementation is done well. Many have tried, a few have succeeded at least partially. If more development had gone into the scriptable RDBMS path than into reinventing incompatible macros and rebuilding "DB versions", we'd probably have a client/server system as flexible, manageable, and powerful as Excel seemed 12-20 years ago. But in today's terms. And the "best practices" to go with using its features.

Re:Sum of Misspent Years

[maxume]

It wouldn't help any if you stuck a database behind the spreadsheet-- people don't use excel as a table, they use it as a giant piece of paper. Crazy, fragile shit would just be stored in a more bulletproof format.

Re:Sum of Misspent Years

[Doc Ruby]

Databases have better tools for supporting even that crazy, fragile ("arbitrary, unstructured") shit. That's why an architecture that makes Excel just the GUI for a database helps reduce the craziness and fragility. While leaving a simple, familiar spreadsheet GUI off the database makes it harder to use. Making them separate, usually unrelated apps has weakened both.

Re:Sum of Misspent Years

[Anonymous Coward]

But Excel is (or can be) a frontend for a DB. That's what the Data|Get External Data menu is for. It can get data from a DB, a text file, or even a web page.

Once you've pulled that data in, though, you have to manipulate it. You start adding formulas, graphing things, rearranging, trying what-if scenarios, and so on. The errors being discussed aren't from incorrect source data, they're from incorrect programming of the spreadsheet.

For example, let's say you are trying to estimate a building job, so you import all of the necessary parts, labor, and permit costs into your worksheet. You then put in a SUM() formula to come up with the total. Once it's approved, you insert a row to account for all of the various bribes and kickbacks that will need to be taken into account for your bid. But let's say you forget to change your SUM() formula to add in the results from the new row, and now your bid is $250,000 too low. Or perhaps you remember to change the formula but you accidentally forgot the minus sign when you typed it in, and now your bid is off by $500,000!

What it comes down to is that spreadsheets are usually one-off programs -- they are the quick-and-dirty shell scripts of financing, wich accountants being the sysadmins of the financial world. And since all programs have bugs, all spreadsheets have bugs, unfortunately.

dom

Re:Sum of Misspent Years

[Doc Ruby]

The problem is not what Excel and DBs can do, but rather their overall design, their presentation as products. Read the first message I posted, rather than just the response to the feature quibbles. If Excel were packaged with a DB that took over all functions except GUI, the "Excel culture" would be stronger, and the quality of code in spreadsheets would be a lot higher than the article we're discussing reports.

Try using a tool such as TK Solver

[Anonymous Coward]

We use TK Solver, as it makes for easy auditing of all equations/formulas! Think of it as a highly-structured spreadsheet program. It even allows for seeing equations in mathematical notation. We use a second product when we need to use MS Excel called MathLook for Excel, which also helps to audit spreadsheets and rename spreadsheet cels for easily understanding what is where in the spreadsheet.

But what if you...

[octaene]

This sounds completely tongue-in-cheek; it's totally a serious question. What if you have spreadsheets stored in a database? Would that pass muster for audits and reviews?

spreadsheets at financial institutions

[toolbar]

At work I look after a number of databases for financial instruments: options, certificates, bonds, funds, ...

When we started to collect data for our funds-database about two years ago I was shocked that at most funds-companies a central spreadsheet is used as the main data-source for all information. Companies that invest billions of Euros rely on an Excel-sheet lying on a central fileserver for most of their data-housekeeping.

Well, not all the companies work this way. There is even an organization that tries to standardize an xml-schema for the exchange of funds-data (http://www.funds-xml.org/) - but of course no one really uses it.

Is that frog boiled yet?

[dpbsmith]

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/

Calculations with Excel == Disaster

[labalicious]

Most of my job entails that I can handle Excel spreadsheets. Tracking money, managing accounts, building reports, etc. I learned early on that you can't always depend on what Excel spits out. More and more, I find that it acts like a scientific calculator. If you have ever done expenses or used Excel to calculate anything monetary related, you'll understand what I mean. Excel keeps track of fractions of fractions of a cent unless you tell it to round up to the nearest 100th of a cent. This is a common mistake that I've seen repeated a dozen times. I've seen quotes go out to a customer with the incorrect pricing because of someone's blind faith that the formula is calculating the correct dollar values.

However, when you actually use a real calculator and start adding and multiplying up these values, you will see how incorrect it can be. Though, technically, it is correct. If you want to prorate a 12 month price to 6 months and convert it to CAN from USD, you're going to see what I'm talking about. You'll get a unit price of 45.344632524464 and excel will happily tell you that it's only 45.34$. Have fun telling the auditors why your quote is over 10,000$ off. :o)

Budgeting

[Chazmyrr]

This is a hot topic in many companies. At my employer, one of the key areas of concern is the use of spreadsheets for budgeting. Currently spreadsheets are used to forecast the variable expenses for much of the business. The numbers from the spreadsheets are loaded into a data mart and from there into a series of warehouses. Each warehouse contains less detail about more of the business than the one before. A project is in place to replace the spreadsheets with a financial planning application from one of our vendors.

I suspect that this project is doomed to failure. A number of similar efforts using various products were undertaken in the past and all failed. There are many reasons why they all failed and most had nothing to do with the actual product. The biggest reason was that the decentralized system using spreadsheets was far more agile. Centralizing the models eliminated spreadsheet errors but took far too much time to update in response to changes in the business practices and environments. Any spreadsheet error large enough to be noticeable would be caught when it was loaded to the data mart and reviewed. Any error that wasn't that large is immaterial. At the top level, a few million dollars is a rounding issue.

I don't see spreadsheets going away any time soon. The models are complex. The business is rather large. The models need to have a circular relationship with the capacity plans. The models can't account for a certain amount of top down budgeting. These factors render centralization largely an exercise in futility.

The killer app is going to be Excel Web Services in Office 2007. That will provide the accountability and auditability while allowing our analysts to continue using Excel.

Coping?

[segedunum]

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

Swearing like fucking buggery at people who think spreadsheets are the universal solution to life.

Why spreadsheets

[Guil Rarey]

Why spreadsheets? Why not databases? What about Sar-box or HIPAA? What about security?

From the top, I AM an accounting troll, and I am the local Excel guru.

Some random thoughts on the topic:

Like anything else, whether or not a spreadsheet is a good solution depends on the problem you're trying to solve.

Part of the problem is that the basic user-interface metaphor has a lot of legs. The spreadsheet metaphor has been around for 25+ years and gone through several different predominant apps in that time. It's well understood, intuitive and discoverable. From a user standpoint those are powerful reasons to stick with it. The problems are behind the scenes and particularly behind the scenes with excel. Now there's a surprise - software from M$ with hidden problems.

In a sense Excel suffers from the same bloat as Word - it's just less visible to most. Excel now ships with an enormous function library but most people only know and use a bare handful of the functions. For about 60% of the time, I only need about a dozen functions. And for most people that's all they EVER need. The other 40% of the time I will use everything I can find. All of that function library is simply unnecessary, confusing, and useless bloat to most people. Much more of it should be broken into add-in libraries only loaded when needed. The app itself should be smart enough to know when a spreadsheet is opened what modules it needs to load and lets the user know.

Excel now ships with a full IDE that lets someone have access to ActiveX and DirectX and all those other happy invitations to malware. Okay, there are things I need to do that I actually need a lot of that power - though I'd rather code in Python than VB - but Excel astonishingly and horrifically does NOT have the ability to do a simple keystroke macro to replicate simple tasks. The alleged "macro recorder" is a sadistic joke. Non-tech-savvy users hate that.

Using spreadsheets as databases - it's a valid point - excel isn't a database, doesn't play one on tv and shouldn't be confused with one. But a data table in excel is intuitive, available, discoverable. I can see the damn data!!!! I can fiddle around with it - quickly! - in a pivot table or the filter features. I don't have anything like the same ability to access data quickly and easily from the company's ERP system, and I certainly don't have the ability to poke around and explore the data when I'm trying to figure out what the hell is going on. I'm comfortable using SQL so real databases are no threat to me, but the data I need, locked away in some utterly inaccessible, uninterpretable ERP hell doesn't do me a lot of good.

Spreadsheet-as-user-interface metaphor is not going away - it's too powerful now. Some things that can be done to improve spreadsheets and excel in particular - make feature and function sets more modular - only load the ones that are needed or useful to a particular end-user. Improve the tools and documentation to make locked-down restricted-use spreadsheets the right way. Provide a lightweight click-and-keystroke macro facility and move the IDE-as-macro further away from the routine user. As for using Excel as a database - well, we can stop misusing excel for those purposes when there is 1) a general-purpose, intuitive tool that allows equally quick and simple access to data, and 2) more readily discoverable data in ERP systems.

Re:Why spreadsheets

[cr0sh]

Look into things that Actuate Software is doing - their stuff is expensive, but they have recognized the issue and are moving their reporting/analysis tools to take advantage of the metaphor. Last thing I saw (almost 2 years ago), they were working on a system that looked and acted like excel, but it was really an excel-like view into a database via their reporting engine. When the database backend changed, the spreadsheet would update as well. A ton of more complex stuff was also handled.

Anyhow, they recognized the issue of "everyone using spreadsheets" - and the issue of "different people all purporting to have the same spreadsheet, but each was different from the others" - they also had a term for this problem: "spreadmart"...

I get no royalties from this...

[ThePooka]

...but there's a nifty explanation of at least one source of these errors here (pp. 13-16).

Re: CSS

[jc42]

Time to hack the CSS and use a local style sheet.

The /. redesign was actually a plot to force us to all learn enough CSS to override the damage.

Re:One percent? COME ON!

[redcone]

Being able to use Excel doesn't qualify you as an accountant anymore than being able to use a PC makes qualifies you as a programmer. A large part of the problem is that many spreadsheet users lack an accounting or mathematical background. They are cubicle drones who have MS Office on their computer and are expected to use it.

Better training, and using a better tool?

[glawrie]

The remarkable thing about this is that it is such an old story. I was involved in looking for improved ways of ensuring spreadsheet models were error free when i worked for the consultancy division of a large accounting firm back in the early 1990s.. Then we were focusing on developing structured methods for laying out / documenting simple spreadsheets (these were the days when Lotus 123 was top dog...), providing more specific training on how to build easy to follow spreadsheets and doing some formal auditing of work. But the biggest idea then was to simply use a better tool - even then people were developing software designed to make financial modelling (in particular) more rubust - including Javelin (from Ashton Tate I think) and Lotus Improv. Both these used multi-dimensional space to track results of formulaic relationships that defined the model (in Improv's case you could have up to 12 dimensions - and its innovative way of allowing the user to browse this datacube by shuffling 'axis tiles' was later crudely copied by MS to come up with the PivotTable - a pitiful copy of the original idea). The idea being that you define the relationship using (in Improv's case near-english language) formulas (such as Profit = Revenue - Costs, with Revenue and Costs being defined separately by other formulas, or simple data vectors). Improv included (as with other similar products) the idea of sequences within dimensions - so you could have 'previous' and 'next' modifiers in formulas which was great for financial type analysis etc. The key was the formulas were were distinct from the numbers themselves (separating code and data as you might now say). It allowed you to do some very powerful modeulling with high confidence levels. I used Improv a lot for a few years, but Lotus never worked out who its client base was, and it finally died in the mid 1990s. A great pity.

A short review of spreadsheet evolution appears here.

I seem to recall that someone still sells an Improv like product, but not sure where / what it is, or if it is well liked.. .

Oh so that's why...

[Anonymous Coward]

So that's why the homebuilders like KB Home keep reporting record profits when they aren't really selling anything, aren't paying their subcontractors and layoff half of their employees...

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.

spreadsheet virus

[Anonymous Coward]

Hm. remember the old idea to attack western crapitalism: write a virus that attacks spreadsheets silently rather than being "look at me" malware, changing the odd figure here and there... Or maybe it's unnecessary...

The Ledger.

[mcsporran]

It amazes me, with all this technology at our fingertips, the default way of storing information for many office workers, is an Excel Spreadsheet, not with any complex formulae, but simply as lists of information, that are only accessed by cut and paste, or by even more error ridden manual transcription.

This is worse than a manual ledger kept on a shelf, where at least large amounts of info can't be arbitarily deleted, and they may have some audit control.

I myself have worked or a large Telco, who through a subsidary maintained a large insurance companies network, they used a giant ( 17 MB ) spread sheet, filled with useless data, to alledgely manage this network. The only time I've even seen anybody using leading zeros in an IP address. No audit trail, No rollback except last nights backup, Single write access. Nightmare.

Must of cost thousands or millions of quid per annum in FUBAR, but the manager who wrote the awfull thing was "completely confident, that it did all required"

Spread sheets for any other use but pure number crunching and pretty graphs, are a cancer strangling your company.

Can't resist

[isotope23]

I will give you what I consider the most blatant and insane example.

U.S. Constitution article III

The trial of all crimes, except in cases of impeachment, shall be by jury; and such trial shall be held in the state where the said crimes shall have been committed; but when not committed within any state, the trial shall be at such place or places as the Congress may by law have directed.

U.S. Constitution: Sixth Amendment
Sixth Amendment - Rights of Accused in Criminal Prosecutions

In all criminal prosecutions, the accused shall enjoy the right to a speedy and public trial, by an impartial jury of the State and district wherein the crime shall have been committed, which district shall have been previously ascertained by law, and to be informed of the nature and cause of the accusation; to be confronted with the witnesses against him; to have compulsory process for obtaining witnesses in his favor, and to have the Assistance of Counsel for his defence.

Now to me and I think to 99.999% of americans the phrase ALL clearly means every single instance.
To judges and lawyers however this is apparently different. As it currently stands you do not have the right to a jury trial.

Re:Can't resist

[Anonymous Coward]

I think it's funny that not only do computer nerds think anyone who doesn't understand computer lingo is an idiot, but they also think that when they themselves don't understand the lingo of another profession that those professionals are the idiots.

A spreadsheet is a specialised functional program

[jesterzog]

IT should be strict about policy around spreadsheets... spreadsheets are great powerful tools, but they shouldn't be anointed as applications.

I see where yoo're coming from, but I'm not so sure about this particular statement -- after all, a spreadsheet is effectively quite a specialised functional program. It can have bugs if it hasn't been designed well, but so can imperitive programs. Try writing a complex program where you're not at risk of changing something a million miles away by changing one line. From my observations it can happen all the time if a programmer's not careful, and sometimes even if they are careful.

The difference is that until now, a lot more research and education has probably gone into writing imperitive programs. People who usually write spreadsheets, on the other hand, tend to get their education of them (if any) as an afterthought to some quite different financial education. Personally I think the problems lie mostly with the techniques people use to create complex spreadsheets, and there's probably a lot that could be improved with the spreadsheet applications, too, to help prevent bugs from being introduced.

I doubt many people use well developed and proven techniques for engineering their spreadsheet applications, and if they did, I doubt that the spreadsheet applications would support them. Putting more emphasis on separating the raw data from the logic would be a good start in my opinion. Some people who are better at doing this go to lengths to colour code things, lock cells, and so on, but the tools don't exactly encourage or force people to do this.

But this is all just similar to the sorts of research that have been going into more obvious types of software development over the last two decades or more. Personally I don't have a problem with treating spreadsheets as applications in our own system. Fortunately in our case, the people who write and use them here tend to know sufficiently about what they're doing that they're not so brittle. Also for those sorts of applications, they simply seem to be the best tool for the job.

Nah, IT never redefines common words ever!

[Medievalist]

IT may overnominalize, but (unlike law and accounting), we tend not to completely redefine perfectly good words for our own uses

Like bit, nibble, archive, file, directory, etc. etc. etc. those words always meant what they mean in IT, right?

Learning what a TCP/IP stack does takes some effort, but once you know the phrase, you know the phrase.

Are you sure? A cursory glance at some standard IT resources, like, hmmmm... the Internet for example, shows that most people who are saying "TCP/IP stack" are actually talking about the IP stack of which TCP is one part. Sometimes they aren't even that close to being correct; they'll refer to ARP, for example, as part of TCP/IP, or start talking about MAC addresses.

What about the bus effect?

[LunaticTippy]

Management has to accept that any employee can get hit by a bus. If they've come to that realization, they can simply pretend the employee was hit by a bus and replace at will.

I like job security myself, but dislike getting phone calls about stuff only I can fix.

Appearance vs. Data Constraints

[cmpalmer]

We recently received a spreadsheet (Excel) with tens of thousands of rows. Visually, all of the rows looked correct, so after spot checking, we imported the data into a database (SQL Server). Hundreds of the imported rows had NULLs in the database columns, in a fairly unpredictable pattern. The culprit? Some of the rows consisted of numbers where the leading zeros were important (essentially a text pattern, but only containing numbers). If the number didn't have leading zeroes, they entered it as a number. If it did have leading zeroes, they put a single quote to force it to a string. Short of scanning the column, it looks the same. The worst part is that selecting the column and applying a format (number or text) doesn't change the internal representation and the database import was looking for one data type and ignoring the ones of the different data type. The fix was easy, but weird - create a new sheet, define the datatypes of the columns correctly, then copy and paste the data from the bad sheet to the new sheet. Just doing this (and changing the database import to work with the new sheet) got us 100% of the fields imported.

This type of thing is what we see most commonly when accessing data from spreadsheets - the users concentrate on the screen or print presentation of the data instead of the constraints on the data (types, ranges, values, formulas). Sometimes, they'll hide columns (which is a good technique for hiding intermediate calculations that you don't want to print), but this screws up the database imports as well (or at least makes you go back and redo them several times wondering where the other data is coming from).

Bottom line is that spreadsheets have their uses, but they suck as databases.

Of course, there is one other major abuse of spreadsheets - using them as drawing programs or diagramming tools. I've even seen a user manual written in Excel!

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.

We use databases, that's how

[v3xt0r]

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

If you want a spreadsheet, you can simply export it by using a query to the database.

All auditing by HIPAA or other accountability issues are no longer an issue (depending upon your database structure of coarse).

People who use spreadsheets AS databases, need to be fired, or removed from their "IT" position.

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.

Re:WebDAV + SVN

[AuMatar]

Except I'd want this on the whole filesystem, from /. on up. Especially /etc. And there's absolutely no reason I should have to use Apache on my computer to get it to work. To me this seems like it should be an integral piece of the filesystem layer, not something hacked on half assed later (and with much lower efficiency).

Re:WebDAV + SVN

[CrazedWalrus]

I see. You're right in that requiring apache for a totally versioned setup is a weak point. I was more concerned with /home.

For entire filesystems, I seem to remember that Sun's ZFS did some kind of copy-on-write versioning. I think it's only in OpenSolaris now (and didn't Apple pick it up?), but I'm sure it'll make it to Linux soon -- always assuming you're a Linux guy.

At work, they have a .snapshots directory, which contains directories like .week .month .hour, and it contains copies of all my files in those timeframes. I suspect that this is an automatic COW feature of the FS, since I can't see them copying everyone's files every hour. That works great, because if you clobber a file you've been working on for more than an hour, you can always grab it from .snapshots. Granted, it's time-based versioning instead of per-save-based like the dav/svn solution, but it'll sure save yer bacon in a pinch.

versioning

[LunaticTippy]

I liked the VAX-VMS versioning.

You'd have myfile.c;15 myfile.c;16 and if you saved it again you can predict the result. When you are running low on disk or get annoyed by too many pages of filenames you can PURGE. To retrieve an old file version, just use the ";x" suffix.

You can recover from many mistakes with this. Now that disk is so cheap it should be everywhere. But it's not.

Now I miss posix.

Re:versioning

[rk]

There was, a long time ago, a FILES-11 file system driver for Linux. Whether it worked well, was read-only, or was a complete mess I really can't say. A casual Googling didn't yield anything, but if you're really hankering for it, you might be able to find it out there somewhere.

Re:versioning

[LunaticTippy]

Thank you. It was nostalgic to read about FILES-11. I'd utterly forgotten the name and never got inspired to look it up.

Logical Names, what a great thing! I couldn't remember if it was CP/M, RSTS/E, or what, but I sure missed that.

you CAN "compile" excel spreadsheets

[Anonymous Coward]

Another thing that would make spreadsheets more useful/powerful would be the ability to COMPILE them into another form.

You can already do this using a program called Turboexcel, which converts excel "programs" into C++ (which can still be used within excel, but run 100 times faster). The banking industry uses this quite a bit.

http://www.turboexcel.com/

-R

Double Entry Bookeeping Spreadsheets

[bjs555]

A few years back I created a quick and dirty double entry bookeeping workbook for some stock traders. As time went on, it grew from a few spreadsheets to over a hundred with about a thousand formulas on each sheet. I tried hard to carefully enter each formula but every once in a while I find a data entry mistake that I made long ago. The mistake is usually a typo in a variable that changes from column to column along a row. Many of the rows were made by copying an existing row, pasting it somewhere else, and changing one variable per column. Fortunately, a double entry bookeeping system almost always catches such errors. I suppose two offsetting errors are possible but unlikely. I think the value of double entry accounting is underappreciated. I once read that it was one of the more important inventions of man and I laughed that off until I actually had to use it.

VSTO?

[fury88]

While I can see the point of this topic, are we talking about typo errors? Those exist everywhere. Unfortunately they are magnified when you are dealing with a SSN or a decimal place in a monetary figure. Microsoft DID come out with Visual Studio Tools for the Office environment that does make their Office tools much more interactive and safer. I happen to be working on an Excel spreadsheet now that hooks up to a web service. Who knew?!?

Spreadsheet errors

[Orion Blastar]

Ironically I used to make a living writing custom business applications which helped eliminate the errors caused by incorrectly using Spreadsheets. The programs were compared to the results of hand calculated results that where tripled checked for accuracy. I worked until the application's results matched the formulas used and the hand and calculator checked work.

Usually the evolution went something like this:

Excel -> Access -> Visual BASIC -> Active Server Pages.

Sometimes Access and Visual BASIC were skipped to go straight into an ASP developed Intranet. Sometimes the employer just wanted an Access based database with forums. Whenever they found someone using and Excel spreadsheet, they came up with an idea to make a Database out of it and a custom application using Access BASIC, Visual BASIC, or Active Server Pages.

Other languages and platforms can be used for the same results. Keep in mind that bugs can develop in custom applications that can yeild incorrect results. That bugs often come about when a rush is made to meet deadlines, or the programmer is told to skip the analysis and design cycles and start coding on day one. There is a proper way to do this to minimize bugs, and there is an improper way to do it which maximizes bugs.

Spreadsheet slips on a banana

[neonprimetime]

30.) Spreadsheet slips on a banana

Genius! This would be the great title to an article about somebody getting caught storing their pron in a spreadsheet at work!

Re:One percent? COME ON!

[sharkey]

You say that like it's a bad thing. Unless TV and the movies have lied to me, secretaries tend to have bigger tits and looser lifestyles than accountants do.

R to the rescue

[yankpop]

Spreadsheets are a great tool, but in a much more limited context that how they often are used. I see a lot of my colleagues in academia managing all of their data in spreadsheets, from data entry, error checking, and even a disturbing amount of actual analysis.

What I find works much better is to set up a simple relational database with data entry forms that limit your ability to make silly typos. Then I visually check over the data in a spreadsheet, to catch obvious errors, like mis-placed decimals. But I don't make any other changes to the data in the spreadsheet. For everything else, the data gets exported to R, where I do all non-trivial error-correction, calculation, and analysis.

This means that any changes I make, and all calculations and analysis, are stored in commented R scripts that I can review months later and still make sense of. This is a little harder than whipping up an Excel spreadsheet that does everything in one undocumented step. But I imagine in a big company you'd be able to build real simple GUIs for 90% of the users who just need to enter data or print out reports, and leave the messy in-between stuff to the IT folks.

yp.

Spreadsheet errors

[Anonymous Coward]

I'm confused as to the problem; in most domains that I work in (both professionally and personally), self-verification of the spreadsheet is typically very easy. You can even devolve it to a single idiot box like ERROR!/OK.

Zimbel

How? Giggling!

[Bob9113]

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

By giggling and thinking to myself, "see, that's what happens when you spend more on management than you do on mathematicians and logicians." The giggling helps.

As for non-Slashdotters, the will deal with this by hiring high priced lawyers for the high priced execs. The lawyers will argue that 1% is far better than could possibly be expected, therefore the VP who did it should not be held accountable - surely SOX is meant to cover willful acts, not innocent mistakes. The VP who did it will then get a bonus for publishing the faulty quarterly report which resulted in the stock going up 2.5 points.

Note that of those innocent mistakes that make it into published results, over 75% will result in lower P/E ratios, higher EBITDA, or something similar, with less than 25% resulting in no significant change or an inverse change. If anyone other than a few university economists notices this, they will simply think, "gee that's funny", not "gee, that's statistically significant."

What was the question again?

Spreadsheets are not accounting software

[blair1q]

Spreadsheets are handy, but yes they contain errors, and if all of the formulae in them are created without testing (ymmv) those errors will propagate and not be caught.

Accounting software goes through a directed QA process (ymmv) and will catch errors (ymmv) and will refuse to propagate them (ymmv).

So the solution is: BUY ACCOUNTING SOFTWARE. Stop using your own spreadsheets for legal records. They're no better than your input, and are capable of making the result worse.

Try VBA!

[Merdalors]

Next time try writing functions in VBA and calling them from the cell.

I just finished helping an E. Eng. with a 1,000-row, 120-column monster with 300-character formulas. It was not humanly possible to manage all the formulas using just Excel, so we abstracted them into VBA functions. Made the spreadsheet much neater and comprehensible.

Spreadsheet alternative

[Anonymous Coward]

Check out something like Quantrix http://www.quantrix.com/ a reincarnation of Lotus Improv

I'm sorry..

[hyfe]

I'm sorry, but that number is slightly scewed, and it's partly my companys fault.

You see, we have these ridicilously large spreadsheets which are really boring, and in order to improve morale Marketing deciding to add 'Pink Font Colour' as a critical requirement. Naturally, this requirement was 'overlooked' so we ended up sending in large amounts of spreadsheets with critical errors on every single cell; even the empty ones. >p> What happended? Now, the accounts didn't mind the pink colour, as they're all colourblind anyways; the only reaction there was one guy sighing and turning up the contrast on his monitor. The engineers didn't mind too much either, as the pink colour faintly reminded them of somebody, and the fleeting sensation really did bring some joy into their lives before they were wipped back to work.

The problem was of course with the web-designers. Two of them plain suicided when they saw the colours, while several others ran around screaming for hours.. Naturally, they started a project to change all the colours back. Being web-designers, they did this one cell at the time. Luckily they did get finished in time for our spreadsheets to be part of this survey.. it only took 20 men two weeks.

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:Sometimes, it's *not* an error...

[idiot900]

No surprise to me that the company went under, if their HR policy is to fire people who fix problems and do things right. I'm currently in the magical la-la land of academia and have no clue how the real world works - is this tendency endemic in business?

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

[Anonymous Coward]

It's a tendency at the *huge* government agency I work at also. It just depends on the management. My management happens to be of the type that values loyalty, politics, and personal favors above integrity, honesty, and ethics. Seriously. I can't tell you how much fraud occurs because a manager is afraid to look bad. The entire work atmosphere is one of CYA and blame the next guy if something does go wrong, but nothing wrong with cooking the books to hide it if at all possible.

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

[tehcyder]

I don't understand how you ended up carrying the can, surely your manager would have been the one in trouble with the exec?

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

[tgd]

Never worked at a real company, eh?

Shit flows downhill, and blame lands on the first person who isn't smart enough to pass it down the line.

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

[tehcyder]

I've always found that people like to dump on the most senior person they can get away with.

You get more big-dick points for firing a manager than a clerk.

Excel ate my DNA

[Anonymous Coward]

This may be redundant (It's 2 am, I seriously need to go upstairs and lie down, not look through all the other postings), but spreadsheets screw up more than financial data:

http://www.theregister.co.uk/2004/07/16/excel_vani shing_dna/

Use Excel Audit Tools

[Anonymous Coward]

There are third party tools that do a good job at finding spreadsheet errors. The common, hard to find ones such as incorrectly copied formulas and incorrect A1 references.

The Spreadsheet Detective is one of the oldest and most established, http://www.spreadsheetdetective.com/

Like all software development, peer review is the key. But for spreadsheets, this is infeasible without tools.

Anthony

people use excel for a reason

[cinnamon colbert]

its simple, i can understand it, and i can get my work done today. these are all important things, so all the programmers should stop being snotty and superior , and instead focus on being helpful, which would mean providing excel add ons and training that solve a lot of the problems.

I bet you dollars to donuts tht 90% of the errors come from a small number of mistake classes, that could be handled easily.
I furhter bet that the smart it guys have scripts that check all the spreadhseets for some of the common errors.

I gave up

[SlashSquatch]

I gave up on spreadsheets when the formulas were not persisting through save and close cycles.

I guess I'll never be a point and click user.

If I can't do it in dc, bc or an awk one liner then I go ahead and write an awk script. If you can't "awk it" then R or Octave may be the solution. Want a nice binary? Use C or Fortran. Big thanks to those linux guys for putting all those tools into circulation. I just love to uncover a gem with man -k for command line use.

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

Re:Anybody who trust spreadsheets is a fool

[Anonymous Coward]

BTW the program is called Excel, not Excell IDIOT

Javelin, Lotus Improv, Quantrix not Visicalc

[WillAdams]

A1:A7 was a necessary (computer) memory-saving expediency --- http://www.bricklin.com/history/intro.htm

Not necessary now though --- http://www.simson.net/clips/91.NW.Improv.html

At the very least, I know of one accounting firm which requires only named ranges be used in calculations --- (cited in ) http://www.amazon.com/exec/obidos/tg/detail/-/0534 371353?v=glance

William

Who cares if there are errors?

[sk999]

Having managed over a dozen budgets and been driven nuts by project managers and bosses who want every cost detail to be accounted for precisely, my mantra has become the following:

"TRUST THE CENTRAL LIMIT THEOREM!"

Any budget or cost estimate is usually the sum of many smaller items. Whether you are dealing with cost estimates or charge accruals, errors will inevitably creep in. Even if the errors for any single item are huge (say, 100%), the central limit theorem tells us that the error in the sum, at least expressed as a fraction, will be much smaller. As a consequence, I maintain that we should not obsess over the details - they will average out in the end. The big picture will take care of itself.

One approach that could be taken ...

[constantnormal]

... is for spreadsheet vendors to offer a "compiled spreadsheet" option that would not allow any changes of content, only format (font/style/size). It would not eliminate logic errors in the spreadsheet -- although some level of increased error detection could be employed in the compilation process -- but such an option would eliminate errors due to user meddling with the internals, since they would not be able to delete/add rows, columns or individual cells.

The same effects can be obtained through password-protecting sheets, but having a "compiled" spreadsheet option makes for a more idiot-proof way of accomplishing this result.

Another thing that could be done by the spreadsheet vendors is to create an "assertion" sheet, where formulas listing groups of cells that should always be identical, or non-zero, or positive, or negative -- or any relationships between critical cells can be listed, generating an error indication should the any of the assertions be violated. Again, this can be manually coded in today's spreadsheets, but having the spreadsheet program prompt to create an assertion sheet would help make this a part of every spreadsheet. Answering "no" to the prompt to create an assertion sheet would result in a visible indicator that assertion-checking was disabled (maybe a custom color in the title bar).

The same approaches that are used to improve error detection in traditional compiled programs can be also employed in the creation of spreadsheets -- all that is required is taking the belt + suspenders approach to coding.

No, it's not.

[teknomage1]

An ideal spreadsheet program would be written in a functional style, but since most spreadsheets do not allow you to have extra variables in your functions (all variables are cell addresses), nearly any complicated spreadsheet application uses numerous temporary or hidden cells for intermediate calculation data. That's not fuctional programming at all, it's tons of global transitive state!

Re:One percent? COME ON!

[Billly Gates]

Problem is businesses today is cheap after the shares of most companies went down during the .bomb.

Instead of using third party or custom database apps, many corporations used excel spreadsheets that have hundreds of thousands of rows. Yes I have seen them. Shudder ...

How do you debug them? Most humans are not programmers and real programmers of course would never let htis happen and would develop a costum app for each task instead.

roots

[lon3st4r]

The roots of Excel and spreadsheets like Lotus 1-2-3 and Visicalc. They used to be wildly popular in the corporate environment and was the killep app. A lot of corporate accounting was shifted to these spreadsheets.

Why is it that spreadsheets have become a bane today for such uses? Could it be that better software alternatives are available today? or that excel macro errors are quite often overlooked and people don't recheck the code/calculations via paper-pencil (too much work)?

it would be wiser to do a need based analysis of which tool to use. if one has to use excel, then the sheets need to be tested properly before being deployed. treating them as quick hacks - and not checking for errors - will lead to disastrous situations.

* lon3st4r *

Still don't use Excel. It's broken. Here is proof.

[Anonymous Coward]

It's the color of broken statistical routines.

Although some clueless idiots marked me as flamebait, I would suggest to use for example Gnumeric instead of Excel. Excel is broken, it won't be fixed, get over it.

Slashdot - marking people as flamebait for telling the truth.

Thats ok I use FoxPro!

[pretentiousPPC]

You can take your fancy-dancy highlights and graphs and shove it somewhere else.

Errors in spreadsheets

[saxonhawthorn]

"How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?" I build-in closed-loop reality-check calculations at key points all over the sheet. Thus if Column X should equal Row Y, I simply make sure it does. (No-brainer surely? Or have I missed something?)

You're barking up the wrong tree

[Moraelin]

I've met people who used buzzwords they didn't understand to sound knowledgeable, and believe me, it wasn't the programmers. It was the occasional management or marketting guys who thought that they'd look like programming experts if they use those. They spend their whole day using bullshit buzzwords in meetings, that they start assuming that everyone else does the same.

Look, I don't use acronyms to sound cool. I use them because they're mean something _very_ precise that are backed by a whole tome saying what they mean, what they do, and what they don't do. They're not there because they sound cooler than another common word, but because it would take tens of thousands of words to specify the same thing with the same detail.

E.g., when I say TCP/IP, it's not just a funky way of saying "over the net". It also tells another engineer exactly what kind of a net, and how. You could print a tome with all the RFCs (basically, standards) it complies with, or at least doesn't interfere with. It tells you something about the kind of hardware and software you need too.

E.g., when I say it's an EJB application and uses SOAP over JMS for messaging and JAAS authentication over LDAP, it's not just for the sake of having some "enterprise" buzzwords. It tells you the exact software you need to run it, exactly what protocol you can talk to it over, what you'll store the user accounts in, and exactly what you need to change if you want those passwords stored in an Oracle database instead. E.g., it tells you that if you want to interface another app to it, a SOAP framework (e.g., Axis) might help. There are whole books that define each of those "buzzwords" in painstaking detail, and define exactly what they do, what they don't do, what they can connect to, what's optional, and what you probably shouldn't use if you don't want to be tied to a single vendor.

It's not even possible to say the same in plain English, or not without writing a whole bible-sized tome.

How would you say that? "Some modules that talk over the net"? Well, that's fine for a quick talk talk to the customer, and we all do it all the time too. But same as in law, it doesn't help you if you wrote a spec like that. What kind of modules? If you want to add your own, what exactly do you need to write? Over what net? IPX maybe? In what format is that talk? If next year the HR database guys want to ask for data from ours, in what format would they send their request and how? If next thing they want it changed to take the user passwords from the portal guys' DB2 database, exactly what has to be changed? Etc.

Re:You're barking up the wrong tree

[Red Flayer]

And when you're talking to another engineer, that works fine. But when you're talking to someone who doesn't know squat about the nuts and bolts, you end up either

(1) Confusing the poor sap, or
(2) Wasting your breath because it's going to get ignored anyay.

In reference to one of the parents in this thread, that info is a waste of time when you're talking to a CFO or finance director -- they want to know Why/Why Not and How Much (and possibly Other Options) -- and that's about it.

I guess my point is that all the clarity that those specific terms have is lost when you're speaking to someone without knowledge of the field -- so there's no benefit in using them in that case.

Self Checking

[Albigg]

Many of the complex spreadsheets I've done in the past have at least 2 paths of computing the result. If the 2 results don't match, display an error.

Please,

[Jerk City Troll]

Tell us how you really feel.

You read it wrong

[Anonymous Coward]

He wants "Excel Expert" Masters.

Apparently this guy is enslaving hordes of Excel Experts for his plans for world domination. And if he's reading this: for the record, I for one, welcome our Excel Expert overlords.

UK Spreadsheet Errors Conference 6-7 July

[SysMod]

The news stories are from EUSPRIG : next month is their SIXTH annual conference on spreadsheet errors ; it takes a while to get attention, but it's attracting some significant attendees this year. Ray Panko has of course spoken there:

http://www.eusprig.org/ European Spreadsheet Risks Interest Group
http://www.uwic.ac.uk/eusprig/2006/index.htm The Sixth annual conference theme is Managing Spreadsheets: Improving corporate performance, compliance and governance.

And I'll add a plug got my book:
http://www.sysmod.com/scc.htm
If spreadsheet users had a driving licence, this would be their seat belt, air bag, navigation aid, repair kit, hazard indicators, and the rules of the road. This new book describes how to produce well-crafted spreadsheets that are easy to understand, maintain, audit, and operate. It shows how to ensure data quality and accuracy and protect against formula and operational errors.

Nothing against that, really

[Moraelin]

I'm not even opposed to keeping it at a "it'll be a form on which you can enter data" level, but usually the problem is:

1. The poor sap has already been confused by some marketting guy (ours, IBM's, whatever) that he absolutely needs SOAP, BPML, Web 2.0 compliance, etc. Or he's read some "IT for managers" ragazine and now he thinks he's a software architect. E.g., I've had some management type decide that I must do an application in Visual Fox Pro instead of, say, C++ or Java, because, in his words, "Java isn't Visual enough." The guy had had a couple of hours with a MS sales drone that showed him how easy it is to drag buttons around on a "Hello World" form, that now nothing could convince him that there's more to programming (e.g., being able to refactor, or debug sanely, or a dozen other things) than dragging buttons around. Or that saving a single-user database's file on the file server does _not_ make it a substitute for a real database.

Basically, as the saying goes, "if you can't stand the heat, get out of the kitchen." If the CFO doesn't want to talk about database record locking, then he shouldn't try to design the application for me. If he's trying to discuss such engineering issues as what database to use, then he damn better not act offended if he gets actual engineering talk. He may be able to understand how much dollars he'll save by dumping a Fox Pro (or Access or Excel) file on a file server, but he better also be able to understand when I tell him why that'll be a liability when 20 people try to use that file at the same time. Or refrain from pretending that he's a software architect.

2. When discussing the Why / Why Not and Other Options, those seeming buzzwords can actually make or break the whole thing.

E.g., if I have to explain why my solution needs some kind of messaging server or database server instead of just dumping a file in a shared directory, there's no sane way of explaining that without some reference to XA Transactions and the like. And why you need those to ensure that if customer X bought product Y for Z dollars, either both the payment and the order to the warehouse must _both_ succeed or _both_ must fail. Having a system that's held together with spit and duct tape, and people routinely get their money taken but get to battle the support guys to actually get the product, won't even save the company money, it'll cost more in lost reputation. But there's no way to explain why his great idea of saving money by using an Excel spreadsheet on a file server can produce the problem, without, you know, at least mentioning the engineering theory behind it.

Re:Nothing against that, really

[Red Flayer]

1. Agree completely. I find myself, when countering preconceived (but wrong) ideas, that I end up teaching a lot. Which is not what I'm paid for, and frustrating when pitching a solution, but that's a different story.

2. Good point. I'm used to dealing with grossly uninformed people (and I am by NO means an expert, especially in engineering -- finance is my main bag) in which case I can see their eyes glaze over when I get technical on non-finance stuff... I find myself explaining as best I can in plain English. This happens so often, that plain English is my default, and I'll step it up if they seem to know enough. I've encountered quite a few decision-makers who think tech terms are meant to obfuscate so people can bill more hours :)

Re:Still don't use Excel. It's broken. Here is pro

[winkydink]

Sure just help me migrate ths boatload of macros (aka VB) over. What? You can't!

Re:spreadsheet errors (product suggestion)

[AlisonHMS]

Spreadsheet errors are hard to fix and errors are easily replicated because the "logic" is not transparent -- the formulas are contained within the cell. Only the creator of the spreadsheet has a prayer of understanding the intent of the document, not to mention the function of each cell. While the spreadsheet might be ubiquitous, serious number crunchers need a better application. There are a lot of enterprise financial modeling apps, but they don't help desktop users. A company in Portland, Maine has developed something called Quantrix Modeler. It's multidimensional, formulas are contained outside the cell, and use natural language. There's a 30-day trial version at http://www.quantrix.com./