typodupeerror

## Advanced Excel for Scientific Data Analysis303

cgjherr writes "If the recent financial meltdown has left you wondering, 'When does exponential decay function stop?' then I have the book for you. Advanced Excel for Scientific Data Analysis is the kind of book that only comes along every twenty years. A tome so densely packed with scientific and mathematical formulas that it almost dares you to try and understand it all. A "For Dummies" book starts with a gentle introduction to the technology. This is more like a "for Mentats" book. It assumes that you know Excel very well. The first chapter alone will have you in awe as you see the author turn the lowly Excel into something that rivals Mathematica using VBA, brains, and a heaping helping of fortitude." Read on for the rest of Jack's review.
 Advanced Excel for Scientific Data Analysis author Robert de Levie pages 700 publisher Oxford Press rating 9 reviewer Jack Herrington ISBN 9780195370225 summary Use Excel for high end scientific data analysis akin to Mathemetica
When I first opened this book my mouth just dropped. It had been years since I had seen a book typeset using LaTeX. But in an instant it made sense as the book is crammed packed with the kind of equations that would have been a nightmare to build with any other tools. Chapter after chapter has everything a really smart person needs to do curve fitting, statistical measures, differential equations, time-frequency analysis. But don't expect a play by play here. You will get the equations, set within a few dense paragraphs, with maybe a spreadsheet and a chart or two to show the results.

The first chapter concentrates on the getting the most out of Excel as a tool. All the chapters that follow dig into specific data analysis techniques. Chapters two, three and four are on least squares. Chapter five and six cover the analysis in the time domain including fourier transforms. Chapter seven covers differential equations. Chapter eight returns to Excel by digging in deeper into macros. Which leads into chapter nine, where we dig deeper into basic mathematical operations. Chapter ten covers matrix operations. And chapter eleven wraps it all up by giving you some spreadsheet best practices.

In University style there are also some exercises that you can do along the way if you want to tweak your brain pan a little more. To amuse myself I tried a few and I believe the book would have assessed my attempts 'wanting' if it had a voice to tell me.

Where most books like this would have several authors this book has just one; Roberte de Levie. This means that the tone, style and quality of the book is consistent throughout. A fact that you will come to appreciate as the book wades in ever increasingly deep data analysis concepts as the chapters roll on.

Though I would have preferred the book to have code samples in C#, I understand that the language of Excel is VBA and I guess I have to live with that. Thankfully VBA has come a long way and if you so inclined it would likely be easy to translate the code into C#, Java, or whatever else you like.

The fact that one person wrote the book left me wondering, "Who is this guy?" In my minds eye I kinda of figured he would look like one of those pulsing brain guys from Star Trek. Turns out he is a professor at Bowdoin College. And his fields of study include ionic equilibria, electrochemical kinetics, electrochemical oscillators, stochastic processes, and a whole lot more stuff that almost seems made up to sound impressive.

When this book isn't serving as an amazing reference for both Excel, scientific problem solving, or just insane equations it serves other purposes as well. It's a handy portable IQ test, as the count of pages you can grind through in one sitting, plus 90, is roughly your intelligence quotient. And if you fail at that you can always put a copy of the book, along with the Orange Bible, under your pillow and try to osmose your way to becoming the Kwisatz Haderach.

In all seriousness, this is a great book. It represents the kind of in-depth work and research we used to see in books that came out twenty years ago. Robert is to be applauded for his work. This is an excellent resource for anyone looking to do scientific data analysis but who was unaware of the powerful capabilities that Excel provides that is likely waiting just one Startup menu click away.

The book is not without fault. I would have preferred that it had been in color, or at least have one color section to show some of the more impressive visualizations that I'm sure would look great in color. In addition the index is silly short for a book that clocks in at 700 pages. But those are only minor quibbles for what is all-in-all an amazing piece of work.

You can purchase Advanced Excel for Scientific Data Analysis from amazon.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.

## Advanced Excel for Scientific Data Analysis

• #### alternately.... (Score:5, Insightful)

