Excel as a High-Powered Computational Tool
Spreadsheets, such as Excel and OpenOffice Calc, have found extensive use in mathematics and the sciences for data analysis, simulations, and concept illustration at all levels; there are many examples in chemistry (1–4). Perusal of the chemistry chapter of the Mathematical Association of America’s publication Curriculum Renewal Across the First Two Years (CRAFTY) (5) reveals the recommendation that chemists should use tools such as spreadsheets to strengthen a variety of mathematically based concepts.
This review examines two books that clearly demonstrate that Excel can handle a large portion of the recommendation of CRAFTY for undergraduate education. Both books assume a good working knowledge of Excel. Since both authors are chemists, readers will not be disappointed with the numerous examples. Can Excel compete with high-powered computational software? Let’s examine this question.
Excel for Scientists and Engineers
 In Excel for Scientists and Engineers: Numerical Methods, the basics of numerical methods (roots of equations; matrices for simultaneous equations; interpolation methods; derivatives and integration; solving both ordinary and partial differential equations; linear regression and curve fitting; using Solver for nonlinear regressions) are presented using Excel in combination with Visual Basic for Applications (VBA) to write macros and a variety of custom or user-defined functions. The advantage of user-defined functions is their ability to automatically update when used on a spreadsheet if data or parameters are changed. It will help if the reader is familiar with VBA; however, all the macros and user-defined functions are provided on a CD that comes with the book. Readers can get the spreadsheet examples to work without writing any code.
From this reviewer’s experience, many of the methods presented in the book can be set up on spreadsheets so that students just add data; the analysis is performed in the background and then students can interpret the results. One can easily compare Euler’s method with Runge–Kutta 2 and Runge–Kutta 4 plus throw in the time step to compare methods and the resulting errors. This will get students started using the methods, and the instructor then can decide on the amount of time to spend elaborating on the actual calculation or having students set up the calculation in a spreadsheet themselves.
Figure 1 shows a model with the growth rate of 0.245 for an initial amount of 10 that compares Euler and Runge–Kutta 4 numerical integration methods with the exact solution for two different time steps, Δt.
Figure 1. Numerical Integration of Growth Model.
Models of this type are referred to as systems models, and Excel can compete nicely with software such as Stella, Vensim, or Berkeley’s Madonna. As a bonus, the students get the feel for the recursive calculation when setting up the spreadsheet. In addition, modeling with a difference equation (as opposed to differential equations) only requires algebra and can be introduced much earlier in the curriculum.
Billo’s book is well written: it includes formulas for all spreadsheet steps, has clear syntax for any functions used, and has a vast number of screen shots. All VBA code is given and contained on the CD as well for easy loading into Excel. Having some VBA experience helps but is not required. Note that if you plan to use macros and/or user defined functions, you must load them on any computers used or set up a Web page to allow student access.
Advanced Excel for Scientific Data Analysis
 The real power of Excel combined with VBA for handling data analysis is explored to the fullest in Advanced Excel for Scientific Data Analysis. Topics include meeting statistical analysis assumptions of linear and non-linear regression with many macros to enhance statistical analysis; Fourier transformation and signal processing; matrix manipulation and the vastly extended capabilities using Volpi’s VBA functions and macros available online (6); and enhanced spreadsheet performance. All topics include a significant number of examples for readers to work on and use the macros provided. Having some VBA experience will help the reader grasp the full power this book.
The spreadsheet presentation of Fourier transformation and signal processing provide a nice pedagogical tool for students to learn what is actually going on and allow for production of some interactive spreadsheets to simulate concepts. de Levie constantly addresses errors and limitations of signal to noise. This book is definitely aimed at the very serious user, and it demonstrates the power of Excel for data analysis.
The use of Solver (7), an Excel add-in, for non-linear regressions of any equation of your choice (minimize the sum of the squared differences or error, SSE, of the experimental value minus calculated value) and a variety of other aspects, is covered including ways to be sure that the solution is correct. This means you do not need weighted linear regression for enzyme kinetics on the transformed data; simply set up the Solver for a non-linear fit where the sum of the squared error, SSE, is the target cell to be minimized and the vmax and KM are adjusted by Solver to fit v = vmax[S]/(Km + [S]) as illustrated in Figure 2.
Figure 2 Solver Parameters using named variables.
A deficiency of Solver is its inability to give an estimate of the uncertainty of the parameters estimated. However, this is handled by an included macro, SolverAid. de Levie also recommends Solver over the use of Goal Seek. Solver can be pre-set up on a spreadsheet as well and run once data have been added.
Any limitations of Excel (and yes, there are some) are discussed as well. All the VBA programs are available on de Levie’s Web site (open source) (8) including Volpi’s work. This makes access easy for students and instructors alike. de Levie also provides a wealth of references to the literature. Volpi’s work with matrices is raising the power of Excel even higher (look out MATLAB!). Watch for a third edition of de Levie’s book.
Can Excel Compete?
Can Excel compete with high-powered computational software? For many applications, the answer is yes when the user-friendly spreadsheet with its easy data entry, manipulation, and graphical display is combined with the programming capability of Visual Basic for Applications. Users beware—know your limitations and validate your spreadsheet calculations! These two books complement each other, are great additions to your software applications library, and will help you enhance your use of spreadsheets.
Literature Cited
- O’Haver, T. C. Interactive Computer Models for Analytical Chemistry Instruction may be found online (accessed Mar 2009).
- Tissue, B. M. Spreadsheet Simulations for Analytical and Physical Chemistry may be found online (accessed Mar 2009).
- Coleman, W. F. Interactive Spreadsheets is available (accessed Mar 2009).
- Sinex, S. A. Chemical Excelets: Interactive Excel Spreadsheets for General Chemistry is available online (accessed Mar 2009).
- Mathematical Association of America. Curriculum Renewal Across the First Two Years, Chapter 4: Chemistry (accessed Mar 2009).
- Download files online (accessed Mar 2009).
- Solver, produced by Frontline Systems, comes with Excel (accessed Mar 2009).
- de Levie’s Web site (accessed Mar 2009).
|
