The Subtle Tyranny Of Spreadsheets

pipingguy writes "I found this link on a CAD-related mailing list which questioned the current state of spreadsheet usage. Since using spreadsheets is often only one step away from PowerPoint mastery, I thought it worthy of submission." An excerpt: "The second distortion caused by conventional spreadsheets is more subtle. It's described in a 1980s paper, written by university researcher Jeffrey Kottemann and others concerning what they called 'Performance, Beliefs, and the Illusion of Control.' The paper described an experiment in which subjects were asked to perform a planning task using different tools, some of them with elaborate what-if capability and others without it." Yup, it's a ZD/Yahoo link, but it raises good questions."

Comments

A better article on the same point...

[Singletoned]

"When you're holding Excel, everything looks like a spreadsheet" by Yoz Grahame

I particularly enjoyed it, and it made me wonder why I've always hated Excel. maybe it's time to forgive...

(I always used to like Pipdream on the Archimedes though. That was a combined spreadsheet and word processor).

"Powerpoint Mastery"

[foobsr]

Since using spreadsheets is often only one step away from PowerPoint mastery.

Erm .. for "Powerpoint Mastery" have a look at Tufte "The Cognitive Style of PowerPoint".

Yes, I know it was discussed here before (as I guess), but still - it is worth a mention.

CC.

Why not to use Excel as a DB

[linuxtelephony]

One of the biggest reasons is the sort function combined with [l]user error.

If a spreadsheet has more columns that fit on the screen, and is used by more than one person, at some point you can almost count on someone highlighting some, not all, of the columns and then sorting the highlighted columns, and saving the file. When that happens, the highlighted columns are sorted, the rest are left as is. Worst, the next person to use the file doesn't always realize the corruption has occurred.

This was a problem in Office 97 and earlier. I think it was a problem in Office 2k, but I don't remember. I have not tested this on Office XP or 2003.

Re:please everybody

[ChiaBen]

You shouldn't be forced to use SQL for manipulating data, you should be restrained from using Excel. ;) The reality of the differences between a spreadsheet and a database is that a spreadsheet lacks the data constraints (relationships) necessary to keep a user from entering bad data. A database can control this (data integrity) to a large degree (depending on your datamodel design).

An example I fight with daily is product attributes. I maintain a n ecommerce database with about 180,000 products, each of which would have, say, a color. The problem is that if I import data from a spreadsheet it might randomly insert spaces in the data (i.e. "Black " or " Black" instead of "Black"), whereas if I get the data entered through our tools, the user selects from a list of colors, and only if the choice doesn't exist do they add a new one.

You mention how people are doing a knee-jerk that 'DB's are sacred'. Yes, they are. So are spreadsheets, the problem is that people bastard-ize their use and end up confused about why they both exist, and how to use them.

Database = Data storage, data consistency, ease of data maintenance
Spreadsheet = Data analysis, data redundancy, lack of data integrity.

That's how I see it, anyhow.

Re:please everybody

[morelife]

I use Excel constantly to do layouts for invoices, estimates, cards, presentation, etc. because of the precise sizing control. It looks professional, not cheesy at all, some of the stuff looks like it came from a printer.

As for the database aspect, Excel is well suited for a database table layout, that's one of it's principal uses. Not a relational database, but just simple tables, it great at. There's no reason you couldn't have an address book with hundreds of entries and a dialog box front end made with macros. I did this in the past, worked great.

Re:please everybody

[generic-man]

PDFCreator gives you "print to PDF" capability in Windows. It's free software.

Re:The underlying problem...

[mst76]

Do you really know what the standard deviation is? For example, you know that most standard tools for calculating standard deviation it assume the data has a Gaussian distribution. But what if your data poisson distributed, or hypergeometric, or maxwell-boltzmann... [...]

That is not correct. The standard deviation of a random variable is the square root of its variance. The variance is the squared expectation of the centralized variable (variable minus its mean). Calculating the variance of a random variable involves integrating (or summing) the probability density function p(x) times x^2. If it's a well known distribution, one would usually look it up in a book, or try to solve the sum or integral (by hand or with Maple or Mathematica). Note that the only thing you need to calculate the standard deviation is the distribution or density function, no actual data is involved.

The things listed as mean and standard deviation in Excel are sample means and sample standard deviations. If you have a list of numbers, and you assume that they were drawn from some distribution with finite expectation and variance, you can calculate the sample mean (simple average), which is an estimate of the expectation. Then you substract this mean from all your numbers and take their squares. The average of that is an estimate of the variance of the distribution, take the square root for the standard deviation. The nice thing is that these estimates converge to the expectation and variance regardless of distribution. If you do make the additional assumption of Gaussianity, you can also say something about the rate of convergence and the distribution of the estimate. But the basic formulas in Excel and such for sample mean and variance work for any distribution that has a mean and variance.