Nonlinear Regression Template

Requirements-, basic Excel installation plus installation of Solver and the ability to 

Goal perform nonlinear regression in Excel in an easy and straightforward manner. 

Description a screenshot of the plain template is shown in Fig. 8.26. The yellow blocks are where the required data are entered. Note that Solver needs to be used to obtain a solution to the problem. The configuration of Solver is shown as an inset in Fig. 8.26. The layout and formatting of the results are similar to the linear regression case. Two important differences are that the model and its Jacobian must be entered as a macro and that Solver must be used. The spreadsheet automatically creates the normal probability plot for the residuals and plots of the residuals as a function of y and y, as well as a time series plot of the residuals. Additional plots can be created by the user. An example of how to use the template is provided in Sect. 8.7.2 Nonlinear Regression Example. 

Warning this template requires that the internal macros be enabled and Solver installed. 

---

Fig. 8.26 Nonlinear regression template. The inset shows how to set up the Solver (Excel 2013)...

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. 

---