Slashdot Mirror
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."
That makes absolutely no sense at all.
Excel is perfect for creating lists of things, and being used as a way of storing simple data...
If you want to use that data for other purposes or it is at all complex, then sure, don't use excel.
What is a set of numbers, what about a list of data with associated figures, get real...
The only possible explanation I can think of for some of the Excel sheets I have seen in the workplace, is that the poor fool who wrote it didn't have a clue how to use Access. The kicker is when they come to me to write code to automate it. Excel is simply evil. The only thing its good for is making pretty charts.
I really fail to see the point in these posts about a spreadsheet program (be it Excel), not being a database.
Maybe there is a genuine need for a database program (and I use this term here loosely) that provides an interace as easy to use as spreadsheet? Not every user is a programmer, and the vagaries of the any DBMS are well known. Besides, no end-user wants to meddle with software administration.
Maybe the users use it as a database, simply because it provides an easy means of storage and manipulation of trivial data? Not ever user (not in every case, at least) has a million records to work on.
Yes, spreadsheet tools may not have capabilities such as porbability distributions or statistical measures. How many naive users need them? Oh, the average executive might need them to project forecasts, but then, is there a tool that allows this? Conversely, if this limitation has been identified (and I'm sure this must have been identified in the past and by others, as well), why do we not see this being incorporated in any mainstream spreadsheet? (hint: there probably is not enough critical mass of users demanding such a feature).
The other point listed in the article - "the worst nightmare of those who justify IT's return on investment - spending extra money on a more time-consuming product that yields absolutely no measurable improvement?". Well then, perhaps in that given scenario, the need wasn't evaluated correctly? Or maybe such a complexity wasn't required after all?
It's easy to point out the missing features/capabilities from any software, but if it's not asked for by average/most users, it will take a long while to be incorporated (if at all). Yes, this however leaves the issue of errors introduced by the use of such spreadsheets, whether tacit or implicit. In either cases, it would be due to the user being unable to find the right tool to model the problem, or not being able to understand the problem correctly and hence not taking into account as many (if not all) parameters involved.
http://efil.blogspot.com/
Spreadsheets have been and will always continue to be an extraordinarily powerful ad-hoc tool for those wishing to tabulate data with automated calculations. They are worse than useless if, for whatever reason, the user has no savvy approach to the problem at hand, or if the model which requires manipulation has no concrete representation.
After many years with little use for a spreadsheet (previously having used Supercalc and Lotus 123) I was shocked by corporate state of the art. Specifically, I was disturbed by the type system employed to represent cell values and by the way in which formatting settings can so easily obscure the values actually being processed. The way in which Excel handles dates seems particularly horrific... and OO-Spreadsheet just mimics the same mistakes. I was also amazed that modern spreadsheets haven't started to use extensible libraries to represent new data types. It seems a no-brainer for a spreadsheet to make use of pluggable C# or Java classes to allow domain specific types to be manipulated in the context of a spreadsheet environment. Am I missing something - or have we not only failed to advance the art (as suggested by the article) but actually taken several steps backwards?
stop misusing spreadsheets/excel as databases- They are for calculating numbers, not creating lists of things
1. Blame AppleWorks first. Before excel it made spreadsheets like databases.
2. If you look at the history of the spreadsheet, you will see that VisiCalc was designed for "What If?" not large scale calculating work. I was taught that spreadsheets are for the display of information - not calculation.
3. Of course I don't even need a database for storing some kinds of information. An ordinary text file is actually good enough. For example my address book is a text file.
4. I think the greatest misuse of spreadsheets is in using them to consolidate financial data. It's seductive. You get to see what you are doing, you get visual feedback, but
a. data is not protected against alteration
b. formulas are not protected against alteration
c. there is no audit trail
d. you are using explicit formulas instead of looping over data files
5. Lastly, you can say to yourself when you use a spreadsheet, "Look Mom, I'm not programming." Pretty soon you are using Macros, then Word Basic then Visual Basic for Applications. Pretty soon you have a maintenance nightmare since you have spent more time getting immediate answers than you have spent in thinking about design.
6. Yet the usual database products are a disease in themselves. I think that relational databases are not the best for transaction processing. I prefer to use programming languages with built in database support.
7. Last, using a computer gives you the illusion that numbers are real. Printed numbers assume god like authority. But of course projections are not facts or reality, except perhaps in government or the business world!
No. A good statistics knowledge is fundamental in making sure that the data you are putting in is valid. As the old adage goes, "Garbage In, Garbage Out."
If you are just mindlessly putting stuff together and say "I think a median/mean/standard dev would go good here" then it's obvious that you shouldn't be doing statistical analysis. Also, after the numbers have been calculated, you need to understand what the significance of them are. I work in a highly statistical field (Reliability Engineering) and I will say that at times it really is a black art. Things may at first look good/bad, but until you sit down, and think about what it all means, you will have way of knowing whether what you just got out of your analysis is "correct."
Well, for really small lists, it's an easy way to store it. Especially when someone with very little computer knowledge couldn't even begin to create an SQL statement.
How's this?
I created a spreadsheet in KSpread (awesome program, that) to plan my menu for Texas Brand Barbecue. So I used it to estimate all of my costs and my gross sales. I'm a perfect example of who this article is about, but I think I'm above the sort of planning the article is talking about. :) (I intentionally went conservative on sales and liberal on costs. I could be wrong in the end, but if I'm going to err, I prefer to err where my error makes profit rather than loss)
Then I needed a list of equipment to start up and to estimate the cost of all this equipment. So I switch to another sheet in the same workbook and create this list. A quick little formula gives me a total.
Aha, so now I wanted to keep all my data in one place, and the next few pieces of data were tabular in nature, but no formula attached. I needed a list of local area farmer's markets, locations, dates and times, market coordinator, and contact phone number (website and email if available). So what did I do? Well, I made a new sheet in the workbook and put my table there. Now I refer to it whenever I need to call someone on the list, or if a market falls through (that process is over, now) I can easily find another market for that day.
Spreadsheets, as another poster put it, are for the presentation of data. For my purpose, I could've taken a couple of weeks to write a program that would have less than half of the functionality of my spreadsheet, but why bother? The spreadsheet is there to do the job.
This isn't saying that I wouldn't like to have something better, and I intend to home-grow a better solution. First I have to write a driver that will let me download transactions from my cash register to my database, though. The program gets complex after that, but the intent is to replace my spreadsheet with it. (And release it as open source, of course. Doesn't give me a competitive edge worthy of note, and others could benefit by it)
Like what I said? You might like my music
At university, I am taking a course in business modelling. We use Simul8 s/ware to generate thousands of monti-carlo 'runs', then analyse the results as if they were real data.
:-/
...
But it's not real data! It's completely determistic, even with a pseudo-random generator. The only things we deal with are simple supply-chain networks, which are just malkov-chains with a few probability distributions. We're using 2000 pounds worth of s/ware to solve high-school statistics problems
You'd get the same results, and have real justifications for the numbers, by using an HP Calculator and a pencil. Alarmingly our lecturers have yet to explain what any of the distributions mean, but they keep using words like 'proof' and 'verify'.
I'm back to linearly regressing my calculated data. It's insane, they're all insane, one day the sane people will rule, wibble
If you outgrow it, just export the whole shabang... delimited by whatever your database software supports.
You make it sound as though that's a trivial task. It can be as long as everyone who used the spreadsheet was disciplined about how they entered data. The problem is that that is rarely the case, and the spreadsheet doesn't enforce any data types etc.. Converting a series of data from a spreadsheet to a database can be a huge PITA. I've been there, it ain't pretty.
Heck, there have been times I reccomended using excel when getting groups of 10 or more people together doing manual data entry.
I'd be interested to know how you get 10 people sharing a spreadsheet. AFAIK most spreadsheet programs will only allow one person to open it at a time.... Of course then someone else selects the open a copy option because they need to be able to write to it, and you instantly have inconsistency or someone else's changes get overwritten.
No, a spreadsheet is not an option if more than one person is using it.
I lay awake last night wondering where the sun had gone, then it dawned on me.
I saw my first spreadsheet on an old Osborne computer. My dad knew a guy who bought small banks, and he had the Osborne and VisiCalc.
Before this guy could buy a bank, he had to value them, and his valuations were always based on a few guesses (predictions) -- what interest rates would be, or whatever (I don't know exactly how he did it).
He told me that when he started doing this stuff with a normal calculator, a pencil, and paper, changing a guess took him a couple of days. Then he got a programmable calculator, and managed to cut it down to about 5 hours. With VisiCalc, it took a few seconds.
The point being that both the programmable calculator and the spreadsheet software gave him an edge in his work -- they made him better at buying banks. They paid for themselves.
*If* no one is using the sorts of software described in this article, and *if* the software really does make you better at making decisions, people should be able to use it to buy banks (or whatever) and do a better job than their competitors. It should give you a leg up in the market place.
That's exactly what happened with spreadsheets. That's why they're popular. A lot of dumb people have started to misuse them, apparently (that sounds plausible to me), but there's no denying that they have provided and continue to provide enormous value to users.
If this new stuff is better, then why isn't Warren Buffet using it? If the answer is "because he's too dumb", why doesn't someone else start using it, and outperform Buffet?
And then we have these PowerPoint, Excel, yada yada threads where the Slashdot crowd tends to be firmly in the "don't punish the users, it's the fault of these evil software applications" camp.
What's up with that?
Having worked as a front-office developer in a very large bank i can give a good example of how spreadsheets can be misused Excel spreadsheets were used by all traders on the desk i was supporting. They did not want to move to any other tool because only spreadsheets gave them the flexibility they wanted. The spreadsheets were absolutely HUGE, think direction 20 or more tabs, all with hundreds of DDE Links to Reuters RICS - complicated formulas hanging off these links producing tables of data each time a DDE link updated (about once a second on average). We had to install gigabytes of ram and dual CPU's desktops for them just so they could run their spreadsheets. Sure excel would crash every now and then, but not often enough to switch to a new solution.
IT tried to introduce new more stable trading tools without success, not flexible enough-did not calculate "their" prices correctly-blahblah. Controlling tried to impose new tools on them to get a grip on their price calculation- all very difficult when the only data source is a "spreadsheet".
The most insane thing that we tried was to write a spreadsheet parser that would traverse all cells, build a dependency graph, reparse the formulas inside to translate this to another programming language. Needless to say this failed.
I've written a couple of applications that use .xls files as an interface. .xls, so we don't run afoul of security settings. .xls form is simple enough, in MS Access, you can have an .xls link table stub, and 'mount' each response in turn, and excecute straight SQL to read it in. Very fast and secure. .xls explicitely to map the response to the database. .xls forms under GNUMeric with great results. .mdb similar. So the fear factor is reduced.
Idea being that you gan query some relational store, put lists of default values on a back tab, set named ranges to those lists, and then, on a front tab, use data validation to constrain the users to putting Correct Stuff in data rows.
Oh, and there is no macro code in the
This is a back-to-the-future batch system. Blank forms go out as email attachments, and come back as email attachments. They are saved to a folder inexplicably named "inbox". When the time is right, we crack them open in turn and read them into our RDBMS, and then do reporting.
If the
More complicated stuff might require MS Access to instantiate Excel and open each
I've opened some of these
Also, languages like Perl and Python can script COM objects like Access and Excel.
Furthermore, as this is very stand-alone, you could use SQLite without concurrency issues.
The biggest advantage of all is that you've blown off the whole web server mess. Obviously our problem domain is non-real-time, batch-able applications. But there are a lot of those. HTTP is great at what it does, but for shedule requests and what-I-did-this-week inputs (the two applications I've done in this mode), here is a way to do them that doesn't require much that isn't generally available and desktop-runnable.
The other key is that most business people are fairly cozy with a spreadsheet interface, and die rapidly confronted with an
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
Yes, I have a colleauge that thinks like this. The result is an Excel sheet that if you want to make it fit on one sheet of paper you'll need a microscope to read cells. And since he's updating this "information" every week you really would like a sort of diff week-1.xls week-2.xls to find out what changed. My time is too precious to search a thousand cells if they may contain information relevant for my job or not. So this document perfectly fulfills the ISO900x criteria but is not usuable for anyone else than the author.
There was an issue of PC World that came out in late '83 or '84 that surveyed readers on which applications they used in various categories. Lotus 1-2-3 ranked third (something like 17% of respondents) in the word processing category. (This was not a mistake; it turned out that some users of 1-2-3 found it easier to enter a few paragraphs of text into a column of cells and use the Range Justify command, than to exit 1-2-3, change floppies, and launch WordPerfect.)
When I started at Lotus, my wife was a buyer for a local retail chain. She had to do quarterly plans where she distributed a fixed number of dollars over various styles of merchandise among several branches of stores; she had been doing this in rows and columns with pencil, paper, and eraser. I built a model for her to do this using 1-2-3, and several days work was reduced to hours; her peer buyers would visit us quarterly to take advantage of the new tool.
My model was flawed; I formatted the calculated values to 2 decimal places, and 1-2-3 rounded the displayed values accordingly. As a result, the actual sum of a column of calculated values was not equal to the sum of the displayed values. (A further example of the ongoing weakness of spreadsheets, and of my own carelessness; my numeric methods prof would have been ashamed of me.) (It should also be noted that my wife caught the mistake by eye, without even doing the math herself; I had been so trustful of the tools that I hadn't bothered to challenge the results. Another lesson learned.)
Eventually, the store's IT department rolled out their own application on the division's single 3270 terminal. My wife still prepared her model at home (since time in the 3270 seat was hard to come by), and transcribed the printed results into the terminal at work. The IT application required her to enter not only the table values but the calculated sums at the end of each column and row. If a sum did not match the contents of its row or column, the IT app reported an error, but did not provide the correct value, nor even state which of the thirty-odd values was incorrect!
You're endangering my revenue stream.
If it weren't for those organically grown excel/access nightmare programs most companies would never think of hiring a programmer. They hire us after they build those things up to their final catastrophic state and realize that they need somebody to come in a fix it up right.
I don't know about you, but if it weren't for homegrown messes like that it never would have occurred to me that anyone needed a program to import proposed insurance fee schedules and munge those values against previous fee schedules and usage data to decide whether or not a particular contract was worth considering. That sort of stuff isn't normally on my radar. Thanks Mr. P for building that supersized crappy excel program to do that witt, so I could re-write it!