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!

Advanced Excel for Scientific Data Analysis

samzenpus posted about 6 years ago | from the read-all-about-it dept.

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.

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.

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

Why All Bailout Packages Will Fail (-1, Offtopic)

Anonymous Coward | about 6 years ago | (#25221079)

I've been thinking about this for a while, but I think I understand why the majority of the American people are against the current bailout package, and why ultimately all bailouts (whether for banks, mortgage holders, or taxpayers in general) will fail.

The root of the problem is not economic but sociological in nature. No amount of economic carrots will fix the problem, despite the fact they may delay the crash or draw it out into a painstakingly long-term affair. Individidual productivity has dropped off as people have become depressed with their jobs, which seem to absorb ever increasing amounts of their time and effort.

Americans have become disillusioned with going after material at the expense of building relationships. People are addicted to their jobs and have delayed finding a partner of the opposite sex, getting married, and having children. Their job brings them immediate stability and shiny things; however, they know their long-term stability is at risk.

I think a majority of the population would appreciate a breather from the corporate world, and if there are large swaths of people in the same position, that gives them ample chance to meet with other people who have plenty of spare time in order to build relationships that cannot be built barhopping on Friday nights.

Maybe I'm projecting my thoughts too much on the rest of the populance, but I know very few people my age (~30 years old) who have even started building a family, and that's quite frankly, distressing. The economy isn't everything. The crash will happen. We will survive. Let it happen swiftly and let the recovery happen in the near term.

Re:Why All Bailout Packages Will Fail (1)

dzfoo (772245) | about 6 years ago | (#25221945)

So what you are saying is that Excel should be banned from the Congress?

        -dZ.

alternately.... (5, Insightful)

mattdm (1931) | about 6 years ago | (#25221095)

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

Re:alternately.... kind of begs the question... (-1)

Anonymous Coward | about 6 years ago | (#25221915)

Why hasn't anybody come out with anything in between?

LaTeX is a chore, Mathematica requires a PHD, and Excel is retarded at best. Are these the 3 choices we get in 2008?

There is OBVIOUSLY a reasonably large market for something simpler then LaTeX and Mathematica in a lower price point, yet with more function and transparency (and accuracy?) than excel could ever offer.

Maybe an "output to excel" function to satisfy the plebes? I dunno. I just keep seeing this fight on /. and wondering why each time.

Re:alternately.... kind of begs the question... (1)

blueg3 (192743) | about 6 years ago | (#25221979)

For what? The three items you discuss do different things.

Presumably nobody uses LaTeX for making charts or for performing calculations (the latter of which is really what's being addressed here).

Re:alternately.... kind of begs the question... (1)

stranger_to_himself (1132241) | about 6 years ago | (#25222235)

Presumably nobody uses LaTeX for making charts or for performing calculations (the latter of which is really what's being addressed here).

The PSTricks [tug.org] package can make some really beautiful charts in LaTeX documents.

There's a book called Tex Unbound that gives lots of other ways to make charts in TeX/LaTeX that are way better than Excel output (or most other graphing packages IMO).

Re:alternately.... kind of begs the question... (4, Informative)

MightyYar (622222) | about 6 years ago | (#25222107)

What in the world are you talking about? :)

LaTeX is a markup language. You can express math with it, but it doesn't do anything for you in terms of analysis.

Excel is good for small data sets and quick looks at stuff - but painful to develop in.

Mathematica requires college-level calculus and linear algebra... not PhD stuff by any stretch.

Anyway, you left out Matlab - which is pretty awesome. Depending on what you are doing, there is also R, Maple, Minitab, MathCAD, yada, yada, yada. Lately I've been doing stuff in Python... SAGE is pretty nifty, and the NumPy/SciPy stuff is coming along well (it is included in SAGE).

Re:alternately.... kind of begs the question... (3, Informative)

jank1887 (815982) | about 6 years ago | (#25222245)

don't forget Octave [gnu.org] ! (it's more-or-less a FOSS Matlab clone, and follows more closely to Matlab syntax than SciLab)

...there's a better solution (4, Interesting)

mangu (126918) | about 6 years ago | (#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] .

This was the problem (0, Funny)

Anonymous Coward | about 6 years ago | (#25221097)

The whole financial mess was due to the use of Excel? No wander!

Bad math (3, Funny)

kwabbles (259554) | about 6 years ago | (#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:Bad math (0)

Anonymous Coward | about 6 years ago | (#25221567)

You were modded funny, but many of the quants at the BoA create their mathematical models in Excel VBA (these are quants with Ph.Ds in physics). These get handed over to the software dev team to be recoded in some other language like Java or C#.

Some other places have had some success persuading their quants to use better tools like MATLAB, but Excel is still one of the primary tools used for modeling owing to its universal availability and folks' familiarity with it.

It boggles the mind, I know.

incongruous (5, Funny)

drfireman (101623) | about 6 years ago | (#25221149)

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

fbjon (692006) | about 6 years ago | (#25221167)

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

Re:incongruous (4, Interesting)

Mr. Underbridge (666784) | about 6 years ago | (#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 (1)

weston (16146) | about 6 years ago | (#25221815)

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 scripting engine. Not particularly natural for most imperative programmers, but definitely possible.

So I don't think loops are the issue. My understanding is that the biggest problem with using Excel for detailed number crunching is in how they handle some precision/float issues.

Re:Loops in spreadsheets (2, Interesting)

MightyYar (622222) | about 6 years ago | (#25222175)

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

mangu (126918) | about 6 years ago | (#25222277)

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

I need infinite loops, you insensitive clod!

Re:incongruous (2, Interesting)

cyphercell (843398) | about 6 years ago | (#25221751)

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 testing a piece of data and figuring out what you want it to look like in a database, but they do not scale well for many types of projects. My rule of thumb is that any given portion of a successful spreadsheet should be limited to about five. Five inputs, five outputs, or five calculations. So you can have five inputs, 20 calculations. 20 inputs and five calculations etc. Otherwise the debugging process will consume your project.

Re:incongruous ...I KNEW there was a reason (1)

davidsyes (765062) | about 6 years ago | (#25221877)

all these years i referred to ms excel as "HEXEDcell"...

Others referred to ms office as ms orifice.

Now, if OO.o fails to at least equal mso, and if OO.o crashes Wall Street 2.0, would we call OO.o "OpenOrifice", in the name of "fairness"?

Re:incongruous (1)

syphax (189065) | about 6 years ago | (#25222039)

- 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 best tool for the job.

BTW last I checked, OO had a workable link with R. I think it was a Summer of Code project; I don't know the current status.

Re:incongruous (2, Interesting)

fiannaFailMan (702447) | about 6 years ago | (#25221271)

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:incongruous (2, Interesting)

johnny cashed (590023) | about 6 years ago | (#25221315)

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

fiannaFailMan (702447) | about 6 years ago | (#25221399)

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

How long is a 'while?'

Re:incongruous (2, Insightful)

johnny cashed (590023) | about 6 years ago | (#25221433)

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

Murple the Purple (130813) | about 6 years ago | (#25221619)

Beating on ExcelDo you check your math utility? Or do you just assume your compiler/vendor does everything correctly?

Re:incongruous (1)

MightyYar (622222) | about 6 years ago | (#25222233)

I certainly run the test suite before using a new build of SciPy/NumPy - but I'm largely dependent on the developers.

Still, in Excel I've caught errors and so now I usually calculate things in two different ways to try and catch stuff. For instance, fit a line using both the built-in methods and the solver. Don't use the line fit in the graphing tool - that's one error that I found.

Re:incongruous (2, Interesting)

Murple the Purple (130813) | about 6 years ago | (#25221637)

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

Re:incongruous (4, Insightful)

johnny cashed (590023) | about 6 years ago | (#25221695)

Well, I do put faith into my fortran compiler.

Re:incongruous (1)

Murple the Purple (130813) | about 6 years ago | (#25222009)

At which optimization levels?

Re:incongruous (1)

johnny cashed (590023) | about 6 years ago | (#25222215)

I use punch cards, you insensitive clod! "optimization levels" please!

Re:incongruous (2, Insightful)

TarrVetus (597895) | about 6 years ago | (#25221673)

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

bugnuts (94678) | about 6 years ago | (#25221465)

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:incongruous (0)

Anonymous Coward | about 6 years ago | (#25221481)

The difference between "meaningful scientific data analysis in Excel is not a good idea" and "meaningful scientific data analysis in Excel can't be done" is quite substantial, actually.

If you can't review so much as a single sentence correctly, why should I care about your opinions on entire books?

Re:incongruous (1)

MadMidnightBomber (894759) | about 6 years ago | (#25221701)

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

drfireman (101623) | about 6 years ago | (#25221939)

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 you're really forced to use it for serious statistics, you'd have to re-implement things using basic arithmetic operations. It's graphing capabilities, last time I checked, lacked the majority of even the most basic kinds of statistical/scientific graphs/plots. Sure, you can do this or that in Excel, and if you're willing to put in enough work you can often get what you really need out of it. But it's rarely if ever the best tool for the job of scientific data analysis.

I don't review books about reading them, and this is no exception. But I do have an informed opinion about the premise of this book (and to a lesser extent about the level of insight of a reviewer who seems, to put it mildly, easily awed). The premise that Excel is good to use for scientific data analysis is pretty deeply misguided. I'd be happy to be convinced otherwise if I were really wrong, but I can only set aside so much time for listening to arguments from nutcases (just in case one of them may have a point). I'm sure if I actually read this book, I'd learn about various useful things Excel can do that would surprise and impress me. But I already have all the information I need to form a reliable opinion on this question, and I value my time too much to read books about space aliens living among us or about doing analysis in Excel.

Truth to tell, I use openoffice calc (more or less an Excel clone) quite a bit for research-related things. But I'm careful with it, and don't rely on it for much more than moving numbers around.

Re:incongruous (1)

91degrees (207121) | about 6 years ago | (#25221691)

Well.. If you happen to have a copy of Excel, know how to use it and have the right sort of data, it can be perfectly adequate. I use it a lot because I tend to have it installed (no idea why MS Office is seen as a requirement for development PCs), and I tend to only want to look at really simple data sets where I just want to plot a graph.

If you need to do something sufficiently complex that you feel you need to invest the time to read this book, I can't help feeling that learning to use Matlab or something might be a better use of the time.

Re:incongruous (1)

blueg3 (192743) | about 6 years ago | (#25222071)

That doesn't really sound like you're doing significant scientific data analysis.

Anonymous Coward (0)

Anonymous Coward | about 6 years ago | (#25221171)

...but will it blend?

Wrong Tool (5, Interesting)

Hatta (162192) | about 6 years ago | (#25221253)

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

Re:Wrong Tool (2, Informative)

treeves (963993) | about 6 years ago | (#25221431)

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

R : script support (0)

Anonymous Coward | about 6 years ago | (#25221439)

Does R have hooks for Perl or some other scripting language?

I have test equipment that spews data and I need to load it via a script. Excel is quite suited for this.

Re:R : script support (1)

compro01 (777531) | about 6 years ago | (#25221507)

R-Perl may fit your needs.

http://www.omegahat.org/RSPerl/ [omegahat.org]

Re:R : script support (1)

lbbros (900904) | about 6 years ago | (#25221725)

There is also RPy [sourceforge.net] for those who (like me) program in Python.

Re:R : script support (1)

retchdog (1319261) | about 6 years ago | (#25221781)

If you're willing to spend hours setting it up, and getting it to "sort of" work with touch-ups required every few days. Maybe I'm a dolt, but I never got any of the embedded-R interfaces to work satisfactorily. The documentation was always just too out-dated, and there were too many surprises and inconsistencies. By the time I worked them out, it would have been easier to do it another way.

If you're in a unix environment, I suggest looking at littleR [vanderbilt.edu] which makes the R libraries usable in unix "piping" style. Useful for batch processing, but of course not good for real-time.

In windows, you're going to have a hard time with either of these. That RExcel mentioned above looks interesting, even though it offends my sensibilities. ;-)

Re:R : script support (1)

Reality Master 201 (578873) | about 6 years ago | (#25221683)

Yes, another person mentioned the perl module.
There's also RPy [sourceforge.net] , a python interface. Works pretty well.

Re:Wrong Tool (0)

Anonymous Coward | about 6 years ago | (#25221561)

What is this Arrrrrrrr, and where can I pirate it from?

Re:Wrong Tool (0)

Anonymous Coward | about 6 years ago | (#25221887)

It's free software.

Joke understood, but ignored.

Re:Wrong Tool (2, Insightful)

internerdj (1319281) | about 6 years ago | (#25221675)

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:Wrong Tool (1)

ykardia (645087) | about 6 years ago | (#25222177)

Amen to that. [burns-stat.com]

eh? (4, Insightful)

Anonymous Coward | about 6 years ago | (#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? (0)

Anonymous Coward | about 6 years ago | (#25221385)

Perhaps because you work in an environment where they have a corporate culture of expecting results in an Excel spreadsheet?

Surely we can admire someone for mastering a tool to the point where they can do extremely complex operations using that tool then writing a book on how to learn to do the same thing? If you can learn to do this in Excel and if Mathematica is a superior product then surely learning how to do this in Excel will only make it easier in Mathematica?

I admire the folks that do incredible paintings in MS Paint, even if I have no desire to learn how to do so (for instance this: http://diamonster.deviantart.com/art/powerdraw-17908194 ) I don't see how this differs really. It might not be the perfect tool for the job but its great to see someone who has figured out how to bypass its limitations and use it anyways.

Re:eh? (1)

retchdog (1319261) | about 6 years ago | (#25221893)

They are wasting their time on something enjoyable; mspaint "masterpieces" are comparable to playing WoW or doing macrame (all 3 of these things are great, don't get me wrong).

On the other hand, it's disgusting to have so much productivity and economy wasted on shoe-horning the godawful Excel into pretending intelligence. Not to mention how error-prone it is; a mistake in MSPaint is mildly annoying; a rounding error or outright bogus value in science is close to lying.

Re:eh? (1)

Bill, Shooter of Bul (629286) | about 6 years ago | (#25221951)

The money and availability arguments posted below are better. You can always write up a simple conversion script to convert from the data crunching program to excel. And if you are doing serious number crunching parsing a file to csv isn't much of a task. Learning how to do something in excel will not make it easier in Mathematica. Its always easier to do any kind of mathematical operation in Mathematica.

Now if you have a crazy person that needs to twiddle some numbers and watch the out put in excel.. then you have a point. Haven't read the book, but there are some third party plugins that turn excel into something like a data cruncher. TK Solver [us.com] includes an excel plugin that give it its capabilities.

Re:eh? (4, Insightful)

goofballs (585077) | about 6 years ago | (#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? (5, Informative)

gardyloo (512791) | about 6 years ago | (#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:eh? (2, Informative)

gardyloo (512791) | about 6 years ago | (#25221789)

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

Re:eh? (1)

goofballs (585077) | about 6 years ago | (#25222077)

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:eh? (1)

Vertigo Acid (1164963) | about 6 years ago | (#25222139)

Except Office Home and Student is just a product title, not a special student-only version.

Re:eh? (0)

Anonymous Coward | about 6 years ago | (#25221809)

"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

1. http://reference.wolfram.com/mathematica/ref/format/XLS.html
2. http://reference.wolfram.com/mathematica/ref/format/XLS.html
3. student edition of mathematica is $139

thanks for your input d00d

Re:eh? (1)

goofballs (585077) | about 6 years ago | (#25222223)

  1. interact "directly" with the excel data, as in manipulate it in excel so you can use other excel functions
  2. exporting does not allow the person with whom you gave the results to work with the data with the same functions that you used
  3. mathematica student version is only licensed to students; office home and student can be bought by non students w/out violating the terms of the licensing agreement. if you need it for commercial use, you can buy the standard version for $240.

no no, that YOU d0000d.

Re:eh? (0)

Anonymous Coward | about 6 years ago | (#25221531)

Because then you don't need their book?

Re:eh? (2, Insightful)

The Dancing Panda (1321121) | about 6 years ago | (#25221573)

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

Re:eh? (2)

jpmorgan (517966) | about 6 years ago | (#25221681)

$120? You must be joking. A professional license of Mathematica is more like $2500.

Re:eh? (1)

goofballs (585077) | about 6 years ago | (#25221705)

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

melted (227442) | about 6 years ago | (#25221291)

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:I have not read the book (1)

PatDev (1344467) | about 6 years ago | (#25221395)

Is this true? If so, I'd like to take a look. I'd probably still use LaTeX, as I've already invested the learning curve, but it could be interesting.

But then, remember that beautiful maths are just a feature of LaTeX, not the main focus. It's a document processor, which is nice in that someone like me with little aesthetic taste can simply specify the *content* of my paper and let LaTeX worry about the layout. And maybe you can write a paper in word that looks as good, but I know I've never been able to.

Re:I have not read the book (2, Informative)

Forbman (794277) | about 6 years ago | (#25221559)

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

Re:I have not read the book (2, Informative)

slashdotlurker (1113853) | about 6 years ago | (#25221645)

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:I have not read the book (2, Interesting)

solafide (845228) | about 6 years ago | (#25221971)

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

networkconsultant (1224452) | about 6 years ago | (#25222005)

Mine's still gryed out!

Re:I have not read the book (4, Insightful)

backwardMechanic (959818) | about 6 years ago | (#25222047)

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

Daishiman (698845) | about 6 years ago | (#25221305)

Someone should tell this guy about SAGE http://www.sagemath.org/ [sagemath.org]

ROOT (1)

phizix (1143711) | about 6 years ago | (#25221363)

I would urge anyone attempting significant data analysis to try a dedicated analysis software package such as ROOT [root.cern.ch] . ROOT has much more support for data trees, histograms, functions, fitting, etc., and ROOT now also has a Python interface.

Spreadsheets are the right tool ... (4, Funny)

MacTO (1161105) | about 6 years ago | (#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:Spreadsheets are the right tool ... (0)

Anonymous Coward | about 6 years ago | (#25221621)

Prof. Smith, is that you?

Re:Spreadsheets are the right tool ... (1)

jhfry (829244) | about 6 years ago | (#25221905)

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

MarkusQ (450076) | about 6 years ago | (#25221483)

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

Vornzog (409419) | about 6 years ago | (#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:When all you have is a hammer... (1)

pla (258480) | about 6 years ago | (#25221919)

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 I want, however I want it done. I can graph it any way I like, save the data to any format I like ("Hmm, I wonder what that function sounds like... Okay, where'd I put that RIFF header code?"), and I can implement ways to massage data never seen outside pretty hardcore mathematical research journals.

All of that, while undeniably "superior" in nearly every aspect to Excel, takes time to implement - Potentially quite a lot of it.

And then we have Excel... I can take list of raw numbers in most common list- or table-like formats, paste it in, and run a polynomial regression on it in 30 seconds flat, with another minute or two to graph it in 99% of the ways I would ever think of using. I can prettify the data for showing PHBs with a half-dozen clicks. I can save my results in a format anyone can view, without having to write a specific set of routines to export this particular blob of data to format-X. I can trivially preprocess the data in a wide variety of ways to see how it affects the results, and see the changes happen as I go.

In short, Excel makes it much, much easier to "play" with data than most programs designed specifically for "serious" mathematical work - In much the same way that I can do most simple image touch-ups in MS Paint faster than GIMP would even finish loading all its plugins.


Now, I sure as hell wouldn't publish (or more likely for me, sell to a client) results or methods based on a quick-n'-dirty Excel sheet... But I can try a hundred different approaches to working with the data in the time it would take to rigorously code just one of them, and then spend my actual effort (aka "the client's money") on coding up what looks like the best approach to the data.

Spreadsheets are not the right tool (3, Informative)

elite1789 (1245036) | about 6 years ago | (#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?

Excel is a horrible tool (4, Insightful)

slashdotlurker (1113853) | about 6 years ago | (#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:Excel is a horrible tool (1)

gardyloo (512791) | about 6 years ago | (#25221873)

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:Excel is a horrible tool (2, Funny)

Anonymous Coward | about 6 years ago | (#25221909)

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

Excel does not Excel (4, Interesting)

systemeng (998953) | about 6 years ago | (#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 (2, Funny)

gmuslera (3436) | about 6 years ago | (#25221679)

Is perfectly safe and trusty for that kind of work. Thats why we are using it here at the Large Hadron Co

Excel *could* replace SPSS (not Mathematica) (3, Informative)

Dr. Spork (142693) | about 6 years ago | (#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.

excel as a flag (0)

Anonymous Coward | about 6 years ago | (#25221813)

Serious researchers do not use excel. Period. Ask scientists in any national laboratory. Excel is a toy designed for simple accounting talks. It is slow, it is limited but, most importantly, it makes error prone since it refers to variables by their position on the spreadsheet, not their name or meaning or role in the workflow of the computation. I guess it is an ok tool to teach undergrads who will never go into sciences.

Age of Excel (1)

lymond01 (314120) | about 6 years ago | (#25221843)

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.

Re:Age of Excel (0)

Anonymous Coward | about 6 years ago | (#25222185)

And assuming you're not an idiot, you would realize that your post is idiotic. What if the book was titled "Today's Date is September 30th, 2008" and the OP said it is the kind of book that only comes along every twenty years?

It does not mean that there is actually a book written in 1988 on that subject. Merely, a book of similar quality, not even necessarily on the same subject.

If you're wondering... (1)

DirtySouthAfrican (984664) | about 6 years ago | (#25221863)

..."When does exponential decay function stop?" scientific data analysis is definitely not for you. How about "How is babby formed?"

Re:If you're wondering... (1)

icebrain (944107) | about 6 years ago | (#25222013)

How about "How is babby formed?"

I know it's off-subject, but where did that come from? I've seen it in a couple places lately...

Re:If you're wondering... (0)

Anonymous Coward | about 6 years ago | (#25222181)

Google is your friend. I found this [encycloped...matica.com] .

What versions of Excel are covered? (1)

harlows_monkeys (106428) | about 6 years ago | (#25222041)

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.

Clueless Reviewer (0)

Anonymous Coward | about 6 years ago | (#25222161)

Its clear that the reviewer is not qualified to review this text. First, if they were familiar with scientific data analysis, they would not use Mathematica for their ridiculous comparison. Mathematica is also the wrong tool for the job. At least it does math correctly, something Excel cannot claim. [doi.org]

You haven't seen a book typeset in LaTeX recently? What scientific computing books do you read that allow you to avoid LaTeX for years?

Excel is not the right tool for the job. If you are going to put in the time to learn some new math, learning a better tool along the way makes sense.

Get a different book, and a different software package.

Anecdote about Excel (2, Funny)

Thelasko (1196535) | about 6 years ago | (#25222165)

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.

comparison of Mathematica and Excel VBA .. (5, Informative)

rs232 (849320) | about 6 years ago | (#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? (1)

exp(pi*sqrt(163)) (613870) | about 6 years ago | (#25222273)

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. What next? A review of a book on functional programming by someone whose only experience with computers is programming the VCR?
Load More Comments
Slashdot Login

Need an Account?

Forgot your password?