Advanced Excel for Scientific Data Analysis (de Levie, Robert

Much of the power of Microsoft Excel lies hidden inside its tools, such as the Data Analysis Toolpak, Solver, and the ability to define special functi...
2 downloads 0 Views 66KB Size
Chemical Education Today edited by

Book & Media Reviews

Jeffrey Kovac University of Tennessee Knoxville, TN 37996-1600

Advanced Excel for Scientific Data Analysis by Robert de Levie Oxford University Press, 2004. 638 pp. ISBN 019517089X (cloth), $94.40; ISBN 0195152751 (paper), $45. reviewed by Carl Salter

Much of the power of Microsoft Excel lies hidden inside its tools, such as the Data Analysis Toolpak, Solver, and the ability to define special functions and macros. Robert de Levie brings these tools to light and displays their power in this book. As the title implies, Advanced Excel is not for the neophyte, and it is not appropriate as a textbook for a first course in data analysis or numerical methods; rather, the book is a fast-paced series of applications of Excel in the areas of least-squares fitting, Fourier transforms, and numerical integration of ordinary differential equations. For these applications de Levie has developed a suite of user-defined functions and macros; any scientist who must use Excel to perform these tasks would benefit from this book. In undergraduate analytical and physical chemistry, where Excel is frequently used, the book is an excellent resource both for its illustration of data analysis and for its lively examples, many taken from recent contributions to the chemical literature. Chapter 1 is a brief overview of Excel’s features, especially those that pertain to scientific applications, and includes an introduction to user-defined functions and macros. The advanced level of the book is immediately clear: the first real example of a user-defined function is a 16-line implementation of Lagrange interpolation. Matrices and matrix operations, often overlooked by users of Excel, are also examined. Chapters 2 and 3 develop methods for linear least-squares, including techniques for weighted least-squares and the use of orthogonal polynomials. In Chapter 4 Solver is used to implement nonlinear least-squares fitting, with a wide variety of examples that include black-body radiation, kinetics, and fits of spectra to multiple curves. de Levie’s macro SolverAid (1) provides error estimates for fits performed by Solver. Chapter 5 illustrates macros for forward and backward Fourier transformation and includes applications such as filtering and differentiation. Chapter 6 illustrates signal convolution and deconvolution; it begins with a small example of RC filtering that does not require a macro and then proceeds to large data sets where de Levie’s Convolve macro is necessary. Chapter 7 illustrates the use of user-defined functions to solve ordinary differential equations by both Euler and Runge–Kutta methods; the examples are from chemical kinetics: the two-step sequential first-order reaction A → B → C and the Lotka oscillator.

www.JCE.DivCHED.org



For some, Chapter 8 may be the most valuable part of the book where “Write Your Own Macros” takes the reader through the essential moves that are required to create a spreadsheet macro using Visual Basic for Applications. One crucial example, well explained by de Levie, shows how to treat a block of cells in the spreadsheet as an array in VBA, and how to perform calculations on the array using nested loops. The chapter also includes six “case studies” of macro development, including interval bisection and Fourier transforms. Obviously Excel macros are integral to this book. Instructions are supplied (for both PC and Mac versions of Excel) to get a data set into the right form so that one of de Levie’s macros can do its work. To get maximum use of the book, the reader needs to sit in front of a computer with the macros at the ready—without the macros, the reader has little more than a list of potential applications. The macros are listed in Chapters 9–11 for those who wish to study them or type them into Excel; fortunately the macros and the example data sets can also be downloaded from the publisher’s Web site, so that the reader can get to work quickly. Because the text consists largely of examples and how-to instructions, there is generally only a bare-bones discussion of theory; fortunately the book is well referenced. While some of the examples have immediate application in analytical and physical chemistry, the high density of examples may leave some readers wondering which ones have the broadest utility. The book is organized by application rather than by Excel tools, which lends an air of “can do” practicality to the exposition; however, as a result there isn’t the complete discussion of the capabilities of Excel’s tools that one might expect. For example, the Fourier transform routine within the Data Analysis Toolpak is mentioned disparagingly but never illustrated. Solver’s capability for simple root-finding is not explored. The book assumes the use of Excel, and doesn’t compare implementations of data analysis methods in Excel to those in other programs. Would a student be better off performing nonlinear least-squares in Excel or Kaleidagraph or R or Origin? Should a research chemist choose Excel or Mathematica to solve differential equations? de Levie makes no recommendations. Nevertheless, the book does just what the author promises: it takes the reader “beyond the standard fare of Excel” and into a world of sophisticated applications that many scientific users of Excel should explore. Literature Cited 1. de Levie, R. J. Chem. Educ. 1999, 76, 1594.

Carl Salter is in the Department of Chemistry, Moravian College, 1200 Main Street, Bethlehem, PA 18018; [email protected]

Vol. 83 No. 1 January 2006



Journal of Chemical Education

43