Nonlinear Regression Example in Excel

This example examines the problem of fitting a theoretical equation to experimental data in order to obtain the values of the different constants in the system. Unlike the previous case, nonlinear regression must be performed in order to obtain a result. Detailed information about the problem can be found in Ross-Rodriguez (2009). Data provided courtesy of Dr. Lisa Ross-Rodriguez. 

Consider the problem of obtaining a relationship for the ratio between the equilibrium and isotonic cell volumes given the osmotic pressure. The theoretical relationship can be written as 

Before we set up the problem in Excel, it is first necessary to compute some preliminary information. First, we need to obtain the derivatives of Eq. (8.4) with respect to the parameters, that is. 

It can clearly be seen that this equation is nonlinear in the parameters. Thus, nonlinear regression using Solver will be performed. In order to obtain values for the parameter confidence intervals using Equation (198), the grand Jacobian will be calculated using the best estimated values of the parameters and the above derivatives. 

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. 

---