Customer Reviews

Advanced Excel for Scientific Data Analysis

5 star:		(8)
4 star:		(5)
3 star:		(2)
2 star:		(1)
1 star:		(2)

 

 

Advanced Excel does very well what it does, so your main concern is whether what it does interests you. The book is intended for engineers and scientists who do real computation, not intended for those making turnkey applications for businesses.

Three chapters describe the use of Excel for least squares fitting. Treatment is authoritative, including things like phantom relations, orthogonal polynomials, fitting to a Lorentzian, finding the derivative of data, and so forth. Although there is a lot of detail, it is well presented, and you will be able to follow without being an expert yourself. Less extensive but still detailed are chapters on Fourier analysis and on convolution and deconvolution. A brief introduction to numerical integration of ordinary differential equations is exactly that, introductory. Tons of references to other literature are provided.

So, if you have a specialized interest in these topics, this book is a must. What else is here?

Approximately the last half of the book is devoted to writing macros, and to a presentation of macros used in the first half of the book. The publisher maintains a web site where these can be downloaded, saving you the tedium and error of typing them into your computer from the book. The approach is to use message boxes to communicate with computation in VBA. VBA is used primarily as a programming language, and there is rather little about the Excel object model. You will learn very little about worksheet manipulation using VBA.

The reader with less interest in the applications, but an interest in applying Excel to their own problems, will also find a lot of interesting details here. The author knows a lot about Excel, and you will pick up not only the big picture, but also many useful details. For example, how to call Solver from a macro. How to line your charts up with the spreadsheet grid. How to make the most of Excel's graphic abilities.

This book is NOT the typical Excel book full of screen shots and low on content. It teaches by example. By going through the examples presented, you really will learn how to use Excel for your application too.

If I had written this book I think I would have called it Scientific Excel rather than Advanced Excel. To be sure, the book is certainly for advanced Excel users, but it won't help you do an advanced business application.

You'd best have some knowledge about Excel before starting this one. There's a brief survey of Excel at the beginning that starts off comparing a spreadsheet to an accountant's ledger. That's pretty basic. Anyone with any Excel experience at all can follow the first three pages. On page four he is talking about making a thousand point plot with random numbers, normal distribution -- no longer something from Excel for Dummies. By page 5 he's calculating averages and standard deviations. By the end of this Survey chapter he's talking about the accuracy of the calculations performed by Excel.

Subsequent chapters discuss various types of mathematical manipulation that are often needed in the analysis of scientific data.

There are three chapters on Least Squares. This is the fitting of a curve to collected data so that the trends might be more easily visualized.

There is a chapter on Fourier Transformations, which is the probably the most frequently used analysis tool when working in signal processing. Geophysical seismic data, radar receivers, cell phone systems are all processed primarily using Fourier Transforms. This kind of data is of course too voluminous for Excel, but the techniques used here would be ideal for quite a number of laboratory applications.

A couple of chapters cover convolution, deconvolution, and time-frequency analysis as well as Numerical integration of ordinary differential equations.

All of these processing tasks are done using macros. These are described in the book, or can be downloaded from the author's website -- www.bowdoin.edu/~rdelevie/excellaneous/. This web site also includes some additional macros that enhance Excel's computationability when handling numbers of higher precision.

The final four chapters of the book are on writing your own or modifying existing macros, with an orientation to scientific analysis.

I consider this to be almost a mandatory book for anyone interested in using Excel to analysis scientific data.

This book by Robert de Levie is a thorough and comprehensive how-to guide to the use of the Excel program on common numerical tasks in physical science. It starts with a chapter that surveys the capabilities of the Excel program itself. It then continues with three chapters of progressively increasing sophistication on the method of least squares, followed by single chapters on Fourier transformation, convolution and deconvolution, and numerical solution of differential equations. The final four chapters are given over to the writing of macros and the author's presentation of the many macros he has developed in the course of solving the problems illustrated in the book. Readers should be aware that all of these macros, as well as the numerical data used in many of the examples, are also available in computer-readable form from the publisher's web site and, in fact, are available to purchasers and nonpurchasers alike.

I should acknowledge at the outset that I am very much NOT a fan of Excel. However, the program is by now so firmly established that there is little doubt of the value of the contents of this book to many in the intended audience of scientists and engineers. Moreover, there is also plenty of value for those of us who prefer to use computational tools other than Excel. Since my own primary interests relative to this book fall within the chapters on least-squares methods, that is where I will direct my specific comments.

