XLPack Solver – Nonlinear Least Squares Problems
Solve the nonlinear lease squares problems.
Let’s solve the following example (Reference: https://www.itl.nist.gov/div898/strd/nls/data/misra1a.shtml).
Find the parameters p1 and p2 to approximate the following data using the model function f(x) = p1*(1 – exp(-p2*x)) with least sum of squares of residuals.
x y 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.4 689.1 75.47 760.0 81.78
The solver program will write the value of p1 and p2 into the variable value cells (B8 and B9 in this case). The function value cells (G7:G20 in this case) must contain the formula to compute f(x) using given p1 and p2. To solve this example, the formula for G7 is =F7-B$8*(1-EXP(-B$9*E7)), G8 is =F8-B$8*(1-EXP(-B$9*E8)), …
Jacobian cells contain the formulas of Jacobian matrix. These are necessary only when Lmder1 or N2g is selected as the solver program.
Initial value cells contain the initial values of p1 and p2. If these are not specified (range remains blank), initial values are assumed to be contained in the variable value cells.
The obtained solutions are output to the output cell range. If it is not specified (range remains blank), the solutions will be output to the variable value cells.
Lmdif1, Lmder1, N2f or N2g can be selected as the solver program.
The standard value of tolerance is 1.0e-8. The tolerance value will be set to Tol of Lmdif1 and Hybrj1, and Xctol of N2f and N2g.
Click “Compute”. Then the solutions will be computed and output.
The cell ranges can be specified as larger than required. In that case, only the necessary range will be used from left upper corner.
The nonlinear least squares problems may have several solutions. The different solution may be obtained depending on the initial values. The different solution may be obtained if the solver program is different even if the initial values are same. It is therefore recommended to give the initial values nearest to the desired solution.
If the solution is not obtained, #NUM! error will be output. The major suspected causes in that case are as follows: The algorithm was not converged due to the bad initial values; The given equation has no solution.
Please refer to here for “Save/Restore” button.
When “Help” button is clicked, this page will be displayed if the network connection is available.
The “?” button of right upper corner will not work correctly. Please use “Help” button.