Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Beginning Excel What-if Data Analysis Tools

samzenpus posted more than 7 years ago | from the get-started dept.

Books 151

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.

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.

cancel ×

151 comments

Sorry! There are no comments related to the filter you selected.

I'll tell you what ... (0, Offtopic)

eldavojohn (898314) | more than 7 years ago | (#14502449)

... once Excel switches to open document formats, I'll switch to opening documents about it.

Until then, I'm probably not going to pick up this book.

Re:I'll tell you what ... (0, Offtopic)

EvilSS (557649) | more than 7 years ago | (#14502527)

Then why even bother commenting about it? I mean, if you won't open a document about it (oh so clever, btw) why feel compelled to tell the world?

Because ... (0, Offtopic)

eldavojohn (898314) | more than 7 years ago | (#14502564)

a) It's slashdot, it's not "the world."
b) As a nerd, slashdot is one of the few places I can voice my angst and fustration
c) Said angst and frustration is actually understood here
d) I want people to know that I want an open document format and I want them to want it also

Re:Because ... (0, Troll)

EvilSS (557649) | more than 7 years ago | (#14503042)

Your angst? You are so disturbed by Microsoft Office that it causes you angst? Christ, did Windows XP cause you to seek therapy? Do you loose sleep at night over Internet Explorer? I mean, it upsets you so much that you feel the need to share your angst with others in a comment about a book review?

Oh I wish I had mod points (1)

winkydink (650484) | more than 7 years ago | (#14503596)

+1 pointing out hyperbole

Re:I'll tell you what ... (2, Insightful)

Pantero Blanco (792776) | more than 7 years ago | (#14502648)

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.

Re:I'll tell you what ... (1)

Stelminator (856547) | more than 7 years ago | (#14502618)

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?

Re:I'll tell you what ... (5, Interesting)

GogglesPisano (199483) | more than 7 years ago | (#14502635)

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.

Re:I'll tell you what ... (1)

Le Marteau (206396) | more than 7 years ago | (#14504301)

I agree. Having Excel (and Word) on your box is nice. World class, gold standard software. It gives me the warm and fuzzies.

Re:I'll tell you what ... (1)

Montecristo6 (398332) | more than 7 years ago | (#14504341)

[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]

Good luck w/OO Calc (2, Insightful)

everphilski (877346) | more than 7 years ago | (#14502649)

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.

Don't mod parent flamebait (1, Informative)

Anonymous Coward | more than 7 years ago | (#14502696)

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.

Good luck w/spreadsheets. (1, Insightful)

Anonymous Coward | more than 7 years ago | (#14502833)

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

re: ac (2, Interesting)

everphilski (877346) | more than 7 years ago | (#14502963)

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.

Data analysis (1)

arthas (654815) | more than 7 years ago | (#14503338)

Have a look at ROOT [root.cern.ch] . 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...

Re:Data analysis (1)

Moofie (22272) | more than 7 years ago | (#14503694)

Or, uh, I could just use Excel...

Re:Data analysis (1)

everphilski (877346) | more than 7 years ago | (#14503898)

Eh, if I am going to go all the way to C++ we have tools here at work we use...

for post-processing I use a mix of Excel and some in-house tools. All I was trying to say is Excel isn't a bad tool for dumping off some data and drawing some conclusions, or staring with an idea and drawing some ballpark answers. Its not the be-all and end-all but its a good start.

Mod Parent "Dumbass" (1)

sam_van (602963) | more than 7 years ago | (#14503370)

Seriously, AC, have you ever worked in an environment doing data analysis?

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.

Doesn't make it a good tool for data analysis (1)

aurelian (551052) | more than 7 years ago | (#14503820)

People use excel because it's the only thing they know, and it's supplied by default on corporate/institutional desktops. Engineers in particular use it a lot. That doesn't mean they shouldn't be using something more appropriate. I've even seen them used for numerical integration!

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.

Re:Good luck w/spreadsheets. (0)

Anonymous Coward | more than 7 years ago | (#14503524)

People who do "hardcore data analysis" will not be using a spreadsheet anyway.

It depends on the application. Excel has several points in its favor, including portability (particularly if the end result is meant to be used by non-programmers who are unlikely to have Matlab, etc.), real-time updates, visibility of processing (the source of most outputs can be viewed just by clicking on the cell), GUI features built into the the file, etc. Excel provides a lot of powerful functions that can be used to develop complex but accessible tools (and without even using VBA, pivot tables, etc).

My only complaint is that the Analysis Toolpak is not enabled by default. Well, that and it only supports 10-bit signed binary fields unless you perform perform the necessary binary math to get multiple 9-bit unsigned binary fields to work together as larger binary fields... And more conditional formatting/display options would be nice... But for something made by Microsoft, it is actually quite useful.

Must be a large tome (1, Troll)

mustafap (452510) | more than 7 years ago | (#14502474)

To fit that title on the cover.

WTF? (0, Troll)

jawtheshark (198669) | more than 7 years ago | (#14502505)

Excel? Has this site become "News for Accountants, Figures that matter"???

Re:WTF? (1)

Pantero Blanco (792776) | more than 7 years ago | (#14502549)

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.

Re:WTF? (1, Informative)

Anonymous Coward | more than 7 years ago | (#14502550)

Chemical Engineers are pretty much nerds. We use Excel for data analysis all the time.

Re:WTF? (1)

jwocky (900748) | more than 7 years ago | (#14502605)

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.

Re:WTF? (1)

sam_van (602963) | more than 7 years ago | (#14503236)

By the same token, I frequently have to deal with ridiculously cludged together Access databases that took months to develop that should have instead been done via an Excel Workbook with a well thought out data structure and user interface. Excel also affords a great deal of quick-and-dirty ability to drill down and manipulate data that Access can't offer most users (especially with Pivots).

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.

Re:WTF? (1)

Neoprofin (871029) | more than 7 years ago | (#14502620)

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)

engineering (2, Interesting)

everphilski (877346) | more than 7 years ago | (#14502674)

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)

ever heard of scripting languages? (1)

aurelian (551052) | more than 7 years ago | (#14503873)

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.

Re:ever heard of scripting languages? (1)

everphilski (877346) | more than 7 years ago | (#14503925)

C++ does the work (I'm a simulation programmer), Excel makes the money (pretty charts convince them to keep paying me).

WTF?-"Money just wants to be free". (0)

Anonymous Coward | more than 7 years ago | (#14502718)

"Excel? Has this site become "News for Accountants, Figures that matter"???"

Not as long as we belive everything should be free.

Re:WTF? (1)

AngryNick (891056) | more than 7 years ago | (#14503111)

Geek Supremacist. Accountants can be nerds too.

See Tax Technology [google.com] .

Re:WTF? (1)

jawtheshark (198669) | more than 7 years ago | (#14503199)

Next time, I'll try to use a smile so that all the humour impaired understand that I was joking. I worked for over five years in banking and know quite well that Excel is a capable tool and that accountants can be more nerdy than I am.

(Replied to your comment because, I found your comment the funniest one.... Geek Supremacist... Hilarious!)

Re:WTF? (1)

AngryNick (891056) | more than 7 years ago | (#14504232)

Sorry to lash out like that...it's part of my online persona.

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.

Re:WTF? (1)

Red Flayer (890720) | more than 7 years ago | (#14503476)

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.

Re:WTF? (1)

jawtheshark (198669) | more than 7 years ago | (#14503603)

I know my roots... Most definately. I owe my computer science degree to an accountant (okay, "Master in Economical Sciences") which is my dad. One of my first encounters with computers were spreadsheets: My dad was working in Symphony (remember that? No? Oh, well... Youngster!) and I as a curious kid wanted to know what was so special about it. He found no better way to explain it to me to show me how to budget my allowance.

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

Re:WTF? [OT] (1)

Red Flayer (890720) | more than 7 years ago | (#14503708)

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.

But any post that looks like it's impugning /. without valid, on-topic reasons is going to get modded down -- regardless of intent.

As one sig I recall said, "One man's (+5, Funny) is another man's (-1, Troll)."

Re:WTF? [OT] (1)

jawtheshark (198669) | more than 7 years ago | (#14503803)

Well, I didn't think my comment was offtopic or trollish... It was supposed to be funny, making fun of the slashdot slogan. Oh, well.... Complaining won't get me anywhere.

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.

Excel (5, Interesting)

mysqlrocks (783488) | more than 7 years ago | (#14502574)

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 (0, Flamebait)

ivan256 (17499) | more than 7 years ago | (#14502975)

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 (2, Insightful)

mysqlrocks (783488) | more than 7 years ago | (#14503105)

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 the features of Excel on the average user the first time they opened the application they would become extremely overwhelmed. 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. The risk is that users stop looking once they've got the application to do the basic things the user wants out of it. 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! So, what do they do? The start exploring VBA or try and find another program to buy while all-along those features are a few mouse clicks away! Oh, and how the hell did you trick me into defending Micro$oft?

Re:Excel (1)

ivan256 (17499) | more than 7 years ago | (#14503605)

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 see that there are more advanced features available that you're just not using yet? Even if you can come up with a good reason why, what's a good reason for making it nearly impossible to figure out on your own how to use the more advanced features, or even what those features are? The interface should point the user in the direction of the more advanced features as part of basic interaction with the application. This can be done without intimidating the novice user, or annoying the advanced user simply by having uniformity in the interface design. Unfortunatly, Excel (which, admitedly was once an incredible tool that I made a healthy living with) has become a morass of hastily implemented modern UI design bolted on top of layers and layers of legacy crap. There are even features in there for backwards compatible support with older worksheets implemented in such a way that those older features run, but you can't change, disable, or interact with them. I don't know why they bothered, because in general backwards compatibility in Excel is pretty much a joke, but... Try unlinking worksheets that were linked in Excel 4 under Excel 2000 or XP. Even an expert would be hard pressed to do it, and that's just one example. 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'm not sure I can say that about the current version, and if I could it would only be because the competition has all died off.

Re:Excel (1)

mysqlrocks (783488) | more than 7 years ago | (#14503833)

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 beyond the few features they've gotten comfortable with. Telling users that "all the features you need are already there" doesn't work when you're asking them to fork out money for an upgrade. Why would users pay money for an upgrade if there aren't new features? MS answer was to shove more, unnecessary features into the product or repackage existing features in a way that could be sold as a "new" feature.

A new way to amuse myself... (0, Offtopic)

gardyloo (512791) | more than 7 years ago | (#14503423)

...is to replace every instance of "Excel" in your post with "women". Seriously.

Re:Excel (1)

ILUsion.be (871905) | more than 7 years ago | (#14504140)

I do agree on the fact that Excel is a great program, it always amazes me with all its built-in functions; but as with other Microsoft programs, it does has its flaws: first of all, it is a hassle to work in two different language versions of the program Excel itself. In other Office programs, this doesn't pose a problem, but in Excel the programmers have chosen to translate the functions usedin the cells. For exemple, when one would use "=SUM(A1:B12)" in the English version, one have to type "=SOM(A1:B12" in a Dutch version (notice the difference O/U according to the language). A less subtle exemple might be "=AVERAGE(A1:B12)" which would translate to "=GEMIDDELDE(A1:B12)". So if one (meaning "me") is used to work on an English version of Excel and that same person would have to make an Excel document say at work or at school where the native language is most often used, one has to look for the right words for the functions. That is what I find the major flaw of Excel, which also happens to make it more comprehensible for beginners who aren't native English speakers. I would rather like Excel to accept the English terms in every version of Excel (this wouldn't be that much of work, since an Excel file isn't language-dependent) and to have a language extension for each translated version with the renamed functions in it.

Then, second, one of the flaws, which I can't really attribute to Excel itself but to the ease of use of Excel: many users abuse Excel by making a database in it. I just hate when that happens, I personally prefer programs which have a much steeper and longer learning curve which causes the user to be less productive at start but in long term the user will know what he's doing and will be more trained to solve new problems on his own.

reading it backwards (4, Funny)

digitaldc (879047) | more than 7 years ago | (#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 (2, Informative)

Tychon (771855) | more than 7 years ago | (#14504165)

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 (1)

revery (456516) | more than 7 years ago | (#14502610)

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.

Re:I imagine (1)

Bravoc (771258) | more than 7 years ago | (#14503030)

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?

FUCKER (-1, Flamebait)

Anonymous Coward | more than 7 years ago | (#14502614)

Excel? (0, Troll)

Anonymous Coward | more than 7 years ago | (#14502688)

Can it handle more than 65K records yet?

How about field limitations?

It couldn't even handle a small dataset the last time I tried it.

Re:Excel? (1)

smitty_one_each (243267) | more than 7 years ago | (#14502865)

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.

Re:Excel? (1, Insightful)

drinkypoo (153816) | more than 7 years ago | (#14503049)

In a world where you can buy desktop PCs with 2+GB memory, is there any reason to support only 64k rows?

Re:Excel? (3, Informative)

Orne (144925) | more than 7 years ago | (#14503052)

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 [msdn.com] .

Re:Excel? (1)

jawtheshark (198669) | more than 7 years ago | (#14503242)

aka Office 2003, currently in development

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

Re:Excel? (1)

Red Flayer (890720) | more than 7 years ago | (#14503649)

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.

Kirix Strata (1)

GreggyBUIUC (262370) | more than 7 years ago | (#14504304)

If you're trying to work with datasets larger than 65k lines you may want to check out Kirix Strata [kirix.com] .

-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 [kirix.com]

Re:Excel? (0)

Anonymous Coward | more than 7 years ago | (#14503293)

The current Excel is also limited to a 56 color palette. Granted, this isn't a big deal to me, but try telling that to your boss who MUST have this cell a specific color...

Re:Excel? (1)

iamlucky13 (795185) | more than 7 years ago | (#14503686)

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.

Calc (1)

squoozer (730327) | more than 7 years ago | (#14502724)

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.

Re:Calc (0)

Anonymous Coward | more than 7 years ago | (#14502890)


Unless you're planning on getting really sparkly and shiny as a result of reading the book, I think you are talking about 'gleaning' knowledge.

Excel - now more that just Word with gridlines (4, Interesting)

borkus (179118) | more than 7 years ago | (#14502808)

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.

Re:Excel - now more that just Word with gridlines (1)

davez0r (717539) | more than 7 years ago | (#14503310)

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

What-if Excel (1)

fossa (212602) | more than 7 years ago | (#14502860)

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

Ever used Excel in another language? (3, Informative)

jawtheshark (198669) | more than 7 years ago | (#14502991)

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 annoying, but I suppose that it's good for the end-users.

Re:What-if Excel (1)

dwhitman (105201) | more than 7 years ago | (#14503677)

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 (2, Informative)

Main Gauche (881147) | more than 7 years ago | (#14504093)

"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?)

Pivot Tables: Separating the men from the boys (3, Interesting)

winkydink (650484) | more than 7 years ago | (#14502901)

Once you learn how to use pivot tables, your entire perspective on Excel changes from "Word with Gridlines" to poor man's database.

Re:Pivot Tables: Separating the men from the boys (2, Interesting)

TerenceRSN (938882) | more than 7 years ago | (#14503219)

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.

Re:Pivot Tables: Separating the men from the boys (1)

sam_van (602963) | more than 7 years ago | (#14503316)

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.

Re:Pivot Tables: Separating the men from the boys (2, Informative)

blenderking (324269) | more than 7 years ago | (#14503410)

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.

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

Anonymous Coward | more than 7 years ago | (#14502915)

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.

What!? (1)

twfry (266215) | more than 7 years ago | (#14503100)

This is slashdot, there can be nothing useful or beneficial about any of Microsoft's products.

Logic simulations (1)

jabelar (913707) | more than 7 years ago | (#14503187)

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.

Excell is buggy! (2, Funny)

Stan Vassilev (939229) | more than 7 years ago | (#14503340)

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!

Re:Excell is buggy! (1)

VisiX (765225) | more than 7 years ago | (#14503417)

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.

Re:Excell is buggy! (1)

Stan Vassilev (939229) | more than 7 years ago | (#14503702)

"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 :)

Re:Excell is buggy! (2, Funny)

dantheman82 (765429) | more than 7 years ago | (#14503449)

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.

I gotta believe parent is a joke, but... (0)

Anonymous Coward | more than 7 years ago | (#14503512)

It's called operator precedence [wikipedia.org] . Look it up. Seriously.

Re:Excell is buggy! (0)

Anonymous Coward | more than 7 years ago | (#14503551)

That's a joke, right? I hope that's a joke.

Its called order of operations... (1)

taboguilla (945776) | more than 7 years ago | (#14503633)

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 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.) :-D

Re:Excell is buggy! (1)

the chao goes mu (700713) | more than 7 years ago | (#14503705)

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.

Re:Excell is buggy! (1)

Stan Vassilev (939229) | more than 7 years ago | (#14503919)

"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:
        6 * (a+b-c) = 2 * (a+b-c)
Remove the same term left/right:
        6 = 2

Re:Excell is buggy! (1)

onemorehour (162028) | more than 7 years ago | (#14504116)

yup. lots of crazy things happen when you divide by zero.

(a+b-c = 0)

Re:Excell is buggy! (1)

Stan Vassilev (939229) | more than 7 years ago | (#14504157)

"yup. lots of crazy things happen when you divide by zero." :P You guys keep spoiling my fun, but prove that wrong then:

You have one haystack. If you add one more haystack to it, the result is again one haystack.

Therefore, 1 + 1 = 1

Re:Excell is buggy! (1)

_archangel (30213) | more than 7 years ago | (#14504182)

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:
6 * (a+b-c) = 2 * (a+b-c)
Remove the same term left/right:
6 = 2
But only because you divided both sides by zero.

Mod this back up (1, Informative)

I Like Pudding (323363) | more than 7 years ago | (#14504079)

To the -1 Troll mod: It's a joke, retard

Yeah, (1, Insightful)

weierstrass (669421) | more than 7 years ago | (#14504187)

a really funny joke, too.

FOSS books (1)

Trogre (513942) | more than 7 years ago | (#14503384)

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 (2, Insightful)

Kuciwalker (891651) | more than 7 years ago | (#14503520)

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 (2, Insightful)

vijayiyer (728590) | more than 7 years ago | (#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.

Re:Please use the right tool for the job (1)

iamlucky13 (795185) | more than 7 years ago | (#14503822)

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 analysis on 24 hours of pressure data read at 100 Hz or generate hydraulic system and pump curves based on inputed parameters? Definitely don't use Excel.

It's not the ultimate tool, but it definitely does a lot more than add up dollar signs.

Re:Please use the right tool for the job (1)

debauched sloth (882145) | more than 7 years ago | (#14503874)

Matlab, faugh. The right tool would be R http://www.r-project.org/ [r-project.org]

Re:Please use the right tool for the job (1)

the_brobdingnagian (917699) | more than 7 years ago | (#14504207)

I participated in a physics project in high school. The project was about comics rays and the detectors generated data files of aproximately 30,000 events per day per detector. The project now has 29 active detectors. The first day of measurements I can find in the database is march 26 2004. These are huge amounts of data (for high school students). Yet they advice high school students to do the data analysis in Microsoft Excel. Because I disliked Excel I wrote my own python script to do the data analysis. This generated a lot of positive reaction from the project organisers who apparently disliked excel too. I started do write a GUI around it. Now all high school students who participate can use and modify my script and it is a lot easier to use than Excel. The useful part of the script took about 30 minutes to write, only the GUI costs a lot more time (I'll take a good command line script ;-) ). So I would advice anyone not to follow the obvious path (Excel), but follow a more fun, easy or even more free path. It is worth it.

Re:Please use the right tool for the job (0)

Anonymous Coward | more than 7 years ago | (#14504226)

If you do a lot of data analysis, you should look to the R project [wikipedia.org] or to a clone of Matlab like Octave [wikipedia.org] (both under GPL).

The shortest book? (0, Troll)

Skiron (735617) | more than 7 years ago | (#14503543)

"Beginning Excel". Instead use Open Office, Koffice or Gnumeric.

The End.

Buy it here! (0)

Anonymous Coward | more than 7 years ago | (#14503865)

Save yourself some money by buying the book here: Beginning Excel What-if Data Analysis Tools [amazon.com] . And if you use the "secret" A9.com discount [amazon.com] , you can save an extra 1.57%!

Excel (1)

rwinston (602519) | more than 7 years ago | (#14503948)

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?

Re:Excel (0)

Anonymous Coward | more than 7 years ago | (#14504118)

Try AMPL. The basic edition is available for free at www.ampl.com. AMPL is superior to Excel's Solver. Moreover, problems are easier to set up in AMPL than Excel. Also, there is a way for AMPL to read in data in Excel (check the AMPL site). Excel's Solver is for amateur's only.

Excel is the 2nd most misused software product (2, Insightful)

jim_mcneely (640547) | more than 7 years ago | (#14504257)

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.
Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>