As already noted, the book is about computations, not about theory, so although key working equations are often presented, they are seldom derived. Thus a beginner wanting to understand the method of least squares might want to consult another source to complement the "nuts and bolts" provided by the examples illustrated here.

Chapter 2 is devoted to the simplest of least-squares (LS) problems, unweighted fitting to a straight line (including one forced to go through the origin). This chapter also introduces the important topic of propagation of error (called propagation of imprecision by the author in an attempt to improve the terminology). A number of common applications are considered, the most important of which is probably the role of linear LS in calibration in analytical chemistry. This is, incidentally, an application where the common textbook expressions for error propagation lead to incorrect estimates of the imprecision; but de Levie "does it right."

Chapter 3 continues with linear LS, but now involving fitting to functions more complex than a straight line and often involving three or more adjustable parameters. (Note that the "linear" in linear LS refers to the manner in which the adjustable parameters occur in the fit function, not to the shape of the function itself; some authors refer to this as "multilinear.") The coverage begins with fitting to polynomials and is later extended to orthogonal polynomials. Toward the end of the chapter, weighted LS is introduced; this is needed to deal with the problem of transforming nonlinear fit relationships into linear ones, like exponentials (log transformation) and hyperbolic relationships (reciprocal transformation). Most of the examples in this chapter are from analytical and physical chemistry and are often encountered in the chemical teaching literature. These include the analysis of diatomic spectroscopic data (I2 and HCl), the analytical problem of estimating species abundances from UV-visible spectra of mixtures, and the treatment of enzyme kinetics data.

Chapter 4 turns to nonlinear LS, in which iterative methods are needed to obtain the solutions to the minimization problem at the heart of LS. The tool for accomplishing this task in Excel is the Solver routine. Solver has one glaring limitation, namely the failure to provide the statistical errors in the adjustable parameters. De Levie has solved that problem with his own macro, SolverAid. The capabilities of these routines are illustrated on a number of examples, again mostly from the realm of analytical chemistry and spectroscopy. Among the more unusual examples are fits of titration data, of discontinuous functions, and of continuous functions taken piecewise. Toward the end are included some illustrations of the performance of Solver on some benchmark nonlinear fitting problems provided by NIST (National Institute for Science and Technology).

I have personally checked many of the examples illustrated in these three chapters using other methods, and I can vouch for their general validity. In a few cases there are errors, but many of these have been corrected by the author since the first printing of the book. Users should consult the publisher's web site for a listing of these.

In summary, this work will prove a valuable addition to the bookshelves of Excel-oriented "number-crunchers." For those who prefer programs other than Excel, the examples can still provide useful instruction. For this group, the Excel material is of no use but also no real impediment. For those who hope to learn both data analysis and Excel at the same time, from "scratch," I doubt that this book will fill the bill: You'll probably need to start with more elementary treatises in both areas. I must admit that my aversion to the Excel program itself and its heavy focus in this book is what prevents me from giving the book the maximum rating.

This book is informative and shows some clever tricks. I have only one substantive criticism, but it's a doozie: this book encourages the use of Excel for serious data analysis. But doing this kind of analysis in Excel/VBA is slow, computationally inaccurate, and very prone to both human and computer error. To his credit, the author does describe some of these issues.

However, free tools like R and Octave and proprietary tools like Matlab and SAS will provide more accurate results and tremendously better reproducibility with less complex code. Excel is almost never the right tool for the job when it comes to scientific data analysis.

