Advanced Excel for Scientific Data Analysis 303
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.
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.
| 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 |
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.
Wrong Tool (Score:5, Interesting)
Talk about the wrong tool for the job. If you need to do any sort of serious data analysis, use R, not Excel.
Re:incongruous (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?
I have not read the book (Score:3, Interesting)
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:incongruous (Score:3, Interesting)
This problem was sorted out? (Score:3, Interesting)
Re:incongruous (Score:5, Interesting)
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.
Excel does not Excel (Score:4, Interesting)
Re:incongruous (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:3, Interesting)
Re:I have not read the book (Score:3, Interesting)
...there's a better solution (Score:5, Interesting)
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:Loops in spreadsheets (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.
Re:Spreadsheets are not the right tool (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 dollars, and 90% Linux, 10% Windows if it costs more than a million (and there's a transition zone in there somewhere, but my experience is mostly with $100k and $100MM equipment). CERN, for instance, is mostly controlled by non-Windows, but a lot of the laptops lying around there, presumably because people are going to do analysis on them, are Windows. Argonne is a healthy mix of Linux for controls and Windows for analysis. At MIT and Princeton, a couple professors use a Mac, a couple use Linux, most use Windows for personal use. Nearly everyone running stat mech simulations uses Linux for those, but a lot of those people have a personal Linux box.
I'm not going to say that it's good, but I will say that it's true.
I must disagree (Score:3, Interesting)
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 didn't show the output of these calculations.
The US is not alone in using Excel for things it wasn't meant to do. Now please excuse me while I run a couple monte carlo simulations in Excel.
Re:Excel is a horrible tool (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.
Analysis and visualization (Score:3, Interesting)
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?