The Subtle Tyranny Of Spreadsheets 554
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."
please everybody (Score:3, Insightful)
Re:please everybody (Score:5, Interesting)
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...
Re:please everybody (Score:5, Insightful)
Re:please everybody (Score:3, Insightful)
The best solutions with complex data are to embed or link to 'real' databases (or even other spreadsheets, they are after all just tables) from within the spreadsheet... But if you want to get so sensitive about it then you would only ever use a spreadsheet as an analysis tool, and never enter data.. which I think we would all agree is stupid for simple tasks
Re:please everybody (Score:5, Interesting)
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
Re:please everybody (Score:4, Interesting)
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.
Re:please everybody (Score:5, Insightful)
Excel as a simple database has a number of advantages. It's portable - most business users have access to Excel, and OpenOffice imports Excel sheets quite well. It requires limited knowledge to get some use out of it. Even unsophisticated users can usually manage a simpe search for the data they're looking for, and can update records. A well designed Access database can be easy to use, but it's much less portable. Not all users have Access, and I'm not aware of any Linux app which will open an Access database directly. (You can export the database from Access and then import it into MySQL or other database server, but that's obviously a great deal more work.) A database run on any of various servers with a custom front end, either web based or not, can be easy to use but is obviously much more difficult to attach to an email.
If you need the power and robustness of a relational database, then use one. But for simple data collection functionality, particularly when portability is an issue, a spreadsheet works well.
Re:please everybody (Score:3, Funny)
Re:please everybody (Score:5, Funny)
No, they didn't want printed spreadsheets - people would lay out flyers, leaflets, posters and small booklets in Excel.
I can only guess their creative genius had to be instantly addressed and they picked the first app they could think of to lay it out on, and excel was just sitting there loaded at the time.
Re:please everybody (Score:4, Insightful)
It was horrible, and frequently they'd managed to change one small thing that would completely screw up the proportions of their worksheet.
I suggested several times that they got Visio or Autosketch or something, but they were too tight to pay for it, despite their average chargeout rates being 70gbp/hour and doing jobs worth 6 figures or more.
Re:please everybody (Score:4, Informative)
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 (Score:4, Funny)
Re:please everybody (Score:4, Informative)
Re:please everybody (Score:5, Interesting)
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!
Re:please everybody (Score:3, Informative)
Add
All this script did was make a new script file called "key" which echoed the value.
So if you just typed the key at the command prompt the value came straight back (of course the *nix cmd line offers many variations for retrieval!).
Dread to think about inode usage if you did this on large scale thougth!
Re:please everybody (Score:5, Insightful)
This is a common thing, in my (corporate) experience. Not much thought is put into how the business fundamentally goes about its tasks, but there is a lot of time spent, e.g., masturbating with time sheet data for salaried employees, etc.
Making things worse, Microsoft's tools encourage instant gratification over design: VBA, Office Macros, ASP and Visual Basic lend themselves not to rapid application devlopment, but stupid application development. It's so easy to tweak and reload that the "right" answer often ends up being the "easy" answer. It's development by instant gratification. The resulting "solutions" are often fragile and difficult to maintain. It's like Powerpoint for Programmers (referencing Tufte), in that the cognitive model of the tools distorts the outcome as much or more than it helps produce it. I'm not convinced that these convenience tools result in less time spent in development, either; quite the opposite. I think any amount of time spent in design and planning will be outweighed by all of the re-work that will usually have to be done because of the mindset the tools engender. This is overlooked because planning isn't a source of instant gratification (it seems to drag on forever, as it requires actual thinking) -- whereas development with tools like these is a source of instant gratification, thus masking their own consumption of your time.
Re:please everybody..stop (Score:3, Interesting)
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
The Microsoft Fat Chicks (Score:3, Funny)
Re:please everybody (Score:3, Insightful)
but stupid application development.
Stupid, yes, from the standpoint of maintainable code, efficient use of computer resources, best algorithms, etc. Absolutely stupid.
However, Stupid Application Development, however SAD, is often very useful in getting answers right now for people without a clue about intelligent application development, i.e., most of the people sitting in front of computers these days.
I think the best you can do under these circumstances is to have the underlying tools be more modular
Re:please everybody (Score:4, Insightful)
Technologies cited may lend themselves to myopic, tactical uses, but that is an unavoidable side effect.
You could, in the same spirit, blame the vagina for prostitution.
Furthermore, you don't offer an alternative. Do you want an MSWorks-type dumbsheet for the masses? What reasonably useful system do you propose when the cheesesheet isn't packing the heat? Something with an Emacs-derived keyboard interface for macro coding to keep out the riff-raff?
What about the heuristic problems that are simply going to be a muddle while requirements evolve, where total hackability is a feature? We treat design as some sort of Revealed Truth, a magic wand that will Save Us From The Fury of the Spaghetti Code. Ahem.
As noted elsewhere, making it easy for the usele^H^Hr to drum up business is far more feature than bug.
Re:please everybody (Score:5, Insightful)
When we ask our users, "why?" the answer is always "it's too much trouble to deal with you technology folks." They're willing to forgo robustness, auditing, data validation, etc. in order to escape the technology bureaucracy: Getting budgets and resources, all those damn planning meetings, dealing with System Administrators, and so on. They generally know the risks and limitations of using Excel but feel the advantage of getting quick results is entirely worth it.
Re:please everybody (Score:4, Insightful)
Microsoft have basically forced Excel to be the packhorse of data manipulation for the masses.
Re:please everybody (Score:4, Insightful)
Re:please everybody (Score:5, Funny)
Re:please everybody (Score:5, Insightful)
I don't understand the problem using excel as a small database. If you outgrow it, just export the whole shabang... delimited by whatever your database software supports. Heck, there have been times I reccomended using excel when getting groups of 10 or more people together doing manual data entry. The data gets entered, it's organized, and easily incorperated.
No, excel is not a database, but a spreadsheet can be used for more thens then calculating numbers.
Re:please everybody (Score:3, Interesting)
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
Re:please everybody (Score:3, Insightful)
Christ, you sound like a manager
Re:please everybody (Score:4, Insightful)
Re:please everybody (Score:4, Insightful)
Could you kindly expand on your argument?
There are already a lot of posts berating the use of Excel as a database. Yet, I have not seen a single clear argument why this is a Bad Thing. The closest someone has gotten to is saying how users might inadvertently delete columns or add unwanted formatting, etc.
That's really just the fault of the WYSIWYG mentality of MS Office applications (in certain cases, the formatting is a bonus, as you'll see below). I don't see anything inherently wrong with the "spreadsheet as a DB" concept.
Seriously, a spreadsheet IS a DB. Its rows and columns perform exactly the same functions as a DB's rows and columns. While a DB might have more features, such as primary keys, indexing and fancy querying, a spreadsheet fits the role if you don't want those extra features.
I should know. I use DBs extensively (MySQL and Oracle). I also use Excel quite a lot. I am in the statistics and decision analysis field, so I use DBs and Excel for a lot of number crunching.
But I also use Excel to store small lists. For example, I have in front of me a sheet containing conferences and journals (that are relevant to me), ordered by due date. Excel's conditional formatting allows me to highlight those conferences that are due soon and grey out those that are past. With a single click, I can sort based on other columns, such as ranking.
I fail to see why I should be forced to use the cumbersome SQL interface to do this. Unless I spend much time writing the necessary scripts, webpages and CGIs, I am not likely to get the same flexibility I have with Excel for manipulating the list. Excel does the job for me, with minimal effort.
I think a lot of people complaining here are doing so knee-jerk. Somehow, the attitude is that a DB is "sacred" and Excel is a bastard child. This is wrong. A DB is just whatever fits your purpose for storing data (or lists or whatever). It can be an Excel spreadsheet, an RDBMS, a flat text file or even an opaque file (think Data.fs in Zope). The wise man uses the right tools for the job, and doesn't slavishly adhere to misguided prejudices.
Re:please everybody (Score:5, Insightful)
There are already a lot of posts berating the use of Excel as a database. Yet, I have not seen a single clear argument why this is a Bad Thing. The closest someone has gotten to is saying how users might inadvertently delete columns or add unwanted formatting, etc.
---
Some people are too hung up on what something was designed for, and overlook what it could be used for. Presumably they're against the Wright brothers use of bicycle parts for the construction of the first plane also.
Why not to use Excel as a DB (Score:5, Informative)
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 (Score:5, Informative)
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 (Score:4, Insightful)
Obviously you have never dealt with trying to give the clients what they want after someone else has polluted the database with their crappy imported data.
Here's a real example: our clients wanted to find all the KING size bedsheets. We looked them up and found that we've got those in 'KING ','King ','KNG ', 'K ', '76X80', and ' KING'. Sure, we had ten KING sized bedsheets that matched their request. But when our providers complained that their clients couldn't find the king sized bedsheets in these other styles, we had to point out that they filled the database with non-normalized values. Their solution was not to normalize their data ("that is too much typing for poor us, boo hoo,") but rather to tell us to give the clients a pull-down with every size of bedsheet we carry so they can pick it themselves. So we did, and now all the clients have to sift through literally 20 different abbreviations for four standard sizes. And God help the poor customer who just wants a king-sized bedsheet.
In the case you mentioned, you are a provider of data, not a consumer. As such, you are responsible for providing valid data; that is, data that will work for the consumers. In many (most?) cases, the data providers are not the data consumers. So there is a burden of responsibility on the providers to use data that will make sense to the clients. In our business, we have hundreds of providers who are assumed to be knowledgable and responsible for this data. We have tens of thousands of clients who are just trying to do their jobs, and finding things like king sized bed sheets is just one tiny, untrained, unrewarding aspect of their day. If you give them a search box and they type 'KING' and get many results (but not the one they want) they will rightly assume they did everything right and that the merchandise doesn't exist. They made no mistake, other than trusting that the data made sense.
And you whine because your DBAs won't let you import an unchecked spreadsheet. Cry me a river pal, but get your ass typing. Your unverified data is worse than worthless. It makes the real clients look stupid in front of the customers. And if that's not enough to make you care, look at it this more selfish and pragmatic way: if the clients can't find your merchandise, they can't sell it. You'll be the one justifying to your boss why nobody sold any ' KING' sized sheets.
Re:please everybody (Score:4, Insightful)
For a quick list of some sort it is generally OK.
On the other hand, I've seen tons of spreadsheets with columns like:
Home Phone, Home Address, Business Phone, Business Address, Home Phone 2, Home Address 2, etc...
In cases like that the contact info should be in a child table with a 1-to-many relationship. Actually, if you have multiple customers in the same household maybe it should be many-to-many...
And that is where databases come into their own - they encourage better design of how data is stored, and when the database grows it makes data a lot easier to get at and manipulate.
If you only have 100 rows it really doesn't matter what you store it in. You probably would be able to store it with paper and pencil with little trouble...
Re:please everybody (Score:3, Interesting)
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 w
RTFA: *nothing* to do with using spreadsheet as DB (Score:3, Informative)
It's not too hard to appreciate the difference between products that incorporate uncertainty and those t
Re:please everybody (Score:3, Informative)
A real life example that still gets on my nerves to this day is when a co-worker in different department who knew I was a "computer person" a
Re:Mods, please mod parent up.What, no Tux? (Happi (Score:5, Funny)
Re:Mods, please mod parent up.What, no Tux? (Happi (Score:5, Funny)
What's so strange about that? Both are highly unpredictable, so it should work pretty well.
Re:Mods, please mod parent up.What, no Tux? (Happi (Score:3, Interesting)
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.
Re:Mods, please mod parent up.What, no Tux? (Happi (Score:5, Funny)
What if it was Gnumeric? (Score:5, Insightful)
The funny thing is that while everyone is going to look at this and say that it is ridiculous, and it is, think what people would say if it had been done with GNumeric. The Slashdot headline would read something like "Cool Hack Let's You Play Pacman in GNumeric" and there would be 300 comments saying how cool it is. Another 50 comments would say that the guy has too much time on his hands. People would talk about the awesome power of GNumeric but, no one would complain that it was an absurd abuse of Gnumeric as they are here about Excel.
Just some perspective.
Sometimes a little education is worse than none (Score:4, Funny)
Which he had calculated by summing the profit column and dividing by the number of divisions.
I mentioned that this was producing a somewhat unrealistic figure, with a couple of small divisions showing very good profit margins and the largest department showing a slight loss. "No, that's the mathematical definition of 'median'," he answered.
Re:Sometimes a little education is worse than none (Score:3)
Spreadsheets in the workplace (Score:3, Interesting)
The underlying problem... (Score:5, Insightful)
The question is whether a tool can ever be a substitute for a good understanding of statistics and probability - or whether it will always be a case of monkeys playing with ever more sophisticated typewriters...?
Re:The underlying problem... (Score:5, Interesting)
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."
Re:The underlying problem... (Score:3)
I am in another fairly statistics heavy field, but one where many people are not mathematically inclined. This leads to a lot of people doing exactly what you describe.
Personally I think even tools like SPSS (which is heavily used in my field) are dangerous because they lead one to doing analyses that don't make any sense given the data. SAS is better, as is S-Plus/R, since these require some un
Re:The underlying problem... (Score:5, Informative)
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.
Re:The question does not deserve mod pts, but answ (Score:4, Informative)
that's what wikipedia is for
it's a measure of the width of the distribution. Given a gaussian distribution, a random measurement will occur within one standard deviation of the mean with a probability of around 68%. Or to put it the other way round, if you have data and are trying to calculate the distribution, there's about a 68% chance that the true value of the mean falls within 1 s.d. of the value you calculated.
If you don't have a gaussian distribution, you can still calculate a standard deviation but it will not have the same meaning with respect to the probability of you having got the right mean.
This is of particular relevance to spreadsheets, since they're often used to do calculations on financial data such as stock prices and most financial data is not gaussian--it's 'log normal', meaning that the logarithm of the data values are gaussian, but not the data itself. So most people doing standard deviation calculations on such data are probably completely misinterpreting the results...
The Bosses don't want to hear probabilities (Score:4, Insightful)
Unfortunately or not, this is not what the bosses want to hear. They want to know that profits will be $1 million. Perhaps the spreadsheets have not adapted to uncertainties for a reason.
The cost of everything (Score:5, Insightful)
This reminds me of something a successful businessman told me about accountants: "Accountants know the cost of everything, and the value of nothing".
A problem occurs when people look at a spreadsheet of accounts and think it represents a business. It doesn't. A classic illustration of this is Marks & Spencer's returns policy. If you buy a pair of trousers from Marks & Spencers and then once you've got them home decide they don't fit or whatever, you can return them, no questions asked. To an accountant, this is just a cost. There is no identifiable figure in the accounts that you can point to and say, there's the benefit of that cost. And yet many people shop there because of the policy.
R&D (Score:3, Insightful)
Another thing that suffers from this type of mentality is long term R&D. Japan has had many very long term R&D projects which has been criticised by outsiders as being too long term.
I've just been watching a Japanese robot demo on the TV. Very impressive. I think the fruits of there long term investment in robotics R&D will be seen in the next decade.
Re:The cost of everything (Score:4, Informative)
'Fraid your friend's not very original. The original quote is from Oscar Wilde: "a cynic is a man who knows the cost of everything and the value of nothing"(
Boneheaded AD undercuts itself (Score:5, Insightful)
I dont know anyone who uses their spreadsheets for doing any kind of predictions. Everyone I know uses it just like the old-fashioned pen-and-paper..spreadsheet! Its a way of accounting for the here-and-now. How many businessmen don't understand their business prospects better than a garbage-in-garbage out number crunching computer?
A spread sheet is not... (Score:5, Insightful)
http://www.wch.org.au/CEBU/software.htm
I guess it's kind of like trying to write HTML with MS Notepad. It can be done, however other tools make the job easer.
Problem is the type system? (Score:5, Interesting)
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?
Problem is intractability. (Score:3, Insightful)
The limit of Engineering as GPA goes to zero is MBA.
Typically, math is the skill that drives that GPA down. OK, the bad joke is starting to look like a flame, and it's true that clueless big dogs with their sensless five year plans make me angry, but please - this is a joke. Everyone has got their skill
Financial Planning (Score:5, Insightful)
A better article on the same point... (Score:5, Informative)
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).
Sometimes it's expedient (Score:3, Interesting)
Turbocharger Spreadsheet [comcast.net]
Now I can just enter engine size, compression ratios, etc., select from a variety of comp
Indoctrinating Excel (Score:5, Insightful)
So far I have seen Excell used for issue mangement, system requirement repository, time tracking, time estimation, code dependency tracking, system reference data and configuration data repository, ...
..and in 99% of the cases the spreadsheets don't even use the SUM function.
Excel isn't a DB! It's a FS! (Score:4, Informative)
Excel 2000? Under file menu, do 'Save as Web Page'. Say 'Publish Sheet' and 'Add Interactivity'. Save to some htm page on your drive. Load the htm page with IE (don't give me any grief over this one- you're already screwing around with Excel so I don't want to hear it ). You should have Excel in the middle of the page. Scroll to row 2000, column WC. Select row 2000, and tab so that WC is the active column. Hold down Shift+Crtl+Alt nad click the Office logo in the upper-left. If you have DirectX, you will be playing what looks like spy hunter. Use the arrow keys to drive, space to fire, O to drop oil slicks, and when it gets dark, use H for your headlights. -B
Suspect citation (Score:5, Insightful)
A quick google search reveals evidence of only one paper (but not the paper itself, unfortunately) entitled, "Performance, Beliefs, and the Illusion of Control", see, e.g., here [umd.edu]:
Kottemann, J.E., Davis, F.D., & Remus, W.R. (1994). Computer-assisted decision making: Performance, beliefs, and the illusion of control. Organizational Behavior and Human Decision Processes, 57, 26-37.
Note that this paper was published in 1994; it's not a "1980s paper" as cited in the article. Careless errors like this make one wonder what else in the author's train of thought is similarly researched. Perhaps he's just incorporating incertainty into his references, too--or, maybe he considers 1994 to be statistically similar to the 1980s?
Problem with spreadsheets (Score:5, Insightful)
I put "enhancements" in quotes because I am skeptical that this actually represents a true improvement of either the quality of the information or user efficiency in finding and using information.
These so-called improvements gloss over the continuing problems that plague spreadsheet users:
- Spreadsheet models encourage the use of "spaghetti" logic, where cells point to cells that point to cells, and can grow into random networks of calculation logic;
- They permit lots of easy off-by-one errors;
- They generally are difficult to verify/audit;
- They do not provide good tools for managing data either in terms of consolidation or searching for specific detail;
- Perhaps most importantly, despite their convenience, spreadsheets are not a robust repository for information.
I have seen one multinational enterprise that (believe it or not) built a budgeting system atop sets of dozens of departmental spreadsheets that they would roll up into a master budget; while it's a neat extension of the technology, only a fool would try to use this to run a large enterprise. One bad link in one subsheet, and the whole house of cards could fall down. (And the "top" vendor these days, Microsoft, isn't noted for building products that are of industrial grade robustness.)The last few points point towards where I would like to see spreadsheets go. They have been, and are very good at producing ad-hoc, one-off reports. This is a proper use of spreadsheets.
They are often being used instead as repositories for information that really ought to be managed by a database management system of some sort.
What spreadsheets should do is to allow, nay encourage, the use of data extracts from external sources, notably relational databases. The use of named ranges (which are a venerable feature from at least as early as Lotus 123 v2.01) is of assistance; Lotus Improv was a rather complex-to-use test platform for improved "modelling" whose functionality included database extraction.
Using external repositories permits the benefits of:
- A single repository that can be kept correct, rather than a multitude of mutually incompatible data stores;
- Data synchronization (a restatement of the last);
- All the good RDBMS "stuff" like:
- Data modelling and
- Stored Procedures/Triggers
In effect, the real point I would propose is that the task of building a spreadsheet should involve some data modelling, with thought not just about the report at hand, but also about where the data comes from and perhaps should go to.- Field validation,
- Maintaining field relationships,
- Transaction logging,
- Centralized backups,
and perhaps even more sophisticated things such asRe:Problem with spreadsheets (Score:3, Interesting)
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
Spreadsheets are the worst sort of hack programmin (Score:5, Insightful)
Some of these flaws are
- Cryptic names for fields
- No comments
- No obvious flow of control
- No modularisation
- No capability to test spreadsheet sub-components in isolation
- No capability to do a diff to see what's changed between versions
Spreadsheets also add flaws of their own, such as unlocalised references.
If we had to design the worst possible "programming language" we'd be wise to look at spreadsheets for an example of what to include.
Re:Spreadsheets are the worst sort of hack program (Score:4, Informative)
- Cryptic names for fields
How is this "stamped out in programming languages" other than convention and training? Is is forced on you?
- No comments
Excel can "comment" to some extent.
- No obvious flow of control
Some would argue that the freeform nature of a spreadsheet is what makes it so appealing.
- No modularisation
- No capability to test spreadsheet sub-components in isolation
- No capability to do a diff to see what's changed between versions
Excel can keep track of every change made - didn't we just have an article making fun of Microsoft for this feature in Word?!
Not saying the spreadsheets, or Excel specifically, is the answer to everything.
Open standards and how to enforce them (Score:5, Insightful)
I am all for open standards in communication, but what shall I do? Send a reply to the salesman "you f*ing Microserf moron, I don't want your car if you force me to buy a bloody spreadsheet just to read how much do you charge for a goddamned air filter?" But is it wise to choose a car just because of the software that a salesman uses?
Finally I picked the one that was described in PDF. It was a coincidence - a decisive factor was actually that the make of that car constantly tops in the consumer surveys, while the other two are just about average. But then I started to think - maybe that's not a coincidence after all? Maybe this make tops in surveys just because it's policy is to make all stages of customer experience as convenient as possible and they ask themselves the question that other car salesmen don't ask - "what if my prospective client does not use Microsoft Excel(TM) or Microsoft Word(TM)?".
Maybe it is possible for us to vote with our wallets against proprietary, closed standards?
Discrete Event Modelling (Score:5, Interesting)
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
Excel is fine for statistical predictions (Score:5, Insightful)
The first distortion is the use of point values and simple arithmetic instead of probability distributions and statistical measures. So far as I know, there's no off-the-shelf spreadsheet product--certainly none in common use--that provides for input of numbers as uncertain quantities, even though almost all of our decisions rest on forecasts or on speculations.
I am a student of this university : http://www.sgh.waw.pl/
Currently I am having a course in the use of Excel for prediction purposes. We do a lot of different case studies. We use Monte Carlo simulations, statistical tests, Markov chains and so on. We always discuss risk (variance, value-at-risk and so on). Excel is our basic tool and it is fine. We use different tools for specific purposes: Best-Fit for distribution fitting.
It is not a flaw of the tool, it is a flaw of the user. As someone said, give a monkey a PC instead of a type writer and you will get digital bullshit. I can only demand that people without proper education are not allowed to deliver multi-million business forcasts.
Excel is no good for Monte Carlo simulations. (Score:3, Informative)
I suggest you seek another university. A spread sheet is not even an adequate tool for teaching Monte Carlo (MC).
MC simulations typically
"Powerpoint Mastery" (Score:5, Informative)
Erm
Yes, I know it was discussed here before (as I guess), but still - it is worth a mention.
CC.
Article is an advert (Score:5, Insightful)
1 -- the article is a content-free advert for Whitebirch's financial toolkit
2 -- Excel is an incredibly powerful and important piece of software which many if not most large corps can't do without. There is no alternative to it. The fact that it's unpleasant to use is beside the point -- nobody has been able to come up with a better (or even comparable) replacement. In my experience, there is a large segment of the IT community that is pathologically unable to focus on business needs enough to understand this.
Re:Article is an advert (Score:3, Insightful)
I call bullshit. How old are you? How many PC software products in that space do you remember? Javelin was both excellent and revolutionary. Lotus Improv was close (but not close enough) to a GUI Javelin. Both used the spreadsheet paradigm as a sort ow "window" into real data. Both failed because the average PC-using simpleton wanted the "simplicity" of 1-2-3. 1-2-3 was overtaken by Excel because their GUI versions sucked worse
Re:Article is an advert (Score:3, Insightful)
There are plenty of alternatives
Most users just need something that performs a function on a row or column of data
Mm, this is what I meant by an inability to focus on business needs. It is not possible (with a reasonable amount of effort) to generate and splice together real time futures price streams based on bloomberg data, a C maths library, and parameters modified on the fly by the user, with OpenOffice. It is with Excel. This is the sort of task that needs doing. Other people in the company ma
A little skeptical (Score:5, Interesting)
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?
Who cares how people use Excel? (Score:5, Interesting)
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?
Re:Who cares how people use Excel? (Score:3, Insightful)
spreadsheets for ultra critical work (Score:5, Interesting)
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.
Re:spreadsheets for ultra critical work (Score:4, Insightful)
Err...I too work doing rates-related stuff for major banks. Blah blah blah??!! That's the entire point of the rates business - that's why those traders are employed, because they can tweak their prices to make a profit from the market.
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".
It did what? Really? A cost centre tried to impose inadequate tools (your own admission - not flexible enough) on to people who were actually generating cash for the bank? And they rejected it did they? Good Lord, how terribly surprising.
Sorry, but I'm utterly shocked at the cavalier attitude displayed here. I work doing a very similar job to the one described (writing tools to control rates pricing), and I tell you now that wandering in to our profit-producing users and saying that their rules are a load of 'blah blah blah' would, quite correctly, get me booted out of the City forever.
Cheer,
Ian
Excel Cluster! (Score:3, Interesting)
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.
Please! (Score:5, Insightful)
Just like, say, PERL or Java, spreadsheets can be used well, and they can be used poorly. Furthermore, people with good "technical" Excel skills can produce lousy spreadsheets with little analytical value, and vice versa. I have seen some fantastic spreadsheets which have totally revolutionized the way people saw a problem. At an insurance company I worked with, they used a huge spreadsheet to do a simulation of the effects on every single customer of a planned, dramatic price increase. The result: They realized that the price increase would have much less impact than they feared. Thus, the product was kept and the employees kept their jobs. The thing with the spreadsheet was that it was developed in fast trial-and-error loops, which meant that their run-once-per-night SAS tools were not an option (this was 7 years ago).
(I have, by the way, also seen people spend 3 months on developing a mega-spreadsheet for assessing the value of a company, only to use the wrong assumption for a critical value and thereby introducing an error of about 40% in the valuation [that critical value being the discount rate]).
I can assure all the concerned citizens of this forum that there is indeed a lot of excellent, first-rate Excel usage out there. Analytical power beyond our wildest dreams is at the fingertips of people without skills in programming at any lower level. This, believe it or not, is a good thing, because anyone who has dedicated himself to becoming great at programming is probably less skilled in disciplines such as financial analysis.
Sure, there is "bad code". Sure, people get a false sense of control. Sure, this new tool puts too much options in the hands of people who do not know how to use them. But how would that be untrue of other IT tools or programming environments? What does it matter that they use Excel as a database, as long as it gets their work done easier than getting an SQL education and then doing it "right"?
Biases are part of all decision-making (as even economists are realizing [economist.com]). So what if that is the case in Spreadsheet World, too?
Spreadsheets early appeal (Score:4, Interesting)
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!
Not surprised that techies don't get it (Score:4, Insightful)
* Entrepeneuers to financially model their business plan.
* Calculations to be performed more accurately than say, in the margin of a ledger pad.
* Simple busines processes to be tracked and managed using a computer instead of say, a legal pad.
* Executives to summarize and categorize and drill down to analyze information from a database (pivot tables)
At the end of the day, I've found that spreadsheets are not the cause of business mistakes. When there is a spreadsheet failure, there are ususally a couple of fundamental problems:
* Lack of attention to detail
* No oversight or validation
* Numbers are not reliable to begin with
* No one bothered to actually do a what-if using a reasonable range of scenarios - they only looked at the rose colored one.
OO calc & marketing (Score:3, Insightful)
Obligatory blame Microsoft (Score:4, Insightful)
How about the simple idea of breaking away from the rectangular grid? Or free form cells placed on a diagram or schematic or blueprint?
change control for spreadsheets? (Score:3, Interesting)
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
The Problem with Spreadsheets (Score:3, Insightful)
An acquaintance criticized spreadsheets and praised pencil and paper forms because mathematical errors can crop up in either one, but with paper there is a double-entry system, running totals, and review by brains and eyeballs.
My argument is that paper is a big step backwards:
line 2 (non-paying customers): 10
line 3 (all customers; add 1+2): 400
I use a decently large spreadsheet to run Technical Video Rental [technicalvideorental.com], and I've certainly found bugs in it, but I've noted that the bugs are denser, and harder to find in those areas where the computation appears with more intermediate values hidden.
I think that a more confident spreadsheet programmer tends to hide more variables in complex cell formula; as I am not a confident spreadsheet programmer, I've - in many places - spread formula across multiple cells...and this has helped me figure out bugs.
This points out running totals as one example of good practice. Nothing could be simpler in a spreadsheet, yet we almost never see it.
So: why do spreadsheet programmers not do these things?
One reason that occurs to me is that spreadsheets conflate calculation with presentation. Intermediate values use up screen real estate, and look ugly.
Yes, there are tools that *allow* one to separate calculation from presentation: one could have two separate tabs, for example.
Yet these tools allow for disambiguation of calculation and presentation in the same way that assembly programming allows for object oriented design.
Or, to rephrase it: "Hidden steps considered harmful".
I don't even like C/C++ code that puts too much computation on a single line: I want intermediate values that I can step through with a debugger.
Perhaps what's needed are much higher level tools with in the spreadsheet that let one select cells of interest on one tab, then create a presentation tab based on these? I've got visions of cool Mac-Aqua-like greying out of 90% of cells, while one drags and drops the still-crisp cells around... Another/alternate idea: it might be nice if instead of the heavyweight tabs that most spreadsheets support, one could open zoom in on a single presentation cell and investigate little "pocket tabs" which might have ~10 x ~10 cells in them. The equivalent in C/C++ would be a complex expression on one line that decomposed itself into multiple lines with intermediate values only when you walk it with a debugger.
Now, don't get me wrong: I'm not arguing for fancy presentation layers, or dancing pie-charts; I'm arguing for the ability to take a huge page of calculations and tie the some of the inputs, intermediate steps, and output to a much smaller summary page, or, conversely, I'm arguing for the ability to take spreadsheets as they are currently written, and expand them into a debuggable format.
This, I argue, would make spreadsheets more useful, and decrease the number of bugs that crop up in them.
Spreadsheet: Worst Invention of 20th Century (Score:4, Insightful)
The ss has some serious advantages. In an environment of increasing number density and decreasing personal involvement, the need to have a comprehesive tool for data analysis could only have given birth to the spreadsheet. We could talk all day about how handy the ss is for many of the tasks in this environment.
But the space between the substance is what concerns me. Ss have allowed us to max/min too many things without much regard for the things that are undefined and necessarily intangible, but are still entangled in the matter itself. No corporate ss takes into account the costs of pollution, unemployment and general social degradation due to uncontrolled greed.
Like handguns, ss have brought us significant personal power at the cost of a good many social problems. Hence, they seem to require more careful handling and regulation. One aspect to this is training, and in general ethics training is a good place to start. (The BBB in my area is attempting to emphasize this, but they are meeting stiff resistance from the business community.)
Ss should be used with care, and their results are suspect anyway. That's the least message I've tried to convey for years.
What's annoying..? (Score:5, Interesting)
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.
Re:Kill them all (Score:3, Insightful)
So really I don't blame them for avoiding that utter POS software.
You have to remember that people are stuck working within the confines of whatever software the business deems 'acceptable'. Although it would be great if we were all on Linux/OSX at work we're not.
Re:Kill them all (Score:5, Insightful)
Re:Kill them all (Score:3, Interesting)
If I want a decent table in a Word document I have in the past been driven to embed an Excel file in it.
Re:Kill them all (Score:3, Funny)
Always insert Powerpoint slide into the document. Unless you really need something Powerpoint can't do of course, in which case you should use whatever actual drawing program you need to.
Re:Kill them all (Score:3, Interesting)
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?
Re:Kill them all (Score:5, Insightful)
Similarly, I don't have any problems with using Excel as a basic flatfile database (never relational though, I'm not that insane) where the visual layout of the data is more important than the flexibilty of querying. That said, on a basic flat-file database you can actually perform some quite sophisticated filters using Excel's auto-filter function.
I don't think the problem is with using a spreadsheet as a word processor, database, or any of the other uses it can be shoehorned into. The problem is simply that people correctly see a spreadsheet as a jack of all trades, but forget that this implies it's a master of none, with the possible exception of what it was designed for: crunching tabulated numbers.
Re:Kill them all (Score:5, Interesting)
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)
Re:Kill them all (Score:3, Informative)
One bottleneck that often happen in VBA for Excel routines is that the application tries to keep the screen refreshed while the routine is crunching, which slows things right down. If it's not there already, you should get a big improvement in speed by putting
application.screenupdating = fal
Excel (Score:3, Insightful)
You could make the first 4 pages with formatting, then simply import the csv values into a third source page.
I appreciate that Excel gives me the capability to make simple semi automated forms that look nice.
Prevents simple errors, and they're easy to use.