Beginning Excel What-if Data Analysis Tools

Beginning Excel What-if Data Analysis Tools 151

Posted by samzenpus on Wednesday January 18, 2006 @03:17PM from the get-started dept.

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 discussion has been archived. No new comments can be posted.