on May 19, 2004
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.
on March 25, 2004
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.