Follow Slashdot blog updates by subscribing to our blog RSS feed

 


Forgot your password?
Close
typodupeerror
×
Books Media Book Reviews

Beginning Excel What-if Data Analysis Tools 151

Posted by samzenpus 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.

Beginning Excel What-if Data Analysis Tools More

Beginning Excel What-if Data Analysis Tools

Comments Filter:
  • To fit that title on the cover.

  • Excel (Score:5, Interesting)

    by mysqlrocks ( 783488 ) on Wednesday January 18, 2006 @02:29PM (#14502574) Homepage Journal
    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.

    • Re:Excel (Score:1, Flamebait)

      by ivan256 ( 17499 ) *
      No matter how well you think you know the program, you most likely have more to learn.

      Yup, that speaks volumes to how well the user interface was designed. Kudos!

      • Re:Excel (Score:3, Insightful)

        by mysqlrocks ( 783488 )
        Yup, that speaks volumes to how well the user interface was designed. Kudos!

        Well designed programs make it very easy to just jump in and start working. This creates a bit of a paradox. Once the user has discovered the boundaries of the "it just works" parts of the application they often don't wander into more advanced areas of the application. This may seem like bad interface design, but what's the alternative? Make it obvious to the user what all of the features are right upfront? If you dumped all of t
        • Oh, and how the hell did you trick me into defending Micro$oft?

          I don't know, but you use some intereesting logic...

          You need to ease a user into an application, make them feel like it's simple and easy to use and then slowly unveil the more advanced features.

          Fair enough...

          Then, later on, when the user wants more advanced features they assume those features don't exist in the application they were using because of course they would have seen it!

          Ok, now you lost me. Why does it have to be hard to make a user s
          • Microsoft seems to be so rushed to push it's customers through the forced upgrade cycle that they have destroyed the product. It's pretty depressing, because you could take Excel from 10 years ago and it would be a Best-in-Class product today.

            I think we're on a similar page here. Microsoft has "upgraded" and added "features" to a product that was already pretty well complete. They took a product that worked and broke it. Why? It goes back to my original point. Users get stuck and don't think to look beyo
    • ...is to replace every instance of "Excel" in your post with "women". Seriously.
    • Excel is crap! Here's the Abstract from a statisticians' report: "The open source spreadsheet package "Gnumeric" was such a good clone of Microsoft Excel that it even had errors in its statistical functions similar to those in Excel's statistical functions. When apprised of the errors in v1.0.4, the developers of Gnumeric indicated that they would try to fix the errors. Indeed, Gnumeric v1.1.2, has largely fixed its flaws, while Microsoft has not fixed its errors through many successive versions. Persons wh
    • As much as it is in fashion to bash Microsoft, I must say they did a very good job with Excel.

      Yes, they did an excellent job ripping off spreadsheet programs developed by their competitors in the 1980's.

      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.

      So, you

  • reading it backwards (Score:5, Funny)

    by digitaldc ( 879047 ) on Wednesday January 18, 2006 @02:30PM (#14502586)
    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

    • Re:reading it backwards (Score:2, Informative)

      by Tychon ( 771855 )
      Off topic, but reading backwards is a handy way to proof read a book. Your mind will fix many errors on its own; reading it backwards forces you to observe what you're reading as you're reading it.

      And I mean starting at the bottom of the page and reading up, not like you've typed.

      ?on, nuf si llits siht tuB
  • 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

    • Missed a couple!

      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 Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver, as I had writing Beginning Excel What-if Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver./p?

  • 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.

  • Excel - now more that just Word with gridlines (Score:5, Interesting)

    by borkus ( 179118 ) on Wednesday January 18, 2006 @02:49PM (#14502808) Homepage
    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.

    • i had a coworker who ran peoples' statistics (SPSS) for them for their theses and dissertations. she thought excel sucked until i pointed out that it was giving her incorrect values because she was using the sum tool incorrectly.

      i think that was the first time i ever called someone a noob IRL
    • "The scary thing is that the student had just graduated that past spring with a degree in finance.

      I don't see what that is scary that they asked you this. If they are in finance...they most likely have to calculate a LOT of percentages, and were probably just inquiring for a way to simplify their task.

      • Finance majors should be spreadsheet jockeys, they should be the ones teaching the Excel class teacher how to use some obscure feature in Excel. They shouldn't even have to think about how to calc a percent of a list; they should be able to recite at least two different ways to do this. What would you think of a coder who asked if there was a hello world library on their first day of class?

        If you are ever evaluating a finance person give them a system with Excel open and a short list of tasks, if they t
    • 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.

      There are two simple reasons for that: (1) Excel isn't very good at mathematical and statistical tasks, and (2) Excel's user interface for such tasks sucks.

      The scary thing is that the student had just graduated that past spring with a degree in finance.

      She probably knew how to

  • 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()?

    • What if Excel had used MEAN() instead of AVERAGE()?

      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

    • My favorite "what if": What if copy/cut/paste worked in Excel like it does in every other Windows program? It drives me crazy that the stupid clipboard forgets what you copied if you do anything other than paste.

    • What-if Users (Score:2, Informative)

      by Main Gauche ( 881147 )
      "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 we

  • Pivot Tables: Separating the men from the boys (Score:4, Interesting)

    by winkydink ( 650484 ) * <sv.dude@gmail.com> on Wednesday January 18, 2006 @02:55PM (#14502901) Homepage Journal
    Once you learn how to use pivot tables, your entire perspective on Excel changes from "Word with Gridlines" to poor man's database.
    • 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.
    • Right-o!

      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.

    • 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.
    • poor man's database
      I'm not sure any part of Microsoft Office can be called poor man's anything.

  • reviewer doesn't under linear programming (Score:1, Interesting)

    by Anonymous Coward
    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.
  • This is slashdot, there can be nothing useful or beneficial about any of Microsoft's products.

  • 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.
  • 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!

    • This is a joke right?

      Open up windows calculator and put it on scientific and you get 6. Every scientific calculator in the world will give you 6. Order of operations in mathematics dictates * before +.

      I'm sorry for the explanation if this was actually a really poor joke instead of the uneducated rant I assumed it to be.
      • "This is a joke right?"

        Yea it's in fact an old Excel joke I thought everyone knew.

        But instead I come back, find the post modded -1 Troll, and about a bunch of posts carefully explaining operator precedence to me.

        So the joke's on me :)
    • Good thing you never learned order of operations. First (), then * and /, then + and -, etc. Oh, and learn how to spell Excel - it really is like kicking yourself when you're down.
    • 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:

      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
    • Actually, as a forth programmer, I can tell you 2 + is a stack underflow error, 2 * is also a stack underflow error, and then 2 is 2. So the answer is 2.
      • "Actually, as a forth programmer, I can tell you 2 + is a stack underflow error, 2 * is also a stack underflow error, and then 2 is 2. So the answer is 2."

        Good. But 2 = 6 so I guess both Excel and you are right:

        Suppose:
        a + b = c
        Multiply the equation by 4:
        4a + 4b = 4c
        This can be written as:
        6a - 2a + 6b - 2b = 6c - 2c
        Reorganising:
        6a + 6b - 6c = 2a + 2b - 2c
        Constants before brackets:
    • To the -1 Troll mod: It's a joke, retard
  • 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.

    • Re:FOSS books (Score:1, Insightful)

      by Kuciwalker ( 891651 )
      Let people know they don't need to depend on proprietary software.

      Except, well, they do. Excel is far more powerful than Calc, which matters if you're, say, an actuary.

      And what to GIMP, LaTeX, or Blender have to do with anything...?

  • Please use the right tool for the job (Score:3, Insightful)

    by vijayiyer ( 728590 ) on Wednesday January 18, 2006 @03:45PM (#14503535)
    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.
    • I'm not entirely sure what you're talking about. For elementary data analysis, it's great. Got 1000 numbers and need the mean, standard deviation, and a histogram of the points? Piece of cake. Need to create simple, easy to style and control graphs? It does it fine. Want to create a table showing raw data, basic calculations performed on it, and final answers (whether for presentation or because it's helpful to see the data while you're working on it)? Excel makes it easy.

      Want to perform a level crossing
    • i think you're working under the (very) false assumption that the kind of "data analysis" you care about is the only kind there is. i've done lots of data analysis using simple spreadsheets (seldom Excel specifically, generally open source alternatives). it's a great tool for that job. but the kind of data analysis i care about rarely exceeds one data point a day, averages lower than that, and we're rarely looking at more than a few years. for doing data analysis against that, it's great.

      besides, Matlab pr [mathworks.com]
    • As an engineer, I love being able to see my data, and the changes that are made to them when I apply some transformation to them.

      I often perform the initial analysis in Excel (so that I can see what is actually happening) before I write code to do the same thing, albeit faster and to a great many more data-sets. It saves a lot of head-scratching time when the output from my shiny new program is totally wonky.
  • 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?
  • 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
    • Let me guess, they don't want to call the database developer because he seems like kind of a woundup, haughty prick?

      Seriously this seems like a great opportunity for you, and you're responding by getting frustrated that the company isn't full of database developers who understand data management theory. Typical IT Syndrome.
  • 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?
  • 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

    • still half finished usability.

      You're mistaken. OOo Calc has a user interface and functionality similar to M$Excel, including full online help. M$Excel is currently king because of inertia, the economic network effect and marketing, not because it's vastly superior.

      ---

      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.

Slashdot Top Deals

The perversity of nature is nowhere better demonstrated by the fact that, when exposed to the same atmosphere, bread becomes hard while crackers become soft.

Close