Slashdot Mirror
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?"
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).
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.