Microsoft Excel

From Slashdot – $10B Annual Tab for Spreadsheet Errors

Not surprised (Score:5, Insightful)
by slamb (119285) on Sunday April 24, @09:23PM (#12333077)
(http://www.slamb.org/)

Most programmers have no little use for spreadsheets, so we don’t know how bad they are. We’ve got a lot of principles that we apply to our own work, but we don’t see that the business people are struggling with the same problems without the proper tools.

I made a couple spreadsheets recently, and here’s what I found:

Fragile references

They’re still referencing virtually everything by [A-Z]d+. This is beyond GOTO considered harmful – when Dijkstra made that claim, we at least could do “goto blah” instead of “GOTO 2050”.

Excel has a couple “solutions”, neither of which are good:

  • You can assign names to cells, but not in a way like “the total of the yearly column of the expenses table”. Even if it’s in the List Manager, there’s still a [A-Z]d+ cell reference between.
  • It has relative and absolute references. Relative ones will basically update correctly when you move the source. Both kinds will basically update correctly when you move the target. They’ve made some effort to make range references expand and contract, too. But it’s a heuristic; it’s guessing information it doesn’t really have.

Massive code duplication

In my spreadsheet today, I ended up with whole columns of formulas like this:

=VLOOKUP((A4-FedStdDeduction),FedTaxRates,2,TRUE) +((A4-FedStdDeduction)-VLOOKUP((A4-FedStdDeduction ),FedTaxRates,1,TRUE)) * VLOOKUP((A4-FedStdDeduction),FedTaxRates,3,TRUE)

I would have much rather made a function FedIncomeTax(AdjustedGrossIncome) that applied that same bracket logic. Once. And called it the N times necessary. You can define VBA functions, but I didn’t see a way to reference cells from them. (Probably because it doesn’t have a reliable way to do the dependency/error tracking seamlessly. I can think of how I’d accomplish that in Python…but Python is a very flexible language.)

Unreadable code

There’s no way to put longer bits of properly-indented, commented code in there. Certainly related to the above; you’re trying to cram way too much stuff into a cell (or group of cells) that’s massively repeated, so no one even thinks of doing this.

Poor layout

The result looks poor in a couple ways:

  • There’s no automatic greybarring (alternating light/dark backgrounds for rows). So you can get lost when reading a big table.
  • An entire worksheet column has the same width. If you have two lists on the same page, inevitably one of them will end up with an awkwardly-sized column.

Poor charting abilities

It didn’t have much support for charts with confidence intervals. (Don’t tell me there’s no use for these in finance! They may write everything out to the nearest cent, but that doesn’t mean they don’t made wild-ass estimates when talking about the future.) If you want to do something like a box-and-whiskers graph, you have to do elaborate tricks [decisionsciences.org]. Even basic error bars have weird defaults; to get a meaningful confidence interval, you have to do custom stuff with ranges. The friendlier check boxes end up with the same-sized error bar for every point, which is worthless.

Overall

Just using Excel for my small needs was frustrating, and it’s not because I don’t know how to use it. (I can read Help files.) I can easily see how people would screw up badly with them and not notice.

It’d be so much better if there were a more free-form document (no overal grid) you could throw 1-dimensional lists and 2-dimensional tables into. With support for formatting, referencing, and summarizing them well. (There shouldn’t be [A-Z]d+ references at all; the concept shouldn’t exist.) Including the PivotTable stuff, of course. (Excel’s one good point, though it could be better.)

It also should have support for referencing external data easily – a RDBMS or CSV/XML/etc. files. Business people pass around spreadsheets and lose track of what’s the most current version, and that’s ridiculous. When they do multi-person projects, they should be able to separate the data from the logic and layout. (Excel sort of has this, but it was buggy when I played with it.)

It’d be sort of a cross between Access and Excel, I guess. It would make you formalize lists, but you could still edit the lists directly in the printable document. And you’d need decent charting capabilities, which IIRC Access doesn’t have. And support for two-dimensional data (PivotTable-like reporting transformations from one-dimensional data, or storing two-dimensional stuff directly).

Apparently the old Lotus Improv for NeXTSTEP was more like this. It’d be amazing if Apple brought it back in iWork.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

Comments are closed.