on Wednesday October 01, 2008 @01:04PM (#25221095) Homepage

Don't do it! [burns-stat.com]

• #### ...there's a better solution (Score:5, Interesting)

on Wednesday October 01, 2008 @02:09PM (#25222169)

Python for scientific analysis [scipy.org],

Python is the solution I recommend for everyone who looks for tips on advanced Excel uses. Excel is OK if you just want some quick and dirty solution for a small problem, but if you have to go to the trouble of reading a book, Excel is clearly not the best solution.

For scientists and engineers who need something more than what Excel (and possibly Matlab) offers, I recommend starting with either A Byte of Python [swaroopch.com] or Dive Into Python [diveintopython.org].

• #### Re: (Score:2)

I use python for certain tasks, and it's great and handy. For data analysis tasks, I tend to use R, which is both far more interactive than python, has much better graphing solutions than python or excel,and supports more statistical analysis methodologies than pretty much anything else. I can prototype and figure out a methodology in R (and provide provable results) long before I get to start running my python script.

• #### Re: (Score:2)

I like scipy, but it certainly is not the 'in between' the parent is looking for. I haven't used it, but my wife likes gnumeric. From what I've seen peeking over her shoulder it looks to be much more apropos.

• #### Bad math (Score:3, Funny)

on Wednesday October 01, 2008 @01:06PM (#25221121)

If the recent financial meltdown has left you wondering, 'When does exponential decay function stop?' then I have the book for you. Advanced Excel for Scientific Data Analysis

So THAT's why we had a financial meltdown. All of those investment banks were doing their books and analysis with Excel 2007.

• #### Re: (Score:2, Informative)

Speaking as a wageslave in the Financial Industry: Yes, excel is the standard. For accounting. For modelling. For almost everything.

It scares me. Deeply.

• #### Re: (Score:2)

Right, and when the data moved past 1,048,576 rows [microsoft.com] the whole thing crashed and took the market with it. Little known fact: the .com crash in 2000 was actually caused by exceeding the earlier limit of 65536 rows.

• #### incongruous (Score:5, Funny)

<dan.kimberg@com> on Wednesday October 01, 2008 @01:08PM (#25221149) Homepage

There's something hard to reconcile about the reviewer's obvious awe and the fact that the book was written by someone who thinks doing meaningful scientific data analysis in Excel is a good idea.

• #### Re:incongruous (Score:4, Insightful)

on Wednesday October 01, 2008 @01:09PM (#25221167) Homepage Journal
Why isn't it a good idea, and does this apply equally to OpenOffice?
• #### Re:incongruous (Score:5, Interesting)

on Wednesday October 01, 2008 @01:34PM (#25221547)

Why isn't it a good idea, and does this apply equally to OpenOffice?

It's OK for simple stuff, but trying to do something like implementing a loop in a spreadsheet. And yes, the criticism applies to OO as well.

There are very good packages out there - some open source - for doing scientific analysis. I'd recommend R or Octave (a matlab clone), personally. Also, Python + NumPy + SciPy + Pylab is great for doing Matlab-like things, and it's all free as well.

• #### Loops in spreadsheets (Score:2)

It's OK for simple stuff, but trying to do something like implementing a loop in a spreadsheet.

This is one reason the VB scripting turns out to be highly useful. But that said... for half the things loops might be useful for in a normal context, they're wrong in a spreadsheet. Iterating over a set of data isn't done with loops, it's done with applying formulas over a range of cells. And if you turn on iteration for the spreadsheet, it *is* possible to build flow-controlling state machines without using the

• #### Re: (Score:3, Interesting)

This is one reason the VB scripting turns out to be highly useful.

I recently had to do a project in VBA/Excel after years away from it, and it made me want to dig my eyes out with a spoon.

Don't ever write custom functions... ever. You'll thank me when you don't have to worry about whether or not they silently fail.

And once I had my whole spreadsheet corrupt for some reason... had to open on a Mac and re-save it. Then it worked fine on the PC again! Aye.

• #### while True: (Score:2)

it's done with applying formulas over a range of cells

I need infinite loops, you insensitive clod!

• #### Re: (Score:2)

Iterating over a set of data isn't done with loops, it's done with applying formulas over a range of cells. And if you turn on iteration for the spreadsheet, it *is* possible to build flow-controlling state machines without using the scripting engine. Not particularly natural for most imperative programmers, but definitely possible.

You could go to all that trouble...or use the most appropriate tool for the job. Yes, if you kludge the living shit out if it, you can get Excel to do a whole lot of things th

• #### Re: (Score:2)

Prototype in R, and if you need to make it repeatable, maybe push it into python (or write a wrapper that gives nice command line options in python.)

• #### Re: (Score:2)

You can call R from python directly (as in in pass data and get results back) given the proper modules.

• #### Re: (Score:3, Interesting)

I use spreadsheets to prototype and document ideas. Once I had thought a full blown reference implementation in a spreadsheet would be a good idea (basically, more time was spent on the reference than the final project). Fact is spreadsheets are good for one-off problems, or simple problems that gather lots of data (ex. accounting, statistics). When you have a heavy data model, heavy logic model, and complex results, spreadsheets are ultimate FAIL. They are good for developing algorithms quickly, good for t
• #### Re: (Score:2)

- Crappy visualization
- Sometimes a 2-D data structure ain't the best
- Excel's pivot tables get the job done, but they have some pretty inconvenient behavior
- Sometimes you want to define a formula once and apply it everywhere, not once per row (when you have ~60k rows). Excel can really bog down when you start having a lot of formulas for big datasets; other tools handle this better
- Last I checked it still had some accuracy issues
- And many more

A lot of the time Excel is fine. But it's usually not the b

• #### Re: (Score:3, Interesting)

There's something hard to reconcile about the reviewer's obvious awe and the fact that the book was written by someone who thinks doing meaningful scientific data analysis in Excel is a good idea.

Care to expand on why you think you can't do 'meaningful scientific data analysis in Excel?' Are you one of these people who 'reviews' books without actually reading them?

• #### Re: (Score:3, Interesting)

Last time I checked (and it has been a while), Excel has computational bugs in it which can result in valid data in -> garbage out. In my mind, 'meaningful scientific data analysis' involves accurate computation. But maybe I'm just a dreamer.
• #### Re: (Score:2)

Last time I checked (and it has been a while)

How long is a 'while?'

• #### Re: (Score:3, Insightful)

Well, 2007 has bugs in it. I don't use Excel, I use something that can utilize math correctly. Have you checked your spreadsheet program? Or do you just assume that Microsoft does everything correctly?
• #### Re: (Score:2, Interesting)

Beating on Excel is easy. Do you check your math utility? Or do you just assume your compiler/vendor does everything correctly?

• #### Re:incongruous (Score:5, Insightful)

on Wednesday October 01, 2008 @01:42PM (#25221695) Homepage
Well, I do put faith into my fortran compiler.
• #### Re: (Score:2)

you're lucky. my abacus has floating-point computation errors.
• #### Re: (Score:3, Insightful)

Well, 2007 has bugs in it. I don't use Excel, I use something that can utilize math correctly. Have you checked your spreadsheet program? Or do you just assume that Microsoft does everything correctly?

I use Excel for daily business functions and data analysis, and will continue to do so, but I don't assume Excel is perfect. I do what I should do with any program I use for calculations, though: I stay aware of all of the quirks and bugs I can of the program, and try to work around them.

Every program is going to have a bug or two (or five thousand, seeing as Excel is part of MS Office), but part of working with software is to know what those are and learn to not let them ruin work.

• #### Re: (Score:2)

Care to expand on why you think you can't do 'meaningful scientific data analysis in Excel?'

A while back, and this might not be true today, Excel gave errors when doing certain functions. [maryparker.org] In this case it was standard deviation.

Several scientific papers came out which had to be recalculated using mathematica or matlab or spice or something, because the data couldn't be trusted after the error was exposed. A small error introduced can be very large, depending how it was used and in the order the data were gathered ... thus Excel got a very bad name for doing "meaningful scientific data analysis."

• #### Re: (Score:2)

Care to expand on why you think you can't do 'meaningful scientific data analysis in Excel?'

Because I have
a. done meaningful scientific analysis
b. used Excel

I've actually used a combination of perl, matlab, C, weka and pen and paper, so that might sound even worse, but it's not :)

• #### Re: (Score:3, Insightful)

Care to expand on why you think you can't do 'meaningful scientific data analysis in Excel?' Are you one of these people who 'reviews' books without actually reading them?

Someone else has already posted a link to a page that nicely summarizes many (not all) of the problems with using excel for science. But there is virtually no statistical technique which isn't already better implemented in R (free) and many other statistical packages. Real stats packages provide implementations of a given technique that are at least as reliable, provide more control, more options, more diagnostics, and often more guidance. The built-in stuff in Excel is so oversimplified that I think if

• #### Wrong Tool (Score:5, Interesting)

on Wednesday October 01, 2008 @01:14PM (#25221253) Journal

Talk about the wrong tool for the job. If you need to do any sort of serious data analysis, use R, not Excel.

• #### Re: (Score:3, Informative)

Or use them together: Use RExcel [univie.ac.at] and RCommander [mcmaster.ca].

• #### Re: (Score:2, Insightful)

While I agree, sometimes being an engineer or analyst means working with one or two or six hands tied behind your back because of time, money, or IT-imposed user-permissions. If you aren't capable of identifying the sources of error in your data as well as those caused by your tools, then you are probably going to do a poor job even with the best tools. Bad tools should never be an acceptable excuse for delivering faulty analysis.
• #### Re: (Score:2)

If I hadn't already commented similarly, I'd mod you up. R has been my bread and butter for serious (and ad-hoc) analysis for a few years now. It's fast to write, easy to get data into and out of, provides fantastic stats support, and creates beautiful graphs with very little effort. The interactivity is incredibly useful when prototyping.

• #### eh? (Score:4, Insightful)

by Anonymous Coward on Wednesday October 01, 2008 @01:16PM (#25221273)

"The first chapter alone will have you in awe as you see the author turn the lowly Excel into something that rivals Mathematica using VBA, brains, and a heaping helping of fortitude."

Then why not just use Mathematica?

• #### Re:eh? (Score:5, Insightful)

on Wednesday October 01, 2008 @01:33PM (#25221519)

"The first chapter alone will have you in awe as you see the author turn the lowly Excel into something that rivals Mathematica using VBA, brains, and a heaping helping of fortitude."

Then why not just use Mathematica?

1. you want to interact directly with excel data you receive
2. you need to give the results to someone w/out mathematica
3. a license of mathematica costs \$2500, vs \$150 for Office Home and Student
• #### Re:eh? (Score:5, Informative)

on Wednesday October 01, 2008 @01:44PM (#25221745)

If you're going to mention that the Office costs \$150 for a student version, you might as well mention that Mathematica's student version (identical to the full version, except for a banner upon printing) is \$140.

• #### Re: (Score:3, Informative)

Oh, and as for sharing w/ people who don't have Mathematica, that's what the free Mathematica Readers are for.

• #### Re: (Score:2)

except that MS Office Home and Student is legit to use if you're not a student. Mathematica's student version is NOT. read the licensing.
• #### Re: (Score:2, Insightful)

Because I already have Excel, and Mathematica is another 120 dollars?
• #### Re: (Score:3)

\$120? You must be joking. A professional license of Mathematica is more like \$2500.
• #### Re: (Score:2)

Because I already have Excel, and Mathematica is another 120 dollars?

uh, it's about another \$2500 actually... student pricing is about \$135 or so.

• #### I have not read the book (Score:3, Interesting)

on Wednesday October 01, 2008 @01:17PM (#25221291) Homepage

But it seems you have not seen Word 2007 equation editor. It's close, in both capabilities, and output quality, to LaTeX. Too bad the rest of Word sucks ass.

>> that would have been a nightmare to build with any other tools

• #### Re: (Score:3, Informative)

The company that developed the equation editor (MS licenses a neutered version of it for Office) does have a full-blown version available...

• #### Re: (Score:3, Informative)

Yeah. Try writing a paper full of equations with Word. You will feel like bashing the monitor in before you are a fifth of your way into the task. (Assuming you know LaTeX).
It may be close now in output quality, but any search, point and click system will always be inferior to LaTeX when it comes to equations.
• #### Re: (Score:3, Interesting)

Output quality: does it have automatic equation numbering? An equivalent for BibTeX? Intelligent modifiable Table of Contents? Ability to replace a math symbol wherever used with another? Change aforementioned numerations at will?
• #### Re:I have not read the book (Score:5, Insightful)

on Wednesday October 01, 2008 @02:01PM (#25222047) Homepage
...in the same way that MS paint is as capable as photoshop...

Yes, I use both. LaTeX if I have a choice, Word if I need to exchange docs with less enlightened colleagues.
• #### Wrong tool for the job (Score:5, Informative)

on Wednesday October 01, 2008 @01:18PM (#25221305)
Someone should tell this guy about SAGE http://www.sagemath.org/ [sagemath.org]
• #### Re: (Score:2)

I've seen a lecture by the main developer of SAGE. It seems to be more a tool for doing mathematics research. I've heard of scientists using S-PLUS and R [r-project.org] (the open source alternative to S) for their research. In any case, any of these tools is probably better than a spreadsheet for serious scientific research.
• #### Spreadsheets are the right tool ... (Score:5, Funny)

on Wednesday October 01, 2008 @01:25PM (#25221407)

You see, there is a fundamental problem in science and the problem can be summarized as this: how do you get the right results in order to optimize the grants that you receive. Spreadsheets are ideal for this purpose for two reasons. First of all, they are designed to handle financial data. This is great because financial data are what grants are all about. For example: will result X allow for a conference in Hawaii or California this year.

The other big reason to use spreadsheets is that they make data more maluable. Normal scientific tools make it difficult to micromanage the data that you acquire, partially because the people who produce that software have this mistaken notion that data has to be managed in a consistent way. So you're usually stuck doing the same thing to an entire dataset, and it's even difficult to treat different datasets in different way. But spreadsheets expose all of that data, so it is easy to tweak an observation here and a variable there to get the desired result to maximize your grant.

So you see, spreadsheets are a tremendously valueable tool for scientists. It is the best tool for the job.

• #### Re: (Score:2)

Spreadsheets are right for much of what you state... but surely one could use another tool to generate the data and simply import the data into Excel to perform more basic operations.

For some things, especially complex math, spreadsheets simply are much less efficient and completely unnecessary.

• #### That's nothing (Score:5, Insightful)

on Wednesday October 01, 2008 @01:30PM (#25221483) Journal

turn the lowly Excel into something that rivals Mathematica using VBA, brains, and a heaping helping of fortitude

So? What's so special about that? You can turn C, Fortran, or even assembly language into something that rivals Mathematica using brains and a heaping helping of fortitude. This is arguably a better deal, since you don't need the VBA.

--MarkusQ

• #### When all you have is a hammer... (Score:5, Insightful)

on Wednesday October 01, 2008 @01:30PM (#25221497)

...everything looks like a snowglobe!

Hardcore data analysis in Excel is almost always a bad idea. You can almost always find a way to do it in excel, and you can almost always find a way to do it better, faster, and cheaper somewhere else.

R, MatLab, Mathemateica, Python/Numpy, SigmaPlot, and any number of old, well written, debugged and vetted numerical libraries written in C or Fortran. I've used all of these at various times to solve something that a co-worker couldn't figure out how to do in Excel.

I fit quick linear regressions in Excel. For *anything* else, there is a better choice.

• #### Re: (Score:2)

Hardcore data analysis in Excel is almost always a bad idea. You can almost always find a way to do it in excel, and you can almost always find a way to do it better, faster, and cheaper somewhere else.

I would have to disagree, having used both Excel and "rolled my own" in pure C.

My own code runs a few thousand times faster, I know exactly where the errors might pop up, and I don't need to try to squeeze the data into a form suitable to whatever MS decided I should use this week, I just handle anything
• #### Re: (Score:2)

It's much more effective to use a well-respected numerical analysis package rather than rolling your own everything from scratch. NumPy, for example, lets you do mathematical analysis far better than Excel very quickly.

• #### Spreadsheets are not the right tool (Score:3, Informative)

on Wednesday October 01, 2008 @01:34PM (#25221541)
As a graduate student in physics, I have never seen a serious researcher use excel for data analysis. Nor for that matter, is it common to see a scientist using windows for the OS--all linux and mac OS. This is akin to writing a book about publishing scientific papers with office. Instead, learn LaTex... The only group of people who use excel for large data analysis are financial types and MBAs. Need I remind you how that turned out?
• #### Re: (Score:3, Interesting)

As a graduate student in physics, I have never seen a serious researcher use excel for data analysis.

Nor for that matter, is it common to see a scientist using windows for the OS--all linux and mac OS.

This is akin to writing a book about publishing scientific papers with office. Instead, learn LaTex...

The only group of people who use excel for large data analysis are financial types and MBAs. Need I remind you how that turned out?

Oh, so that's why at APS meetings I've seen maybe 5 presentations, ever (out of at the very least 500) given on something other than Windows (Ubuntu once, MacOS the other few times), despite the fact that nearly every speaker uses his or her own laptop for the presentation. Wait...my data seems to indicate that physicists hardly ever use Mac or Linux at all!

If we're just talking about computers controlling instruments, then I see about 90% Windows, 10% Linux if the instrument costs less than a million doll

• #### Excel is a horrible tool (Score:5, Insightful)

on Wednesday October 01, 2008 @01:35PM (#25221571)
for scientific data analysis.

I know it is popular and many science and engineering faculty lazily encourage their graduate students to use it. However, something like matlab beats the crap out of excel any day. Spreadsheets tend to obfuscate relationships between data, require a lot more clicking (read human intervention) and waste time that could be spent thinking about the data, and are singularly unsuited for analysis of similar sets of data (a situation any scientist faces when he has to do a series of experiments).
Matlab might take sometime to initially write the scripts, but it is so powerful and extensible that no one in their right mind would want to use excel. If you are a slave to spreadsheets, get yourself a copy of Microcal Origin or Labplot.

Excel is especially unsuited to the task of preparing figures for scientific publications. The default formatting is at once wrong for the task and hard to change. Once you set your preferences in matlab (easy to do), you are set for life.

In my experience, excel is also rarely used for anything serious outside of US. Maybe its an indictment of how lazy, slow witted and easily misled our pool of talent is becoming.
• #### Re: (Score:2)

Thank you for mentioning Labplot (http://labplot.sourceforge.net/). I'd also like to put in a shout-out for QTIPlot (http://soft.proindependent.com/qtiplot.html) and Scilab (http://www.scilab.org/), and of course the aforementioned Sage (http://www.sagemath.org/).

QTIPlot and LabPlot, in particular, have amazingly responsive developers, who seems to go out of their ways to help people.

• #### Re: (Score:2, Funny)

by Anonymous Coward

I don't quite understand your post. Do you have a Power Point presentation?

• #### I must disagree (Score:3, Interesting)

In my experience, excel is also rarely used for anything serious outside of US. Maybe its an indictment of how lazy, slow witted and easily misled our pool of talent is becoming.

I recently spent some time in Japan in a design group for a large Japanese company. I was showed the massive spreadsheet used to calculate power plant capacity and consumption. I almost cried. The whole sheet was based upon one large circular reference. Nobody understood it and it referenced steam tables through a plugin but
• #### Re: (Score:2, Interesting)

In my experience, excel is also rarely used for anything serious outside of US. Maybe its an indictment of how lazy, slow witted and easily misled our pool of talent is becoming.

I have experienced whole companies running on Excel spreadsheets - they use it for accounting, instead of a database, and, you guessed it, scientific data analysis. The company I'm talking about is in the power supply industry.

• #### Excel does not Excel (Score:4, Interesting)

on Wednesday October 01, 2008 @01:38PM (#25221623)
When I worked in the semiconductor industry in the late 90's, Excel nearly cost us several hundred grand. It had "helpfully" autocorrected a code in the documentation for a mask used in one of our clock buffer chip products. Had the engineers not caught this mistake in the printout, the fab of the chip would have been botched. The engineers were mad as I recall because they would change the code and Excel would change it back. If you can't prove what your tool is doing, you don't get to use it is what they taught me in engineering school.
• #### Using Excel for scientific calculations (Score:3, Funny)

on Wednesday October 01, 2008 @01:41PM (#25221679) Homepage Journal
Is perfectly safe and trusty for that kind of work. Thats why we are using it here at the Large Hadron Co
• #### Re: (Score:2)

I don't know about Excel, but using my copy of OO.o calc, I have concluded that this type of "broken-off ending" joke will become 3.71% more funny once the LHC is back online.

(Insignificant, p less than .05, however, because no one will be laughing then, either)

• #### Excel *could* replace SPSS (not Mathematica) (Score:4, Informative)

on Wednesday October 01, 2008 @01:47PM (#25221805)

SPSS has now become the standard data analysis package for quantitative studies in social sciences. It's very crappy software, and it wouldn't take a whole lot of augmentation to get Excel do what SPSS does.

The problem is that social scientists don't want to mess with the internals too much, and SPSS made for them a point and click interface - in effect, they out-Microsofted Microsoft. They charge an insulting \$1500/copy and completely dominate the universities, so they're making good money.

They seriously need some competition.

• #### Re: (Score:3, Insightful)

Now, who told you SPSS is crappy software? It's a widely used software for not only Social Science but for the Biology and Medical fields - in short, for anyone serious about statistics who's not a statistician.

Excel, OTOH, has a long track record of errors. Microsoft does not have the expertise for numerical and statistics software.

Which is not surprising, if you remind yourself that Microsoft did not even have security expertise for its own main product line...This software landscape is dominated by Matla

• #### Age of Excel (Score:2)

Advanced Excel for Scientific Data Analysis is the kind of book that only comes along every twenty years

Excel was introduced in 1990. So, assuming that it was introduced with a book just like this one in 1990, that would be "a kind of book that comes along every eighteen years". I'm certain the poster would have realized this had he or she applied what she'd read in the first edition to do the proper calculation.

• #### What versions of Excel are covered? (Score:2)

In particular, how much is applicable to the Mac versions? 2008 dropped VBA support, I believe, which sounds like it could wipe out much of the applicability of this book.
• #### Anecdote about Excel (Score:3, Funny)

on Wednesday October 01, 2008 @02:09PM (#25222165) Journal
When I was a freshman in engineering school, my intro to engineering class required us to purchase a book similar to this. We were given two class periods to work with Excel, supervised by a TA. (it was considered a lab) I remember the assignment involved proving that sin^2+cos^2=1.

If you couldn't figure out Excel within those two class periods, it was recommended that you switched your major to business administration. The business administration school had a semester long class devoted to learning Excel.
• #### Re:Anecdote about Excel (Score:5, Funny)

on Wednesday October 01, 2008 @02:24PM (#25222379)
When I was a freshman in engineering school, my intro to engineering class required us to purchase a book similar to this. We were given two class periods to work with Excel, supervised by a TA. (it was considered a lab) I remember the assignment involved proving that sin^2+cos^2=1.

Proving that with Excel? How does that work? That's a trigonometry problem, and it follows from the definitions of the sine and cosine functions, and from Pythagoras's theorem. You do it with a pen and paper and you write 'QED' at the bottom. To prove it with Excel, you'd have to calculate the result individually for every possible angle, and unless Microsoft have released an update I haven't had yet then Excel doesn't have a transfinite number of available rows.

Oh, wait...

engineering school

That's dangerously close to reality. That's where they think that if something works the first fifty million times, then it's going to work every time.

Still, it could be worse. You could be in If you couldn't figure out Excel within those two class periods, it was recommended that you switched your major to business administration.

Yeah.

• #### Re: (Score:2)

To prove it with Excel, you'd have to calculate the result individually for every possible angle, and unless Microsoft have released an update I haven't had yet then Excel doesn't have a transfinite number of available rows.

Ok, so it wasn't a strict mathematical proof. Let's face it, in the real world you rarely have to worry about the infinitesimal. With empirical data in today's digital age, you will have a sampling rate to contend with. Why use an integral when a Riemann sum is all your data can support?

• #### Re: (Score:2)

Proving that trigonometric identity using Excel is surely an amazing feat...
• #### comparison of Mathematica and Excel VBA .. (Score:5, Informative)

on Wednesday October 01, 2008 @02:09PM (#25222167)
You cannot be serious ..

"Excel 2007, like its predecessors, fails a standard set of intermediate-level accuracy tests [mathforum.org] in three areas: statistical distributions, random number generation, and estimation"
• #### Am I supposed to take this review seriously? (Score:2)

Some of us have to crunch numbers every day and it's interesting to consider Excel as a tool for this purpose. But to then have a reviewer talk about things like "insane equations" makes it clear that the reviewer sees "equations" as some kind of esoteric icon associated with peculiar people with "pulsing brains" rather than the bread and butter of the jobs of thousands, if not millions of people the world over. How can /. post a review by such a clearly ignorant reviewer? It verges on embarassing to read.
• #### Excel for Scientists and Engineers (Score:2, Informative)

Another book in the same line is E. Joseph Billo's "Excel for Scientists and Engineers," Wiley-Interscience, 2007 ISBN 978-0-471-38734-3, including CD.

You may or may not agree with using Excel, but if you do, this book will help with roots of real and imaginary equations, ordinary and partial differential equations, matrices, and statistics.

Sometimes you just don't have the luxury of using Matlab, Spotfire, etc.

• #### Excel is not best suited for the job (Score:2, Insightful)

I thumbed through the book but not impressed.
The author probably has used excel as best as any one can in doing the task he intends. But for most of people, the effort to acquire the skill by reading the book is not well-spent, since one can probably learn other tools which really intended for scientific analysis.

For statistic packages, R probably is much better, though I would prefer SAS. Try a huge data set (200MB, and put it in excel, your system will crawl before excel crashes, but in SAS, it will be re

• #### This is not Excel, its VBA (Score:3, Insightful)

on Wednesday October 01, 2008 @02:26PM (#25222397)

So according to the book, here's the recipe:
1. Write your data analysis software in VBA
2. Use the Excel cells, buttons, bells, and whistles for the I/O
3. Profit!

The math is actually irrelevant. Any computational mathematics book that respects itself uses pseudocode for the examples. If it is possible to program in one programming language it should be possible in any other language too.

I tried it too, although I wasn't nearly as crazy to do any numerical computations in VBA. I wrote the program in pure good old Fortran 95, wrote some VBA scripts to read the Fortran ASCII output, and set everything else up in Excel that my boss liked (I'm a chemical engineer). There you go: it's fast to program, fast to run, easy to maintain.

I would like to see anyone try to keep up with the Microsoft paraphernalia between VBA-Excel versions, if the whole thing is written in VBA. Not to mention the problems that I had with the locale when I tried to run the VBA code in a computer running a german version of Excel that had decided that the decimal point is there as a thousands separator and the comma was used as a decimal point. The setting for it in Excel was nowhere to be seen (I still haven't really figured it out. The central Windows setting seemed to have no influence on it, although I suppose it should) and 1.234 was then 1234 and 1.2E-02 was a character string. Oh, the pain... Thankfully, my *basic* Fortran part absolutely did not care, it just worked, and only the I/O needed to be reviewed.

Try to send the program to a customer without knowing what kind of Excel version he is running. We had to go as far as Office 97 just to be sure, and there was still the problem with the locale. After a year, the I/O was useless, but who cares? It was only 1% of the code.

I would still use Excel, but for nothing other than the most trivial tasks. There are wonderful libraries out there that work with Fortran and produce very nice graphs on the fly.

• #### I can't believe you people... (Score:4, Insightful)

on Wednesday October 01, 2008 @02:49PM (#25222779) Journal

Look at all those posts saying "Excel is not the right tool for this" or "When all you have is a hammer...". The point was not grokked by those folks.

I'll lay it out for you, plain and simple:

This book is like installing a linux kernel onto a wristwatch.

We should be marvelling at the feat, not lambasting a tool that was "hacked" to do so much more than it is normally used for. If you can't appreciate that kind of work, maybe you should just stick to appreciating fine arts.

• #### Re: (Score:3, Insightful)

While some of us admire the author for doing something akin to fitting Linux on watch, some of the objectors are pointing out the watch still needs to accurately tell time. There are a number of papers that show how Excel does not have the accuracy necessary for detailed scientific analysis. Some things like random number generation are not implemented correctly. So back to the watch analogy, as long as the author clearly divulged that the watch only tells time accurately to the minute and is not waterpr
• #### How do you know if a book was typeset using LaTeX? (Score:5, Informative)

<peter@si l m a ril.ie> on Wednesday October 01, 2008 @04:27PM (#25224277) Homepage

> It had been years since I had seen a book typeset using LaTeX.

The publishing industry (including my company) typesets books using LaTeX all the time. The reason you don't notice it (apart from the superior quality) is that it does its job of typesetting very well.

If this book has been typeset using LaTeX then I'm a Dutchman, or something has gone very wrong (and I'd like the author to contact me to let me know what).

Perhaps he was given faulty fonts, perhaps he was using a badly-written publisher's style, or perhaps he -- or his editor -- spent a long time making it look as bad as possible. Maybe OUP had it completely re-typeset in some other system without telling him. There are at least a dozen typographic faults in one paragraph alone, from unnecessary hyphenation to excessive word-spacing to bad math spacing, and LaTeX simply doesn't make those types of mistake unless you work very hard to introduce them manually.

As a test I screenshot a random paragraph [silmaril.ie] that I viewed in Amazon's "Look Inside" feature, and then retyped it in LaTeX [silmaril.ie] and typeset it (PDF [silmaril.ie]).

As I don't have the book (and wouldn't understand it anyway :-) I'd be interested to know where the information came from that it was typeset with LaTeX; and if it really was done in LaTeX, I'd love to know WTF kind of style files, fonts, and preamble were used.

• #### Analysis and visualization (Score:3, Interesting)

<wagnerr@umich.edu> on Wednesday October 01, 2008 @04:40PM (#25224433) Homepage

Maybe some of the people yelling about how Excel is the wrong tool can give some advice for my scientific data analysis and visualization needs.

I have simulations (written in C++ and Python) that spit out tab delimited data files. I then need to analyze that data, doing things like linear regression on subsets of the data and calculations to transform the raw data into something else for plotting.

I have a Mac (with Windows XP in Parallels), I am not a student, and I don't have much budget (\$500) for software. Currently I use a Mac program called Plot which is a little buggy and incomplete but has some nice plotting abilities. When I need a spreadsheet I use Apple's Numbers, but that seems sorely limited in abilities. What's a better tool for this job?

#### Related LinksTop of the: day, week, month.

The difficult we do today; the impossible takes a little longer.

Working...