C. E.
Analysis Name: ____________ Tutorial #10 Date: ________
1.0 Non-linear Regression Outline
Since many models don’t have linear parameters, efficient non-linear regression tools are
necessary. While is possible to do this in Matlab, the Solver command in Excel provides a
quick way to choose model parameters rapidly. The process includes the following steps:
A. Enter x and y data
B. Estimate the initial parameter values
C. Calculate the ŷs , residuals, sum of squares, and the mean square
D. Use Solver to estimate the least-squares parameters
E. Calculate the residual sum
F. Plot the function with the data and then the residuals
2.0 Array Entry
Enter t and M into Cells A1 and B1, respectively. Type in the x-y data into Columns A and
B of the spreadsheet starting on Row 2. For the tutorial example, the values are mass
absorbed (M) as a function of time (t).
t M
77.6 10.07
114.9 14.73
141.1 17.94
190.8 23.93
239.9 29.61
289.0 35.18
332.8 40.02
378.4 44.82
434.8 50.76
477.3 55.05
536.8 61.01
593.1 66.40
689.1 75.47
760.0 81.78
3.0 Initial Parameter Estimates
Next estimates for the non-linear parameters will reside in Column C. The model that
relates mass to time is:
M = 1 1 – exp – 2 t +
Type “Theta1” in Cell C3 and “Theta2” in Cell C6. Next initial estimates for the parame-
ters are needed. For large time, the exponential term is zero, so choose 1 = 100, about 1.2
times the largest M value sampled, and type it in Cell C4. Using a point near the beginning
of the data (since the last point determined the first parameter) - e.g. 289, 35 - solve for 2
= -ln(1 - 35/100)/289 = 0.0015, and place the value in Cell C7.
4.0 Calculate ŷ s, residuals, sum of squares, and the mean square
The x-values (time for this) and the parameters can be used to estimate the model values
( M̂ s) and placed into Column D. Enter the formula into Cell D2:
= C$4*(1 - exp(-C$7*A2))
and copy and paste it down the list for the number of observations.
Column E will contain the residuals and Column F the residuals squared. Type “= B2 -
D2” into Cell E2 and “=E2*E2) into Cell F2. Copy and paste the two cells for each of the
observations.
Type “Residuals Sum”, “Sum of Squares”, and “Mean Square” into Cells H3, H5, and H7,
respectively. Type “=sum(” into Cell J3, highlight all the residuals values in Column E,
followed by a “)”. Repeat the sum for Column F in Cell J5. The mean square requires the
degrees of freedom (number of observations - model parameters) which is 14 - 2 for this
example. Type “=J5/12” in Cell J7.
5.0 Use Solver
Refer to the Nonlinear Regression PowerPoint for details on the use of Solver, since you
made have to load the add-in for it to appear on the Data-tab toolbar. Once you have
opened the Solver, type in J7 for the objective, and click the radio button to minimize the
sum of squares. The variable cells are C4, C7, and check the box above the solution
method to ensure only positive parameter values are tested. In the drop-down menu
choose the solution method to be the GRG-Nonlinear option. Upon completion, your win-
dow should look like:
Press the Solve button, and you should receive a message that the parameters have been
updated. Close the window and observe that the mean square value in J7 is much smaller.
Also the values for the parameters have been updated to reflect the least squares result.
6.0 Model and Residual Plots and Residual Sum
These processes are similar to linear regression. I usually take the values for the model
parameters and the residuals and enter them into Matlab. The well-distributed x-values
can be used to generate ŷs , and then non-linear model performance can be compared on
the same plot as the linear polynomial models.
7.0 Required
Use the data:
x = {1.31, 1.47, 1.49, 1.56, 1.61, 1.68} and y = {2.1, 3.4, 3.6, 4.3, 4.9, 5.7}
2
to fit coefficients for the model y = 1 x + .
What are the initial and final parameter estimates? What’s the mean square error?