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...
God forbid we break the sanctity of spreadsheets by making lists with them.
Why do you think MS added a list manager to Excel?
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?
I'm guessing that you've never tried to create a table in Microsoft Word. Yeah, it might seem a bit daft, but at least Excel won't randomly change your text styles, automatically adjust the margins so that half the table is off the paper, insert page breaks through the middle of rows, etc, etc, etc.
If I want a decent table in a Word document I have in the past been driven to embed an Excel file in it.
evil math within Nature's Cubic Creation!
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."
I wanted to model the characteristics of a turbocharger I was planning to install in my car. It seemed to me a spreadsheet was the ideal way to try various scenarios. Of course, modeling a turbo requires entering lots of lists of numbers. I had to fight with it, but despite my years of programming experience, figuring out Excel was easier and faster than writing my own custom app for the job.
Turbocharger Spreadsheet
Now I can just enter engine size, compression ratios, etc., select from a variety of compressor maps, and presto - power curves computed without breaking a sweat.
-- *My* journal is more interesting than *yours*...
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...Of course if you're taking the standard distribution of a set of averages then you're safe because the distribution of the mean is almost always Gaussian. but then, you knew that, right?
The real problem with statistics is that everyone thinks they understand them, and almost nobody does (including me, the above is a very hazy memory from high school and my first year of uni).
The author of this post asserts his moral rights.
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
I believe the term for that is 'Spreadsheet Physics'
In the early '90s when I was a student, and teaching physics by computer was still in it's experimental phase, one of the things they had us do was solve simple numerical problems (trajectory of a ball with air resistance etc.) using Quattro Pro. It did work, but it was not much faster or easier than programming it directly in C or FORTRAN and using GNUplot to draw the nice pictures, so as far as I know, they scrapped the program.
... is people who use excel as some kind of DTP package. I used to work in a school, and the deputy principal used to make report cards by typing them into excel, and fiddling around with the cell formatting properties until it looked right. Except it didn't, and when he couldn't make something work he used to DEMAND that I help him (some things were just impossible to acheive without using Publisher or something). The worst year was when he saved them all as CSV files (one of his friends told him it saved space or something) and it was up to me to get them back. Urgh...
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?
So MS dominates the office software market becuase people really need the functionality they offer, right? After all why would anyone want to use something lightweight with a clean UI (like Lyx for example) which makes you use another application just to draw a diagram?
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
This is not intended as a flame or anything, but then what do you recommend?
I use Gnumeric for (among other things) a list of movies I have (about 80-100 rows). The fields are Movie name, category, and who (if anybody) has borrowed it at the moment.
Another spreadsheet "database" I have is an expanding table of the time taken for me to bicycle to work every morning; it is sort of fun (and motivating) to plot the long-time trend. The flexibility of the spreadsheet also allows me to experiment with various ways of displaying the data.
So, for the first application, I could use a simple text file. That would of course not really improve on using the spreadsheet, and the spreadsheet has better UI for editing single fields, compared to a text editor. Using a "real" SQL database, on the other hand, would be horrendous overkill for something this small, as would using some dedicated application just for tracking stuff like this.
For the second one, the choices are even fewer; if i can't plot the data, it's not usable at all. So, no text files, and no generic SQL interface.
So, _what_ should I use to keep track of these lists (and plot and mangle the data, in the case of my travel times)? I haven't found any other tool that comes close, but I am certainly open for suggestions.
Trust the Computer. The Computer is your friend.
I swear to god. You can't make this stuff up. Our financial institution actually ran (past tense -- I'm converting it) a cluster of PCs all running Excel for pricing hugely complex financial products.
After finally getting my hands on the underlying VBA code, I printed it out. It was 56 pages of data movement (copy this piece of data from here to there). The actual pricing code was built as an add-in module and used as a formula.
The only reason for this system's existance is that several years ago someone heard about clustering PCs. They decided that it would be cool to do it with MS Windows and Excel. Gah. It's been an expensive mistake.
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.
I do systems and IC design, RF mostly. A couple of years ago I was doing work for a very large semiconductor company. They were using an excel spreadsheet to do the majority of their system level simulations. It was a very intricate piece of work that had been revised by one individual over many years, spanning a couple of different employers. The company purchased Agilent's ADS and was having trouble getting engineers to abandon their tried and true excel spreadsheets.
I must admit, in some ways, for some simulations I preferred it as well. With ADS most components were black boxes, when input to output didn't behave as expected you didn't have much info to debug with. At least with the excel spreadsheet all the equations were there to study.
It dawned on me about half way through the project that the reason most of the engineers preferred the excel based system was in large part because it was (bear with me) open source. Many times in simulation you aren't sure if the problem is your model, or the simulation package. Analysis was much easier (or trustworthy) when all the calculation methods were easily viewed and tested. (All this is also why I use Octave!)
The author of the spreadsheet is now working at another high profile semiconductor company. Word around the campfire is that they are using the spreadsheet now too.
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!
If they sent it as a PDF, then he'd have never known that they originally made it in excel.
I also work at a printer. Crappy sent in files are a real problem, even though we wouldn't touch a Word or Excel doc with a 10 foot pole, our artists don't even have MS office installed.
I've had enough abrasive sigs. Kittens are cute and fuzzy.
Is there any WYSIWYG spreadsheet like front end to SQL? Not something that offers spreadsheet capability but allows you to use a an SQL database in the same way excel is misused as a database.
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!
What truly functional advancement has Microsoft made to Excel in recent years? It seems that they make a few bug fixes, slap the newest Office look on it, and push it out the door. The advancements in spreadsheet functionality were made by other companies, which MS copied into Excel (pivot tables is an example). The one real exception to this is the VBA macro system. I find it useful for data reduction, and it remains the one reason I will not switch to other spreadsheets.
I just wish some other company would make inroads in spreadsheet design and either push Excel to improve or topple it from its current perch.
For the love of God - Stop using Access to store critical information. Use a spreadsheet. Then we'd stand a chance of getting your data back.
What I don't understand is: they make us software developers use change control (for good reason), but upper management builds their business on this fragile house of cards spreadsheet system.
Is there a good change control system for spreadsheets? Sure, we could treat the xls files as opaque binary files, but that's losing most of the power of the change control system. I'm sure it's out there. Pointers anyone?
Bryan
Yes, yes. And programming languages with only gotos are inherently evil. But with a discipline on the part of the user it is possible to build maintainable systems.
Very true, and I have seen it happen any number of times. It's always a good idea to build in validation checks that provide some feedback on how "sensible" the numbers coming out are.
Well, yes and no. The "trace dependents" and "trace precedents" functionality in Excel can be quite handy for that kind of thing. I've also seen a regression testing tool (developed in VBA) that checks one spreadsheet against another to ensure that the page-to-page links have remained the same (i.e. the interface is consistent) and flags any changes. Detailed validation of the numbers produced by each sheet still had to be done by hand, but it was setp in the right direction.
No argument there.
That depends to a certain extent on how you are using them.
While spreadsheets get their most heavy use in the financial world, they have gained a lot of popularity in the engineering world, particularly for doing the computerized equivalent of "back-of-the-envelope" calculations. While I was initially pretty skeptical of this concept (being a diehard Matlab fan), I have to admit that for quick exploration of various design options using simplfied models things like Excel are king. Most of the value comes from the same things that are cited as problems for the financial world: that its easy to tweak your assumptions until you get the results you want, and do it with immediate feedback. Bad for the financial world perhaps, but in the early requirements/design phase it becomes an incredibly valuable tool for rapid trade-space exploration. I guess the difference between engineering and finance is that in engineering the resulting numbers are validated in later design phases that use more rigorous tools - that helps to filter bad assumptions before they produce disasters. This kind of spreadsheet-driven conceptual design is very popular in the aerospace industry: JPL, NASA-Goddard, NASA-JSC, The Aerospace Corporation, ESA, NGST (ne TRW), Boeing, Ball Aerospace, Spectrum Astro, and AFRL, to name just a few, all use some variant of it in one way or another (often in teams that use inter-linked workbooks).
I would berate anyone who used Excel IN PLACE OF what a database should be doing.
If you have a small team or just one person using a spreadsheet to make sense of numbers, that's fine. If you're storing numbers that many people have access to/crunch; you generate reports from those numbers and many people have access to the underlying numbers (and the code to generate the reports); or several other scenarios - you're using the wrong tool. Yes, the spreadsheet can do it - and it can work - but taking the extra time to do a DB and write code to do what you're doing through a spreadsheet will ultimately save you time and money.
Case in point 1 (spreadsheets used incorrectly): buddy of mine works for a car dealership. Someone wrote a monstrosity of an Excel spreadsheet that crunches numbers and creates reports. Every time they change the report layout or the way data is calculated my buddy has to scramble around updating all of the desktops with the new spreadsheet (yes, its on a share, but he still has to scramble around to fix issues)
Case in point two (a decent use of a spreadsheet where a more complex tool could do the job): My team has an objectives list, the tasks, who the tasks are assigned to, dates, etc. in an Excel spreadsheet. "Ah ha!" you say - that's something that should be in Project or in a database.
Why?
Project is overkill for what we - the team members - do. And its damn expensive to put on each of our desks. We document the tasks (and add links to the docs as we write the docs); sometimes we split the tasks up; and we mark down when we start and complete the tasks. That's our objectives for the spreadsheet. Da boss man (project manager) takes what we have in the spreadsheet and updates Project to do time forecasting, hour tracking, etc. (project manager does stuff we don't need to do - we just want to know what the task is, where the specs are, who is doing something, has it been done, etc.) Yes, there is duplication of work (we're entering some things twice, in effect) but Project doesn't (easily) do some of what we need to do and for the rest of what we need in "task list" Project is overkill.
My job is to look at the job, decide what tools I could use, and then to use the right tool for the job. I can often use flat text files, relational databases, spreadsheets, HTML, CSS, Java, C, C++, RPG, etc. - my job is to decide (based on known requirements and based on what I think future requirements may be) what to use. I very very rarely would recommend a spreadsheet to do anything complex. It's too hard to maintain data integrity; it's too hard to maintain version control; it's just too hard to maintain in comparison to some of the other choices. It will work for one time projects or small scale. It will even work for large scale. But just because it will work doesn't mean its the best solution.
"excel should NEVER be used to calculate ANY statistics beyond a mean, since it uses patently WIERD formulas that DON'T always work (compare the answers against those gotten with SPSS or SAS for more complicated work and you'll be shocked)"
amen to that brother,
And I agree that stats packages in the hands of people not familiar with statistics is a disaster. I see it on an almost daily basis, where people have the stat package in front of them and just assume the output is correct without any understanding if the numbers make sense or not. They just go right down to the p-value and call it day. It's almost shocking to me the errors I've seen people make in their data entry and analysis that simply go unchecked. And i've seen very bright, competent people fall into this trap time and time again. It really makes me question just about all research in any field for reasons too numerous to mention here.
And forget about using Excel to do stats (which is a horrible idea). Even stats packages in the hands of knowledgable users can be dangerous. As an example, a few years back I was learning Minitab and SigmaPlot. I had some analyses from an SPSS run that i was running through Minitab and SigmaPlot for nerdy interest. Low and behold, the results were different. Fortunately, two of the three matched (SPSS and SigmaPlot) which led me to believe Minitab was wrong in its calculation. And this wasn't some esoteric procedure. This was a curvilinear multiple regression. So I email Minitab with all my results, programs, and data. Sure enough, there was an error in their calculation of the R^2 which they knew about! They just hadn't issued a patch yet, and worse hadn't made their users aware of the problem.
So now as a rule, I try and double check everything I do, whether it be with SPSS, SAS, Minitab, EpiInfo, Excel, etc... to ensure this doesn't happen again.
jeff
1. Blame AppleWorks first. Before excel it made spreadsheets like databases.
I believe Lotus 1-2-3 predates AppleWorks. It combined spreadsheet calculation, graphing, and data management into one program. Its success in the marketplace spawned many other "integrated" programs such as AppleWorks. Now that multi-tasking operating systems and inter-application communications are the norm, such integrated applications have dropped to the status of budget-buy suites.
(re Lotus) I always admitted that I was confused -- if 1-2-3 is so great, why is Lotus selling Improv? And visa-versa.
It's one thing that Lotus never seemed to understand -- the power of network-marketing. Every time they came out with something new, they started with zero, rather than huge installed base of Lotus customers. It would have made a lot more marketing sense if Improv was a module for or bundled with 1-2-3 -- even if that wasn't the most technically ideal solution.
You are right on. I work with spread sheets from time to time and Autocad. They both have a similar problem when you get a complicated project. Without a clear normalized data structure you end up making a mess. People end up linking documents in ways that are incomprehensible to anyone but the original author. In the end, sometimes even the original author has problems. Finding errors is impossible. This may work for small projects, but for large team based projects it is a nightmare. People don't think of it as a database, if they did it would probably be more organized. Often times people manhandle a quick fix, but that just makes matters worse in the end. It is a huge waste of time and leads to shoddy quality. The biggest Excel error I have seen was made by a contractor for a cool $1 million. What do you say to that? OOOPs, my bad!
Excel charts are generally horrible- the default values tend to include extraneous "chartjunk" (to borrow a word from Tufte). It is tedious to get a nice-looking chart from your data, and seems to be very difficult to produce any even mildly-complicated graphics. I use R for charts, and I'm familiar with several linux charting apps of varying degrees of complexity, but I'm not sure whether there are any good OSS apps for Windows.
A much bigger problem, though, is Excel's lack of statistical quality! This website provides a quick overview, with links to some more detailed references. Excel is occasionally accurate for simple analyses, but why on earth would you use an unreliable program for _anything_? The only way to be sure that Excel did your ANOVA or whatever correctly is to redo it in better stats software, and at that point I don't see the advantage.
This is an issue that comes up regularly on scientific mailing lists. Lots of people seem to take the path of least resistance and use Excel for both their analysis and presentation. Ick!
I prefer to be called Evil Scientist.
We developed a web-based application that used Excel to format invoices for the clients. No computation was done by Excel. That was all done by the business logic using data stored in a real database. Excel was used entirely for its formatting capabilities. The really nice thing was, each customer could specify, through an administrative form, how they wanted things laid out (what data goes into which cell, where to display the company logo). When they wanted to print an invoice, they just clicked on an image button, Excel popped up with all the data filled in, and they could modify and print from there. The customers really liked it. As others have stated, it was something they were familiar with.
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
Arrrghhhhh! If you've ever lived through an entire department sharing an evolving unprotected spreadsheet with a new tab per month, used to generate monthly, quarterly and annual management reports and criefings for senior management, and had some *helpful* person decide to *update all links*, you have truly known the meaning of despair.