Information • Textbooks • Media • Resources edited by
computer bulletin board
Steven D. Gammon University of Idaho Moscow, ID 83844
Nonlinear Curve Fitting Using Spreadsheets Juan O. Machuca-Herrera Departamento De Química Inorgânica, Instituto de Química Universidade Federal do Rio de Janeiro Cidade Universitaria–Ilha do Fundão, 21949-200 Rio de Janeiro, Brasil
The use of spreadsheets in chemistry is common, mainly in analytical and physical chemistry, where they are used to calculate systems of linear equations, nonlinear equations using the iterative Newton–Raphson method, linear least squares regressions, etc. All these applications are well described in articles (1–5) and books (6, 7). Besides calculational facilities, commercial spreadsheets also have advanced numerical tools packages that allow sophisticated calculations in multivariate statistics, as well as linear and nonlinear optimizations, Fourier transforms, and much more. These packages often contain graphical facilities that allow 2D and 3D graphics of functions and discrete point sets. However, finding these tools is not easy, because they are usually not readily apparent from the documentation accompanying the programs. The goal of this article is to emphasize the capabilities of spreadsheets in solving specific problems common in chemistry, such as nonlinear curve fitting. Nonlinear least squares fitting is used, for example, in deconvolutions of overlapping bands in vibrational or electronic spectra and in analysis of chemical kinetics. Frequently, fits of nonlinear equations are done using the Gauss–Newton or Marquardt methods (8–10). However in this work we use the quasi-Newton method (11). The method for nonlinear curve fitting presented here is simple and any undergraduate student, with some knowledge of spreadsheets, can easily program any of the examples shown. The principal difference between this work and other articles (5, 10) on this subject is that in previous articles macro structures are necessary for carrying out the calculations, whereas here the Solver is used. The ability of spreadsheets to fit nonlinear equations is shown with several examples. The results obtained are compared to other results available in the literature which use the Gauss– Newton or Marquardt methods. The nonlinear least squares method uses a function, y, to fit the experimental points, which has the form: n
y=
Σ b j f j(x) j=1
(1)
where the coefficients bj are adjustable parameters and x is the vector whose components are the independent variables. The objective function to be minimized is the sum of the squares, SS, m
SS =
n
Σ wi yi – jΣ= 1 b j f j(x) i=1
2
(2)
with respect to the bi parameters:
g = ∂SS , ... ∂SS ∂b1 ∂bn
The natural criterion of an optimal estimate of b is a zero value of the gradient g. Many methods of a minimum search terminate the iterative process when the norm of the gradient: 2
igi =
n
Σ g j2 j=1
(4)
is sufficiently small. It is possible to select a critical value of this norm, for example 10{5, the limit under which the point bi is considered as a local minimum. Often iterations terminate when too small a change of the estimated parameter appears. This criterion is valid for derivative as well as nonderivative methods. Derivative methods are used when the function f(x) is at least twice differentiable. The quasi-Newton methods fall into this class of methods. All calculations were carried out using Excel 5.0 for Windows on a PC-AT 80486 DX2-66 MHz IBM compatible. All that is necessary to do the calculations is to prepare the worksheets for each example (i.e., any equation that is to be used for nonlinear modeling and the experimental points) and then select the Solver option from the principal menu. From the Solver dialog box the options can be chosen. In the Set Target Cell box, type (or indicate) the cell containing the SS equation on the worksheet, then choose the Max (maximize) or Min (minimize) option. The original default Solver parameters were used in all examples discussed here. In situations where a strong nonlinearity is evident, these parameters should be modified to improve the fit and to avoid divergence. Finally, after a few iterations (typically 7–10) the minimal condition is satisfied and a stable solution is reached. The optimized values of the parameters, together with the residuals and the SS, are pasted up into the worksheet. Also, the graphics containing the adjusted data can be put into the worksheet easily. The examples considered here were chosen because they are known experimentally and represent classical situations found in first-order chemical reactions. This agrees with the aim of this article, which seeks to explore characteristics present in commercial spreadsheets and frequently not used. These powerful tools can be used along with chemometrics. The mathematical models used were :
where wi represents the weight associated with the experimental point (xi,yi). Commonly, the wi elements are considered unitary. At the minimum (b*) all of the components of the gradient g of SS vanish. The gi components of the gradient vector are given by the derivatives of the function SS
448
(3)
Journal of Chemical Education • Vol. 74 No. 4 April 1997
Model I: A First-Order Chemical Reaction (type one)
A = A ∞ 1 – exp (–kt)
Information • Textbooks • Media • Resources Model II: A First-Order Chemical Reaction (type two)
P = P∞ 1 – exp (–kt) + P0 exp (–kt)
strate that this method is a fast and easy alternative to fit experimental points to any nonlinear equation. The goodness-of-fit in all cases is in agreement with that available in the literature (8–10, 12). Acknowledgment
Model III: An Exponential Equation with Nonseparable Parameters
I thank Ira M. Brinn for corrections and a critical reading of this article and for many valuable suggestions.
b2 Y = b1exp b3 + x
Literature Cited
A critical point in nonlinear fits is the initial choice of parameter values. If the initial values are far from the true ones, a poor convergence may be obtained, frequently leading to a lack of fit. In spite of the fact that different initial points were tested, the calculations converged to similar parameter values for all examples. These results agree well with results obtained using the different methods available in the literature (8–10, 12). Considering, for example, the results obtained from Model II, our best fit parameters are : P0 = 0.544 (0.544), P∞ = 0.299 (0.301), k = 26.4 (26.8) s{1. [Values in parentheses were obtained by Moore (9)]. The initial SS value of 11,809.0 was reduced after 5 cycles to 33. The results obtained demon-
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.
Rioux, F. J. Chem. Educ. 1991, 68, A282–A283. Currie, J. O.; Whiteley, R. V., Jr. J. Chem. Educ. 1991, 68, 923–926. David, C. W. J. Chem. Educ. 1989, 66, A237–A238. Simpson, J. M. J. Chem. Educ. 1994, 71, A88–A90. O’Connor, S; Bailey, D. J. Chem. Educ. 1994, 71, A91–A92. Gaeng, P. EXCEL for Science and Technology; Abacus: Grand Rapids, MI, 1993. Freiser, H. Concepts & Calculations in Analytical Chemistry: A Spreadsheet Approach; CRC: Boca Raton, FL, 1992. Copeland, T. G. J. Chem. Educ. 1984, 61, 778–779. Moore, P. J. Chem. Soc. Faraday Trans. I, 1972, 1890–1893. Poshusta, R. D. Comput. Phys. 1991, 5(2), 248–252. Himmelblau, M. D. Applied Nonlinear Programming; McGraw-Hill: New York, 1972. Militky, J.; Meloun, M. Talanta 1993, 40, 269–277.
Vol. 74 No. 4 April 1997 • Journal of Chemical Education
449