This is a remarkable book, a really advanced book on Excel, which illustrates through a wide variety of examples the extraordinary power of this modern "spreadsheet" software when exploited by a really knowledgeable user. The author is clearly an expert on spreadsheet techniques - witness his previous publications "Spreadsheet Workbook of Quantitative Chemical Analysis" and "How to Use Excel in Analytical Chemistry".
(...) In my opinion it will be mostly appreciated by postgraduate students and professionals, who will find that they can make even extremely complicated analyses of their data with full statistical cover very easily using the friendly environment of the Excel spreadsheets. (...) Therefore we can examine the accuracy and reproducibility of our data, the effectiveness of the method we use to analyse them and estimate the impact of the various errors on the final results. This is what the author almost emphatically tries to teach along with the correct application of statistics.
The great capabilities of Excel are further enhanced by the use of macros, i.e. by programming Excel to perform certain actions. (...) Moreover, it is didactic and the average reader very soon will be able to write his own macros or modify the macros of this book to suit to his interests.
As pointed out above, the capabilities and features of Excel are mainly illustrated via a wide variety of examples, which demonstrate the use of the programme for simulation of an experimental system as well as for analysis and presentation of experimental results. Most of the examples are accompanied with an extensive introduction that clarifies its physical content, quite useful since the readers may be from different scientific fields. In addition, the statistical and mathematical background at each chapter is, with a few exceptions, very good.
The book comprises 11 chapters. Chapter 1 is an introduction to Excel, although it is addressed to those who use and are familiar with Excel. It starts with a general description of spreadsheets and continues with the Excel capabilities for making 2-D and 3-D graphs. Next the complete exploitation of Excel via built-in functions, the various add-ins, custom functions and macros is extensively discussed. Finally, the use of complex numbers and matrices, the accuracy of calculations and the possibility of obtaining erroneous results are also shown. It is a useful chapter because it sums up and refreshes all the basics needed for an effective use of Excel.
Chapters 2 and 3 describe the application of the linear least squares technique starting from the simple fitting of data to a proportionality and then extending to polynomial and multivariate fittings. These methods are so easily and widely used that one can hardly be aware of the possibilities of misapplications yielding quite misleading results. The book tries to focus our attention on the correct application of the least squares technique, which means the correct selection of the dependent and independent variable, the correct selection of the adjustable parameters by means of statistical criteria and the treatment of these parameters as mutually dependent. I was impressed by the simple exercise 2.14, which shows that even the correct application of statistics may yield erroneous results, as well as by exercise 3.19 which points out that the careless application of an advanced technique, like weighted least squares, may worsen the results.
Chapter 4 describes the use of Solver for non-linear least squares and it is, in my opinion, the most interesting and useful chapter. The extensive applications of this technique are illustrated by a great variety of examples. However, this is the strength and simultaneously the weakness of this chapter. For example, one of the most useful applications of Solver is the case where the experimental and the calculated data do not correspond to common values of the independent variable. This very interesting case is described in exercise 4.4 but since this is pointed out clearly neither in the title of session 4.4 nor in the introduction of this session, it is very likely to escape from reader's attention.
Chapters 5 and 6 deal with applications of Fourier transformation in data analysis, convolution, deconvolution and time-frequency analysis. Although entire books have been written for the Fourier transformation and its application, the themes discussed here are carefully selected and clearly presented.
The numerical integration of ordinary differential equations is described in chapter 7. It is based almost exclusively on custom functions and one might be surprised by the author's choice to start with the rather unknown Euler's methods and then pass to the most popular Runge-Kutta methods. However, this is due to the author's attitude to warn constantly the reader that routine application of maths, the Runge-Kutta method in this case, may give misleading results. The chapter is completed with examples of systems exhibiting oscillations and chaotic behaviour. I think that a few pages here or in another chapter about the differentiation and integration of data would be useful.
The next chapter, chapter 8, is tutorial for writing macros. Although the author believes that earlier knowledge of some computer language is not necessary, I very strongly doubt that such a reader can follow this well-written chapter and eventually write his own macros. In my opinion this could have happened if the author had added the very basic commands of VBA, for example like those concerning control loops and conditional statements. Thus this chapter is particularly useful and very instructive for those who are already familiar with programming.
The final three chapters describe in detail the custom macros used in this book. (...)
The chapters are arranged in a logical order and establish a satisfactory balance and conformity among them. Some of them and in particular chapters 1, 7 and 8 could be more complete by including the necessary basic material that would make it unnecessary for a novice reader to consult other sources. Another minor shortcoming is that the book is not free from annoying typographic errors, though the majority of them do not confuse the reader.
To sum up, this is a valuable help for all users of Excel, highly recommended for postgraduate students and professional researchers.

Every modern scientist and engineer relies upon some type of software for the analysis of data. Many software programs are available in the market today and each seems to have its own unique code and learning curve. In the PC world, perhaps no other software for data analysis is more common and easier to learn than Microsoft Excel. Many high school students are already using Excel for their homework assignments. All of these features make Excel an attractive analytical tool for scientists and engineers at university and afterwards. All such tools need reliable tutorials in order to train users to harness their full capabilities. Most available literature on Excel is introductory in nature, and therefore not appropriate for advanced applications. Robert de Levie's "Advanced Excel for scientific data analysis" helps fill in this void.

