These [incorrect] rules are not explicitly stated in the methods; we inferred them... from formulae embedded in Excel files...
One of the things that most puts me off from using spreadsheets is the difficulty of validating calculations. The formulae themselves are not visible, so it takes a lot of mousing around to convince yourself the cells do what you think they should. For example, I've seen problems where a long "vector" calculation is correct in the upper reaches, and wrong down below, due to failure to completely "fill down" the calculation.
I wonder how many of the spreadsheets studied by Baggerly and Coombes expressed input errors, rather than errors of understanding?
One of the things that most puts me off from using spreadsheets is the difficulty of validating calculations.
A very good friend of mine worked for a major aerospace company that used Excel spreadsheets for almost all engineering. I'd say the biggest problem is that if you're doing important calculations in Excel, then you're completely ignorant of the last 40 years of research into defect reduction and detection in computer science. That ignorance is not completely unintentional either: there were a lot of people who literally pride themselves on not being like the dumb computer folks.
Plus, if spreadsheets are your tool, you likely have very bad tools. So when you make a spreadsheet to run the power budget for satellite A, the obvious thing to do when satellite B comes up for development is to copy the spreadsheet containing the analysis from sat A, change the filename and bury it somewhere on the shared network drive. Then, if you find a defect in the spreadsheet, you have no way of tracking which copies have that same defect because they're scattered all over the shared drive.
In any event, my friend found tons of bugs in spreadsheets, many of which were actually important. Probably the best bug involved a power budget for satellites that inverted electrical current so instead of comparing current to a max theoretical current value, it compared 1/current to the max value.
I can verify that this practice is still common. I have been amazed at the size and complexity of some spreadsheets in aerospace applications.
It's so clearly the wrong thing, and yet the authors of these beasts are always really pleased with their efforts.
Besides the "old shoe" feeling of Excel, I think there's something about the automatic updating done by the spreadsheet that users find gratifying. You alter a cell and the program launches into a bunch of updates automatically. It's like seeing the array of blinking lights.
Spread sheets with units would help "can't compare ampere to 1/ampere". As would ditching "the world is one giant grid" in favor of multiple tables as in a relational DB on the screen. For example if you applied a formula to a table, you get a new table instead of some region of cells that happens to be the formula applied to another region of cells. This also solves the problem of having to enlarge the region where a formula is applied when more data comes in.
I wonder whether there’d be any good way to encourage people to use more inspection-friendly code, e.g. in R, Matlab or even better Python/numpy/scipy. I wish that science journals would accept/encourage source code appendices to articles or at least prominent links to online source code, and that such code, ideally clearly organized and commented, were as expected for “serious” articles as bibliographies are currently.
Even many papers in computer sciencey topics leave out critical implementation steps in describing algorithms and approaches; even a mediocre implementation makes the authors assumptions’ and precise meanings much more explicit. Often (though not always) code is as readable or more readable than mathematical formulae.
That barely begins to address the problems though. When you're writing formulas, you're writing in a small box with no syntax highlighting, no serious whitespace, and no comments. It almost seems like Excel's designers worked really hard to come up with a user interface that would make introducing errors trivial and noticing errors very hard.
And yeah, I understand that spreadsheets were never designed with the idea that people who would use them to enter long complex formulas or for purposes where defect detection would be really important, but that's how they're used in the real world.
> "It almost seems like Excel's designers worked really hard to come up with a user interface that would make introducing errors trivial and noticing errors very hard."
Or, rather, Excel's designers spent very little time and effort into support for non-trivial formula creation and use.
But, if we all seem to agree that Excel is not the place for serious calculations, can we fairly continue to take shots at Excel for being bad at supporting them?
i.e. When I see people maintain non-trivial web apps by opening notepad against production code, it never occurs to me to complain that notepad should have syntax highlighting, collaboration and version management, etc.
I actually feel bad for the Excel devs. In my friend's experience, the relatively modern features that MS introduced into Excel like named cell references were deliberately avoided because they smacked of "programming" and "made things too complicated"...so it seems like the Excel guys lose no matter what.
But with something like making the formula editor reasonable, I think they do have some blame. Excel costs a heck of a lot more than notepad and it is billed as being a serious tool for professionals to use.
Totally agree, hopefully MS will get on the ball one day and improve the editing features. Limited syntax highlighting and an awkward commenting tool are available, but mostly unusable.
Google Spreadsheets is catching up fast (and has even surpassed Excel in quite a few areas), I'm hoping that this motivates MS to make more improvements.
Correct, collaboration is much better (realtime editing/chat/cloud storage and sharing).
Admittedly, Excel has a smoother editing experience (which is the real issue here) and is probably better suited for more resource heavy tasks, but Google Spreadsheets excels [intentional] in the following areas:
At one stage, when we were selling a very expensive version of our spreadsheet product, we'd offer a pre-sales service of going through some of a potential client's spreadsheets and converting them to our own system -- the aim, of course, being to show how much better our product was. In one case, a company was using a specific metric to drive their strategy; it was calculated as the difference between the sum of two columns. Kind of, "if the metric is positive, buy, if it's negative, sell". (I'm simplifying, of course.)
Each column was populated with a recurring formula, for example lets say that column A had something like "=D3+E3" in row 3 and column B had "=D3-F3" in the same row, filled down the whole sheet. We factored out the repeating patterns, put them into user-defined functions, and found that we got different results to the original sheet. After spending a while trying to work out what we'd got wrong, we double-checked the original. Halfway down column B it switched from using its own formula to using a mis-copied version of column A's. So half the numbers in column B were wrong. In effect, the metric that the business had been running on for several months had been meaningless.
The company never bought our software, but that may have been because they disappeared a month or so later.
One of the things that most puts me off from using spreadsheets is the difficulty of validating calculations. The formulae themselves are not visible, so it takes a lot of mousing around to convince yourself the cells do what you think they should. For example, I've seen problems where a long "vector" calculation is correct in the upper reaches, and wrong down below, due to failure to completely "fill down" the calculation.
I wonder how many of the spreadsheets studied by Baggerly and Coombes expressed input errors, rather than errors of understanding?