Slashdot Mirror
Beginning Excel What-if Data Analysis Tools
Graeme Williams writes "Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver makes it easy to learn about some neat features of Excel, including the four data-analysis tools mentioned in the title. I found the book useful, but the style is dry and unadorned, and others may find it less approachable than I did. The examples around which the book is built are clear and straightforward rather than insightful, and presented plainly rather than with a lot of discussion." Read the rest of Graeme's review.
Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver
author
Paul Cornell
pages
xxii + 167
publisher
Apress
rating
7
reviewer
Graeme Williams
ISBN
1-59059-591-2
summary
A clear but bare introduction to a useful set of Excel tools
This book reads and feels more like a textbook than an introduction. Other beginner books are full of diagrams, icons and text in boxes. This book has almost none of that – the occasional tip or note is set off with horizontal lines. In other books, text in boxes often seems to be put there for no reason at all, but this book has exactly one diagram. Comparing this book to others, I feel as though we've lost the middle way.
The book seems to go out of its way to avoid diagrams. To fill out a dialog box, for example, the instructions are to click on the first field, type in the value, click on the second field, type in the value, and so on. I just don't understand why you wouldn't put in a screen shot, with the instructions, "Make it look like this". I don't know if screen shots weren't used because they're more expensive, or harder to translate, but if so, a table could have achieved a similar result.
Goal Seek is a simple one-variable equation solver. You put x in one cell and f(x) in another. You point Goal Seek at the two cells, give it a value of c and it attempts to solve f(x) = c. It's a simple enough feature, and the book goes through a number of straightforward examples.
The examples are relevant and clearly explained, but they seem only to be examples of themselves. They don't trigger any new ideas, and none of them jump out at you as "Neat!". I wish the author had put a little more creativity into the examples. They seem a little dry and occasionally repetitive, and don't seem to build on one another. An example shouldn't be just, "Here it is", but rather, "Here's something important to know about how it works" or "Here's an idea you can use in other places as well as here".
At the end of each chapter, there's a list of possible errors, but the suggested fixes aren't all equally helpful. If Goal Seek can't solve f(x) = c, the book suggests (page 19) changing the value of c! This is an area where a set of related examples would have been very helpful: first showing a simple example, followed by a more complicated example that fails, and finally with the failure repaired.
Data Tables are a way to automatically generate a one- or two-dimensional tables of values, given a formula and one or two sets of values. The book shows how to build data tables, going through a number of good examples, but I was somewhat mystified why this would be better than doing the same thing by hand. Building a data table by hand means you have to understand the difference between A1, $A1, A$1 and $A$1, which I guess is one reason for using the automatic mechanism. A1 and $A$1 are referred to as relative and absolute references, in case you want to google this particular mystery. But building a table by hand gives you more control over the layout. Unfortunately Microsoft has made the layout of two-dimensional data tables both odd and inflexible (the formula for the table is stuck in the upper left corner). It would have been clearer if the book had explained that the examples looked the way they did because that was the only way they could look. It would also have been useful if the book had at least briefly compared data tables to the manual equivalent.
Scenarios allow you to store versions of a spreadsheet that have different input values. This is neater than it sounds, since you can vary any number of input variables and calculate any number of output variables, including charts. You can also generate a summary sheet which tabulates the corresponding inputs and outputs. The book explains all this very well, going from a clear explanation to three good examples.
Any book with code samples risks confusion about whether the reader should type in the examples or download them, but this book crosses the line. In some examples (the most egregious example is on page 51), the discussion assumes that some cells have defined names, something that would only have been possible if the reader downloaded the example, since names were not included in the step-by-step instructions. The odd thing is that in some of the examples, the instructions DO include the defined name for each cell.
When presenting Excel examples like these, you have to deal with the possibility that a cell will have three pertinent properties: a formula, a value, and a name. This is another case where the book seems to lack a good designer who could show this graphically.
The Solver is a general-purpose equation solver that will handle multiple variables and multiple constraints. For a given function f(x1, ..., xn), the solver can either solve for f(...) = c, or maximize f(...). The book explains how to set this up, and the meaning of the dozen or so options (tolerance, maximum iterations, and so on) pretty clearly.
The Solver provides a sensitivity report (how much the result will change if one of the inputs changes fractionally), but this report is disabled if even one of the variables is restricted to whole numbers. There are two obvious ways around this: run the sensitivity analysis as though the constraint wasn't there (which would provide the counter-factual information about how much the solution would change if the whole number value changed fractionally); or run the sensitivity analysis without the restricted variables. Microsoft doesn't provide either of these workarounds, and the book doesn't discuss them either.
The sensitivity report is disabled if any variable has either an "integer" or "binary" constraint, but the book repeatedly mentions only integer constraints, which could be confusing to a beginner. It doesn't help that Microsoft gives the same error message ("Sensitivity Report and Limits Report are not meaningful for problems with integer constraints") for both cases.
The appendices are quite good – I'd almost recommend reading the book backwards. There's an overview of the data and financial analysis functions in Excel, such as average, median, floor, ceiling and mortgage payment, with enough detail to lead you to the right part of Microsoft's documentation. Another appendix describes ways of handling data that aren't discussed in the body of the book, such as Lists, Subtotals, sorting, filtering and consolidating data. These extras add a considerable amount to the usefulness of the book.
At $34.95 list, the book is expensive for an introductory book, but I'm not sure that should count against it. If you use the techniques described in the book, the time you'll save will quickly pay back the cost. On the other hand, if you need more explanation and discussion than the book provides, it's going to seem like a whole lot of money. I strongly recommend downloading the sample chapter. It will give you an excellent view of the book's strengths and weaknesses."
You can purchase Beginning Excel What-If Data Analysis Tools from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
This book reads and feels more like a textbook than an introduction. Other beginner books are full of diagrams, icons and text in boxes. This book has almost none of that – the occasional tip or note is set off with horizontal lines. In other books, text in boxes often seems to be put there for no reason at all, but this book has exactly one diagram. Comparing this book to others, I feel as though we've lost the middle way.
The book seems to go out of its way to avoid diagrams. To fill out a dialog box, for example, the instructions are to click on the first field, type in the value, click on the second field, type in the value, and so on. I just don't understand why you wouldn't put in a screen shot, with the instructions, "Make it look like this". I don't know if screen shots weren't used because they're more expensive, or harder to translate, but if so, a table could have achieved a similar result.
Goal Seek is a simple one-variable equation solver. You put x in one cell and f(x) in another. You point Goal Seek at the two cells, give it a value of c and it attempts to solve f(x) = c. It's a simple enough feature, and the book goes through a number of straightforward examples.
The examples are relevant and clearly explained, but they seem only to be examples of themselves. They don't trigger any new ideas, and none of them jump out at you as "Neat!". I wish the author had put a little more creativity into the examples. They seem a little dry and occasionally repetitive, and don't seem to build on one another. An example shouldn't be just, "Here it is", but rather, "Here's something important to know about how it works" or "Here's an idea you can use in other places as well as here".
At the end of each chapter, there's a list of possible errors, but the suggested fixes aren't all equally helpful. If Goal Seek can't solve f(x) = c, the book suggests (page 19) changing the value of c! This is an area where a set of related examples would have been very helpful: first showing a simple example, followed by a more complicated example that fails, and finally with the failure repaired.
Data Tables are a way to automatically generate a one- or two-dimensional tables of values, given a formula and one or two sets of values. The book shows how to build data tables, going through a number of good examples, but I was somewhat mystified why this would be better than doing the same thing by hand. Building a data table by hand means you have to understand the difference between A1, $A1, A$1 and $A$1, which I guess is one reason for using the automatic mechanism. A1 and $A$1 are referred to as relative and absolute references, in case you want to google this particular mystery. But building a table by hand gives you more control over the layout. Unfortunately Microsoft has made the layout of two-dimensional data tables both odd and inflexible (the formula for the table is stuck in the upper left corner). It would have been clearer if the book had explained that the examples looked the way they did because that was the only way they could look. It would also have been useful if the book had at least briefly compared data tables to the manual equivalent.
Scenarios allow you to store versions of a spreadsheet that have different input values. This is neater than it sounds, since you can vary any number of input variables and calculate any number of output variables, including charts. You can also generate a summary sheet which tabulates the corresponding inputs and outputs. The book explains all this very well, going from a clear explanation to three good examples.
Any book with code samples risks confusion about whether the reader should type in the examples or download them, but this book crosses the line. In some examples (the most egregious example is on page 51), the discussion assumes that some cells have defined names, something that would only have been possible if the reader downloaded the example, since names were not included in the step-by-step instructions. The odd thing is that in some of the examples, the instructions DO include the defined name for each cell.
When presenting Excel examples like these, you have to deal with the possibility that a cell will have three pertinent properties: a formula, a value, and a name. This is another case where the book seems to lack a good designer who could show this graphically.
The Solver is a general-purpose equation solver that will handle multiple variables and multiple constraints. For a given function f(x1, ..., xn), the solver can either solve for f(...) = c, or maximize f(...). The book explains how to set this up, and the meaning of the dozen or so options (tolerance, maximum iterations, and so on) pretty clearly.
The Solver provides a sensitivity report (how much the result will change if one of the inputs changes fractionally), but this report is disabled if even one of the variables is restricted to whole numbers. There are two obvious ways around this: run the sensitivity analysis as though the constraint wasn't there (which would provide the counter-factual information about how much the solution would change if the whole number value changed fractionally); or run the sensitivity analysis without the restricted variables. Microsoft doesn't provide either of these workarounds, and the book doesn't discuss them either.
The sensitivity report is disabled if any variable has either an "integer" or "binary" constraint, but the book repeatedly mentions only integer constraints, which could be confusing to a beginner. It doesn't help that Microsoft gives the same error message ("Sensitivity Report and Limits Report are not meaningful for problems with integer constraints") for both cases.
The appendices are quite good – I'd almost recommend reading the book backwards. There's an overview of the data and financial analysis functions in Excel, such as average, median, floor, ceiling and mortgage payment, with enough detail to lead you to the right part of Microsoft's documentation. Another appendix describes ways of handling data that aren't discussed in the body of the book, such as Lists, Subtotals, sorting, filtering and consolidating data. These extras add a considerable amount to the usefulness of the book.
At $34.95 list, the book is expensive for an introductory book, but I'm not sure that should count against it. If you use the techniques described in the book, the time you'll save will quickly pay back the cost. On the other hand, if you need more explanation and discussion than the book provides, it's going to seem like a whole lot of money. I strongly recommend downloading the sample chapter. It will give you an excellent view of the book's strengths and weaknesses."
You can purchase Beginning Excel What-If Data Analysis Tools from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
To fit that title on the cover.
Open Source Drum Kit, LPLC deve board - mjhdesigns.com
I know architects for a metal building company that use spreadsheets on a regular basis. Not Excel, though...Last I checked, they didn't want to move from Lotus.
Chemical Engineers are pretty much nerds. We use Excel for data analysis all the time.
As much as it is in fashion to bash Microsoft, I must say they did a very good job with Excel. No matter how well you think you know the program, you most likely have more to learn. So many times I've had people ask me how to do something in Excel/VBA and I tell them, "Don't use VBA - that feature is already built into Excel". So, before you DIY try reading up on some of the features of Excel.
As I side note, I use to teach Excel to an adult student who just didn't "get" some of the concepts. Every session he would ask me, "what's this I-F function for again?" He didn't even get that it was the IF function and not the I-F function as if I and F were letters of an acronym. Let me tell you, that was frustrating every class.
Bradley Holt
The appendices are quite good. I'd almost recommend reading the book backwards.
.ehcadaeh a em evag tsuj ti tub ,ecno taht deirt I wonk uoY
He who knows best knows how little he knows. - Thomas Jefferson
I call excel the ultimate mba tool. if data isn't in excel then it just doesn't exist. not that it's a bad tool, it's just not always approprate. At my current job we had some cowboy excel "programmers" that practically made relational databases out of excel books. i've been there almost two years now and i still haven't gotton all of the nightmare excel "applications" convereted to access. even when it's in access, i need to provide a button on every form in order to dump data back out to excel.
I imagine a good bit of the book is taken up by repetition of the title:
In Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver we're going to show you how to use some of Excel's What-if Data Analysis Tools. Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver is written with the beginner in mind, but if you are coming to Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver as an intermediate user, we have something for you. Even if you are coming to Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver as an advanced user, we believe that you will come away with something useful. So let's get started with Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver.
P.S.
I had a lot of fun writing Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver, and I hope you have as much fun reading it.
isn't all of MS Office due to switch to (compressed) XML-based files in the next release?
so, I guess you'll be reading this book sometime in the next couple of years?
I work for a recycling company and all of our theoretical input and output is calculated on excel as well as I'm sure a number of things on the financial end (that I have absolutely nothing to do with)
If you do any work at all in the financial industry, you'll find that Excel can't be that easily dismissed. It is simply *the* essential application for large segments of the workforce.
It must also be admitted that in the hands of an experienced user (and at the banks that I do work for, there are some serious Excel power users) Excel is an impressive application. The open source spreadsheets that I've seen (e.g., OpenOffice Calc and Gnumeric), while fine for casual use, don't even come close to matching Excel in this arena.
People complain about things they don't like, especially ones that they can't really avoid running into, such as MS Office Suite apps... and posts on Slashdot complaining about things someone doesn't like.
Calc sucks. You can't do a tenth of the stuff you can do in Excel in Calc. And I'm not even talking about VBA scripting. Sure, you can make a table with your friends names, their screen names, their favorite colors and their girl friends, but try doing some hardcore data analysis and you will be left dead in the water.
its a good post-analysis tool for looking at data sets and drawing some conclusions. Like monte carlo analysis and stuff.
Or a stand-alone simulation, when a fullup C++ program is overkill but you can't quite do it on your calculator... (or sliderule for those of you a few years older than me)
This is entirely true. The pivottable equivalent (datapilot) is very flaky, often erasing sheet areas, and the graph options even are awful.
As much as I have tried to use Calc, I need some of the power of Excel.
I'd be interested to know how much of what is covered in this book is also supported by Calc. While I realize that this book is about Excel I am also interested to know how portable the knowledge I would gleen from it is.
I used to have a better sig but it broke.
Having used Excel for over a dozen years, I'm still saddened by how few folks use it for more than a poor man's database. Even basic mathematical tasks - making a budget, figuring out the total cost of a purchase - escape most people. The features covered in the book are truly powerful, but probably too complex for over 90% of Excel's userbase.
I was a software trainer for five years and I ran into many adult students whose lack of math skills kept them from using many of Excel's features. Now, for students without college degrees, I didn't assume too many math skills. However, even folks with four-year degrees would shock me. One time as I was showing students how to use the Auto-Sum tool, one student asked me if there was an "auto-percent" tool.
I was puzzled, "Do you mean formatting percentages? We'll cover that later in the class".
"No, my boss asked me to add up some numbers and then show the percent each one is of the total. Is there a tool for that?"
"Um, you mean the division operator?" I then proceeded to show her how she could divide the individual numbers against the total to get their share of the total. It wasn't a bad question, since it let me show the rest of the class how to combine formulas (which they had learned earlier) and functions. The scary thing is that the student had just graduated that past spring with a degree in finance.
"but try doing some hardcore data analysis and you will be left dead in the water."
People who do "hardcore data analysis" will not be using a spreadsheet anyway.
What if Excel didn't implement its own window manager and actually allowed one to view two windows side by side in the fashion one has already learned? What if Excel allowed one to save to a folder with a "[" in the name, which Windows happily allows one to create? What if Excel didn't have math errors (or so the Gnumeric people claim). What if Excel had a dynamic transpose function? What if Excel had used MEAN() instead of AVERAGE()?
It's really great, within its range. ;)
If you use ADODB to query a spreadsheet (as in through a linked table within Access, for example), you start to see "interesting" behavior for cells with >255 characters. Got to use the API and touch each cell explicitely.
Is that bad? No: if your PHB uses Excel to paper over his non-command of Word tables, you've probably got bigger headaches.
Excel has reasonable max column/row limitations. If you're encountering them on any regular basis, you application may require a proper database.
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
Once you learn how to use pivot tables, your entire perspective on Excel changes from "Word with Gridlines" to poor man's database.
"I'd rather be a lightning rod than a seismometer." -Ken Kesey
if he did, he would understand why the "workarounds" he proposes to perform a sensitivity analysis of an integer programming problem are meaningless.
take a look at The Science of Decision Making: A Problem-based Approach Using Excel by Eric Denardo if you are serious about doing data analysis with Excel.
People who do "hardcore data analysis" will not be using a spreadsheet anyway.
At its price point Excel makes a good post-processing data analysis tool. Its no matlab but its several thousand dollars cheaper.
Now, I know this is a joke... Still, have you ever used Excel in, say, French? The formula will not be =AVERAGE(A1:A10). No, it will be =MOYENNE(A1:A10). It makes it hell to find what functions you want. I can cope with multilingual menus, but multilingual functions are impossible.
Note that the functions are compatible: AVERAGE will show MOYENNE when opening it in a French Excel. Luckily... ;-) Oh, and OpenOffice replicates this behaviour. Very annoying, but I suppose that it's good for the end-users.
Ahhh...the great dumpster continuum. Many a free computer will be found there. -- sowth (748135)
In a world where you can buy desktop PCs with 2+GB memory, is there any reason to support only 64k rows?
"You're right," Fisheye says. "I should have set it on 'whip' or 'chop.'"
Currently, Excel handles 256 columns x 65536 rows... anything larger, and you need to be working in Access. However, if your data fits those limitations, Excel is (IMHO) the best analyst tool under our sun.
Excel 12 (aka Office 2003, currently in development) will have 16k columns x 1M rows. I found information here on the new limits.
This is slashdot, there can be nothing useful or beneficial about any of Microsoft's products.
See Tax Technology.
I work in semiconductor design, and a boss of mine 15 years ago used to simulate state machines in Excel. Each row was a clock cycle, each column was a state variable, and each cell was the contained the logic. There are of course many state machine design tools, but for quick discussions he could prove a lot of points in meetings just with Excel.
(Replied to your comment because, I found your comment the funniest one.... Geek Supremacist... Hilarious!)
Ahhh...the great dumpster continuum. Many a free computer will be found there. -- sowth (748135)
Yeah, pivot tables are great. But what's also handy and AFAIK pretty new is the easy ability to make quick lists from your spreadsheets. Adding a list creates filter options at the top and gives you a totals row at the bottom. It's like pivot tables lite and it's great for sorting through data quickly.
I do time tracking in Excel and it's simple to select one customer or one project with the lists and see a total of hours for the week.
Excel is great for simple data analysis/tracking work--including simple, single user database applications. Access is great for slightly more complex, single user database applications only. Neither should be used for anything that requires multiple users, which is what leads people to bitch and moan about "M$ Applications Suxoring".
And by the way, I'd rather unravel an Excel nightmare than a clapped together Access database any day.
Thinking of starting a business in Minnesota? Me too! mnsmall.biz
Odd... I have Office 2003 on this machine and it's not a beta version. I rarely use it, I only have it because my job requires it. For all my personal stuff, I use OpenOffice.org
Ahhh...the great dumpster continuum. Many a free computer will be found there. -- sowth (748135)
At my last two companies I've been the first in my group to do any sort of pivot based reporting. Between the ability to drill down and swap criteria on the fly, I get all sorts of awed looks and positive comments.
This usually leads me to run a couple of informal classes for the department, which, in the end frees up my time since everyone is busy doing their own analysis...meaning I can spend my time on more important stuff, like Slashdot.
Thinking of starting a business in Minnesota? Me too! mnsmall.biz
Have a look at ROOT. It is an object oriented data analysis framework with a C++ interpreter. It provides you with very powerful tools for doing all kinds of plotting (histograms and stuff), doing fits to data and storing data (so called ROOT files), etc... In addition to that it is free software (the latest version is licensed under the LGPL). It may not seem as easy to use as Matlab, but in the end I think ROOT is a lot more powerful.
On the other hand most secretaries and people like that would not find writing C++ scripts all that fun...
Try this in a calculator: 2+2*2. Every dipshit knows that 2+2 = 4, and then 4 * 2 is 8.
Now type it in Excel and it gives you 6!
Both in my corporate and graduate academic career, Excel is the most frequently used tool for data analysis. Not necessarily the most powerful, but likely the easiest and most flexible tool for most analysis applications.
Thinking of starting a business in Minnesota? Me too! mnsmall.biz
What I'd really like to see is books and courses on how to use OpenOffice, GIMP, LaTeX, Blender and other FOSS programs.
Let people know they don't need to depend on proprietary software.
"Nine times out of ten, starting a fire is not the best way to solve the problem." - my wife
Agreed - Pivot Tables are great - but don't forget the function: Getpivotdata. I've been entire applicatons that store data in pivot tables and then use GetPivotData to populate a template. GetPivotData, done right, can even take input from drop down boxes and such without any VBA. It's a handy way to create a nice user interface in Excel without a lot of work. Use the contact form on my website and I'll create and send a sample workbook.
blenderking.com over 50,000 blenders can't be wrong
I am an accountant, you insensitive clod!
Seriously, though, IAAA. There are plenty of people like me who belong to both sets. I'd even say that the proportion of nerds is just as high, if not higher, in analytical accountancy than it is in, say, web development. Weren't actuaries the prototypical nerd of the last century, and didn't they drive a lot of the computing advances of the time? Don't forget your roots, man.
"Trolls they were, but filled with the evil will of their master: a fell race..." -- J.R.R. Tolkien on Olog-hai
As an engineer, I hate it when people use Excel for data analysis. It's a financial spreadsheet tool, and it's awful for anything else. Skip it, learn Matlab, and you'll never look back. Otherwise, you'll only cause others headaches when you hand them your "program" in Excel.
The fact that Excel has a 65,535 row limit is an indicator that even Microsoft doesn't expect it to be used for real analysis.
+1 pointing out hyperbole
"I'd rather be a lightning rod than a seismometer." -Ken Kesey
My dad is the perfect accounting-geek and I owe him a lot. Not only my computer degree, but I also owe him financial responsibility, the ability to get my paperwork done efficiently and the mania to keep lists about everything and nothing.
I just find it sad that most people didn't understand that I was joking in my original post. Seems I hit a nerve and was thus labelled "Troll".
Ahhh...the great dumpster continuum. Many a free computer will be found there. -- sowth (748135)
2+2*2 is indeed 6...believe it or not. And while yes, every "dipshit" does "know" that 2+2*2=8, I would venture to guess that a small percentage of the population (those with at least a 5th grade understanding of arithmetics) would recognise that Excel is simply implementing standard order of operations. It's complicated, but it breaks down like this:
:-D
2+2*2 = 2+4 (perform multiplication first)
2+4 = 6 (perform addition last)
I personally don't use MS Office or Windows, I try not to give them my business willingly...however, in the spirit of fairness, I just checked my OpenOffice Calc and yes, it agrees "=2+2*2" is equal to 6. Oh geesh, I guess someone should file a bug report.
(I hope this isn't taken as a flame, I'm just poking a little fun.)
I sure hope they fix the slowdown problems when dealing with 150+ columns. I spend a lot of time sitting and waiting when I want to add fields to my largest spreadsheet, even longer when I need to do a recalc.
/Access is verboten in my office, since the PHBs can't use it.
"Trolls they were, but filled with the evil will of their master: a fell race..." -- J.R.R. Tolkien on Olog-hai
If you find Oracle, or Access, or PostgreSQL easier to use for manipulating small sets of primarily numerical data, feel free to use them. Honestly, though, I think there might be a reason why Excel is classified as a spreadsheet instead of a database.
The nerves are pretty sensitive right now, there is a huge movement to make sure discussions are on-topic etc -- which I agree, need consideration so that /. doesn't just become a tech-centered fark.
/. without valid, on-topic reasons is going to get modded down -- regardless of intent.
But any post that looks like it's impugning
As one sig I recall said, "One man's (+5, Funny) is another man's (-1, Troll)."
"Trolls they were, but filled with the evil will of their master: a fell race..." -- J.R.R. Tolkien on Olog-hai
One man's (+5, Funny) is another man's (-1, Troll)
So true... I have seen this indeed in a sig a long time ago. Dunno if the guy is still around. Well, at least you won't take it personal now, and you know that I hold accountants in high esteem.
Ahhh...the great dumpster continuum. Many a free computer will be found there. -- sowth (748135)
Just because you can solve your problem in a spreadsheet doesn't mean you should. At best they are a convenient way of doing simple non-recursive calculations on a dataset. At worst they are a really non-portable way of making your algorithm incomprehensible to anyone else.
Forget Excel, and definitely forget C++; check out Octave, SciPy, or Perl if you want tools for data analysis. Matlab if you have some funds.
Excel is indeed an awesome product. I never really appreciated it (or knew much about it, frankly) until I started studying finance. Now its pretty indispensable. Speaking of goalseek, I was looking at a GNU-licensed product called Maxima, which performs a similar function. Anyone have any experience with that?
"If we cannot be free, then at least we can be cheap" -- Frank Zappa
To the -1 Troll mod: It's a joke, retard
"What if Excel didn't implement its own window manager and actually allowed one to view two windows side by side in the fashion one has already learned?"
What if Users could find the "Window|Compare Side by Side" command?
"What if Excel allowed one to save to a folder with a "[" in the name, which Windows happily allows one to create?"
You know that square brackets have a special use in Excel, right?
"What if Excel had a dynamic transpose function?"
There is Edit|Paste Special|Transpose. I can only guess you were looking for more.
"What if Excel had used MEAN() instead of AVERAGE()?"
Then there'd be no need for overly picky users to write their own MEAN() function in order to save three keystrokes; what fun would that be? (You know you can write your own functions, right?)
a really funny joke, too.
my password really is 'stinkypants'
I've been surrounded by nerdy accountants for the last 13 years. I'm thinking of buying the book for them so I can get out of my role as the local Spreadsheet B!tch for the Excel challenged.
As a database developer, I have come across organizations countless times that are using excel as a database. They keep some list, with lots of visual formatting, which they send around in emails, which they then end up with dozens of different versions of. Someone gets the bright idea to put the file on a file server so lots of people can open it at once, but that doesn't seem to work right! THEN when it truly gets out of hand, I get a call. Can you help us? Can I just shoot myself, it will be quicker and less painful. I have seen people keeping inventory, invoices, correspondence logs, etc. in excel. Why not put it in a database? It obviously needs to be shared. Data should be kept as close as possible in ONE place, and when edited it should propagate immediately to all users. This is why databases are useful. After having been confronted with these kinds of messes over and over, I have developed a (perhaps unfounded) hatred of excel. It really does have its place, and in its place it is a wonderful tool. Very few people seem to understand what that place is. The power that it really possesses rarely seems to get used either.
I agree. Having Excel (and Word) on your box is nice. World class, gold standard software. It gives me the warm and fuzzies.
Mod down people who tell people how to mod in their sigs
If you're trying to work with datasets larger than 65k lines you may want to check out Kirix Strata.
-Handles 60 Billion records
-Spreadsheet-like viewing of data from relational databases (drag in fields from related tables)
-Really, really fast
-Runs on Linux
Currently there's a 30-day evaluation version
[rant]
I work in the finacial industry, and I know Excel/VBA/COM all too well. I also choose to do everything but the basic data acquisition and inspection using proper tools (Python and R, in my case). In fact, I take Excel's ubiquity as yet another piece of evidence that the majority of those toiling in the finance vineyard are numerically illiterate. The fundamental problem is that a spreadsheet conflates data and analytics, the cardinal sin in anything above a throw-away script; surely you know that, if you ever had to maintain a large non-trivial sheet. Another obvious flaw is that the spreadsheet is a Flatland, with nothing but 2D arrays (sure, writing VBA/VB or tacking on something more serious using COM resolves this problem, but why drag the ball-and-chain of Excel's baroque object model around to begin with?). Moving on to the actual implementation, Excel's Frankenstein nature, with all sorts of grafts, add-ons and arbitrary limitations always terrified me. Out of the essential applications we run here, this is the one that has someone pounding the table in frustration more often than everything else put together. In summary, Excel is horrendously overused, probably because it presents a seductive shallow-learning-curve alternative and traps people in a sub-optimal situation, where they spend time cobling workarounds together once the going gets tough, instead of doing real work.
[/rant]
"I am just a customs officer; but I, too, wish to understand what is going on" -- Bertold Brecht
Office 2003 is the one that was released in 2003. Office "12", that the parent mentions with the larger limits, is the unreleased version after 2003.
You really don't want more than 255 characters in a cell. Lots of the functions just don't work. You also start running into very weird bugs (particularly when using VBA) when the workbook gets above a certain size. Things just stop working.
Me too, but one must realize that basic data acquisition and inspection can be 90% of the project (for me, it's usually lots of pivot table playing to figure out what the data looks like and what I want it to look like, then a quick R script to get it that way). As you say, the trick is knowing when to switch.
Graduate academic career in what area?
Excel is decent all-purpose spreadsheet, but it excels in nothing. Until a few years ago, Excel had serious well-proven flaws in certain algorithms contained in the Analysis toolpack as well as they way it approximates number. Let alone the help section, which contained many errors. I was told it hasn't changed much. If you are serious on data analysis, inferential analysis, decision making, scenarios generation etc. you should investigate what other options are available.
Most likely you will find a package that specializes in your area. To me, if you say you're truly serious about, say, data analysis and still use Excel, clearly I would be highly skeptical about your results.
The reasons so many people use Excel is because they feel confortable with it and/or it is the only software package they know.
P.S. Someone suggested 2+2*3=12 when done with a calculator and ==8 when done in Excel. This is a fairly basic issue: in a calculator you can only do one operation at a time, so first you type 2+2 then you multiply the result by 3. In Excel (or Matlab or Maple, or SAS or R or SPLUS or Stata or...) you have to use appropriate mathematical notation whereby multiplication and division takes precedent over addition and substraction. So there is a difference between (2+2)*3 and 2+2*3
This review is pretty negative towards the book. Every paragraph mentions something the reviewer didn't like, even the price seemed steep.
But it gets a 7? On what scale is this? 7/100?
-David
Well, I wouldn't accuse MS of not trying to drive hardware sales, but, maybe there isn't a business case to change the application? I'm sure the market is well-modeled in a spreadsheet somewhere in Redmont. ;)
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
Agreed. Now, how many opensource spreadsheets apps out there support this? I know that openoffice supports it, but sucks badly when there is a huge ammount (eg: from a database query) of data to handle while excel makes it instantaneous. Last time I checked, KSpread didn't have it, neither GNumeric.
sign(c14n(envelop(this)), x509)
Currently, Excel handles 256 columns x 65536 rows... anything larger, and you need to be working in Access. However, if your data fits those limitations, Excel is (IMHO) the best analyst tool under our sun.
Sorry, Quatro Pro can have millions of rows and has had this feature for the past several versions. It also has better charting and data analysis/stats. It has had problems in the past importing excel spreadsheets but the latest version seems to import excel with no problems.
There are times when it has really come in handy. For example, I wanted to copy some tables from a web page. Excel and Word refused to format the data as a table and instead tried to put it all in one cell. By comparison, it copied just fine into Quatro Pro.
Today's vices may be tomorrow's virtues.
use the right tool for the job.
I recently was tasked with building a tracking document for work. I need to be able to track progress on thousands of files, and be able to view large portions of them. There are many cross-linked files, and many different categories.
I know full well it is a database problem. In fact, I got Office 2K3 Pro installed so I could evaluate Access for just this task.
After playing around with it I decided against it and used Excel (a sheet checked into source control). My reasons are as follows:
1: Not everyone in the office knows how to use Access, or even has it installed.
2: Everyone and their dog has Excel and is familiar with it.
3: My data set at its extremes will definitely fit in Excel 2k3's restrictions.
4: The List function and Pivot-tables allow me to answer every question I need to.
In the end it boiled down to time. Access might have been more robust, but this document needs to be created two weeks ago and run for 6 months before we retire it.
In my case using it checked into source control eliminates the problem of creating a shared document. I don't need (or want) multiple people editing it. In this case, Excel is the right tool, and I am happy to use it improperly.
Excel 12 (aka Office 2003, currently in development) will have 16k columns x 1M rows.
:)
Well, let's take a look-see.
* Fires up Excel 12 Beta 1 *
Hey, neat! Looky there, it's true. XFD1048576... what a cell reference
Excel does act a little weird when you get down to the last 20 or so rows/columns. It starts having a hard time drawing the window resulting in a lot of black boxes. Odd, but not really unexpected in the first beta.
"What do you despise? By this are you truly known." --Princess Irulan, Manual of Muad'Dib
/)
There was a time when we thought no-one would ever use Excel again, let alone write a book about it. Internet Explorer ran on SunOS, Star Office just came out, students were writing lots of free spreadsheet programs with perfect build systems and half finished usability.
Some C net writers said with the number of half finished free programs coming out, there would surely be a replacement for Excel one day.
Here we are 8 years later, Excel is king, and the free stuff has evolved into spreadsheets with perfect, finished build systems but still half finished usability.
You're exaggerating.
OOo Calc has more than 360 functions, full scripting in multiple languages, DataPilot, charting, graphics and a user interface similar to M$Excel.
While M$Excel, depending on the individual application, may be the better choice, your comment "don't even come close" is mistaken.
---
Are you thinking long term? Just because a TCO may be good in the short term doesn't mean it's good in the long term.