Prospective readers should be aware that this text is not appropriate for beginners. The author clearly alerts readers to this point in the preface. This is also readily apparent from browsing the Table of Contents. I was skeptical at first with some of the more advanced applications such as solving differential equations in Excel. Many scientists use higher-level programming languages such as Mathematica and Matlab to solve differential equations. While such software packages are quite powerful, they also have steep learning curves. I previously thought that Excel is not capable of solving differential equations, but Chapter 7 turned me into a believer.

The major emphasis of the examples is on least-squares and Fourier transformation. Chapter 2 does a nice job of contrasting Excel's three available routines for linear regression. The author does a very thorough job showing how Excel can be effectively used for Fourier transformation, and gives many examples. However, some other useful mathematical topics are either covered minimally or omitted entirely. For example, I was disappointed by the lack of a routine to calculate eigenvalues and eigenvectors. Excel's array structure makes it well-suited to linear algebra and the author should consider adding more on this topic in a future edition.

One of the greatest strengths of the book is its detailed coverage of Visual Basic for Applications (VBA). Advanced data analysis require the use of special user-defined functions, and VBA allows one to extend Excel capabilities to satisfy this need. Unfortunately, VBA code sometimes conflicts with Excel code. For example, the square root operation in Excel is SQRT, but in VBA is SQR. While the author certainly has no control over this, he does an excellent job alerting the reader to these pitfalls.

Chemists definitely need a reliable tool for the analysis of experimental data. de Levie's book covers most of the techniques we use in our lab. The book clearly demonstrates how Excel is not just a convenient tool for plotting data from the stock market or keeping track of students' grades, but a powerful tool for scientific data analysis. This book is highly rercommended for all students and research workers in the areas of analytical and physical chemistry.

I bought this book for three reasons: (1) we were considering using Excel to analyse scientific data; (2) the fact that the Slashdot review at
[...]
said it was beautifully typeset with LaTeX; and (3) it is published by Oxford University Press, who have an excellent reputation.

On point (1): yes, the book does some amazing things with Excel. However, when I see what it takes to get Excel to do what you want, and given the availability of free tools like R and Octave, it's now clear to me that it does not make sense to use Excel for scientific data analysis.

On point (2): no, the book was not typeset at all. It was word-processed. At the end of the preface, it states: "This book was printed electronically from copy made on a standard personal computer. All text was written in Word, all figures [...] were made with Excel." It looks terrible, not at all like Numerical Recipes, which the author cites on the same page, and which was properly typeset in TeX. This book looks so bad, I can't read more than two pages without getting a headache.

On point (3): Come on, OUP, you can do a lot better than this (and have in the past).

This book is indeed advanced material for Excel, and it does its job well. But you can forget about Excel 2008 for Macintosh for applying what is there. It is just about useless for anything other than playing around with with just a few numbers. Turns out that it's a known bug. The wondrous thing is that the Microsoft Mac BU knows about this--and has known for some time--but hasn't bothered to let users know.

You can only plot about 100 points before it gets totally bogged down. I tried plotting 5000 points, no formulas, very clean data set, and it essentially locked up--the beach ball twirled forever. BTW, Numbers can't do any more than 1000 lines.

So, get this book if you're serious about Excel calcs, but stick with PCs at this point.

Seems like OpenOffice does better, but it still feels anemic.

Best computer book I've purchased in a long time, maybe ever. Although an advanced book, he provides all the little steps, saving you those infuriating random walks through Excel's hopeless menu structure. This is the book you should read if you want to crunch numbers with Excel, this is the book experts should read before they write a computer instruction book. And his Macro downloads really work and are documented and kept up to date. This book is clearly a labor of love, and is what he does, he's a Chemist with a strong quantitative orientation. This is not a 90 day knockoff by a hack writer.

A couple notes may be in order.
1. He clearly prefers Excel 2003 and is quite emphatic that upgrading to 2007 is unnecessary and possibly undesirable, unless you have to. (2007 represents a syntax break from earlier versions, and the menus are even worse.)
2. He asserts that with his Macros and VBA Excel is a powerful general purpose language for data processing. He is also clear however that for REAL number crunching (Quantum Mechanics, Cosmology etc.) it will be way too slow, and you should use something heavier duty or a compiled language. For myself I intend to do everything I can on Excel (with his help) in the future, and upload data to Mathematica only when absolutely necessary.

It's a good enough book, but the target audience would be better off learning one of the programs that's actually dedicated to scientific data analysis instead of wading through pointless complexity to try to make Excel suitable for the job.