Nonlinear Regression Using Excel

Step 1 Place the data on a new sheet in the Workbook, columns A, B, and C from Table E.3, as reproduced in Table E.7. 

Step 2 Select some cells for the parameters k, n, and m. Arbitrary values are inserted -use your best guess, because that will possibly mean the difference between success and failure. 

Step 3 In column D, calculate the value of rate using the parameters in C16 C18, the data in columns A and B, and the formula, Eq. (E.17). 

Step 4 Make column E the difference between columns C and D, and then square the 

TABLE E.7. Correlation of Rate Expression using Solver in Excel Initial Guess 

---

For more information on nonlinear regression using Excel see. R. Crouch and F. J, Holler, Applications of Microsoft Excel in Analytical Chemistry. 13. Belmont. CA Brks/Cole. 2004. 

It is clear that by adjusting the parameters of this model by nonlinear regression, an excellent fit to the experimental data could be obtained. The value of such a procedure is rather dubious, however, and it is more useful to use the model to obtain qualitative information about the quantities Perf(°°), Peaf(°°) and Big, which are poorly determined in the literature. 

Spreadsheet Summary The second exercise in Chapter 13 of Applications of Microsoft Excel in Analytical Chemistry involves enzyme catalysis. A linear transformation is made so that the Michaelis constant, K, and the maximum velocity, can be determined from a least-squares procedure. The nonlinear regression method is used with Excel s Solver to find these parameters by fitting them into the nonlinear Michaelis-Menten equation. 

To use nonlinear regression, you minimize Eq. (E.3) with respect to the unknown parameters. Polynomial and multiple regression do this too (behind the scenes), but for nonlinear curve fits it is necessary to use functions such as Solver in Excel and fminsearch in MATLAB. This is demonstrated using the same example given above for multiple regression. 

If poor initial parameter estimates are used, it becomes likely that the nonlinear regression will not be able to find an acceptable fit, or it may stride off on a mathematical tangent and arrive at a nonsensical fit to the data. It turns out that the values determined by traditional methods of residual analyses typically offer excellent starting estimates for the model parameters. Hence the traditional data analysis methods described in this chapter remain highly useful, even when nonlinear regression curve fitting is employed as the final step of parameter evaluation. 

The regression is garmaUy nonlinear and in the second case the computations are even more complicated because the equation is implicit in x. Peterson and Lajndus [42] used the integral method with nonlinear regression on Franckaerts and Froment s data and found excellent agreement, as shown by Table 2.3.C-1. A further illustration of such agreement is based on Hosten and Froment s data on the isomerization of n-pentane [38] as analyzed by Froment and Mezaki [43],... 

Equation [8.89] is linear with respect to parameters 4> and 0, but nonlinear with respect to q, and therefore the data must be fitted to this calibration function using nonlinear least-squares regression (Section 8.3.8) it is emphasized that it is very important to ensure that the initial estimates for the unkown parameters should be reasonably close to the final best estimates (see the text box dealing with nonlinear regression). In the present example (Equation [8.89]) excellent initial estimates can be obtained experimentally (see below) but if this is not possible tricks can be employed to obtain reasonable first estimates. One way is to plot the experimental data for Ra VRsis s Qa VQsis" nd draw an approximate curve though the points by hand. Experimental data expected to be well represented by Equation [8.89] should extrapolate to a value of (Ra /Rsis ) = 0 as (Qa"/Qsis ) zero, and to (Ra VRsis") = as (Qa"/Qsis") becomes... 

A comparison with a hard-model evaluation based on nonlinear regression techniques shows excellent agreement. The proposed ANN method is of a general nature and, in principal, can be adopted to any instrumental technique used in equilibria studies including extraction. 

This section presents three examples that show how to implement various forms of regression analysis in Excel. The topics considered are linear regression, nonlinear regression, and analysis of factorial design. All examples are based on real data obtained from experiments. The procedures use the appropriate templates for... 

The nonlinear regression Excel template used is set up identically to that of the linear regression template. The only difference is that now the estimated parameter values are not computed using a formula. Instead, they must be determined using Solver. Given the problem set-up, initial parameter estimates can be a bit of an issue, as the solution is sensitive to them. A recommended initial guess would be 0.5 for b and 2.5 for B. The macros are shown in Sect. 8.7.2.3 VB Macros. 

Another approach is to use a nonlinear regression technique, which produces a weighted least square that maximizes the efficiency of the parameter estimation. This technique does not require linearization and can be used to determine multiparameters. Hernandez and Ruiz (1998) developed an Excel template for the calculation of enzyme kinetic parameters using this technique. 

St data have been obtained for the off-gas CO2 concentration response obtained from changing the feed rate to a bioreactor. Use Smith s method as well as nonlinear regression based on Excel and MATLAB to estimate parameters in a second-order model from experimental... 

Nonlinear least-squares analysis can be used to obtain best fit values of the unknown parameters in a nonlinear rate model. Elementary nonlinear regressions can be performed using the SOLVER function in EXCEL. 

A nonlinear regression was then performed in an EXCEL spreadsheet using SOLVER, beginning with these estimates.The resulting values of ti, x, and f were 10 min, 49 min, and 0.80. These values were then used to calculate E t). A comparison of the E(t) data from the table above with the calculated values of E(t) from the CSTRs in parallel compartment model are shown in the following table. The fit of the model to the data is excellent. 

Numerical Methods for Chemical Engineers Using Excel , VBA, and MATLAB Exercise 9.2 Nonlinear regression. 

---