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.
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
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)
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.
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
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)
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.
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.
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!
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
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.
"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?)
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.
Yeah, I'm gonna rush right out and examine something that "...may not seem as easy to use as Matlab,"
Matlab is an astonishingly powerful application, but the ease-of-use factor is not there. And we're being told that this ROOT thing is more powerful and less user friendly?
Uh huh. If I need to use Matlab, I'll use Matlab. If I need something less powerful than Matlab, I'll use Excel. More powerful than Matlab...what the hell are you going to do with that kind of power? I'm sure somebody has a problem that's too big for Matlab, but I'm glad it's not my job to get my brain around those.
Why yes, I AM a rocket scientist!