14. Nonlinear least squares problems

Note – The example worksheets are available for download. See 1. How to obtain and use example worksheets.

Example of solving nonlinear least squares problem by XLPack is shown below.

Example

The following is the test data from NIST (National Institute of Standards and Technology) home page (https://www.itl.nist.gov/div898/strd/nls/data/misra1a.shtml).

The model function is as follows.

  
  f(x) = c1(1 - exp(-c2x))

 
The data are shown in the following table.

Two sets of initial values for the parameters are specified: (500, 0.0001) and (250, 0.0005). The certified parameter values are c1 = 2.3894212918×10² and c2 = 5.5015643181×10^-4.

Solving problem with VBA program (1)

The following is an example code using VBA library subroutine Lmdif1.

Const Ndata = 14, Nparam = 2
Dim Xdata(Ndata - 1) As Double, Ydata(Ndata - 1) As Double

Sub F(M As Long, N As Long, X() As Double, Fvec() As Double, IFlag As Long)
    Dim I As Long
    For I = 0 To M - 1
        Fvec(I) = Ydata(I) - X(0) * (1 - Exp(-Xdata(I) * X(1)))
    Next
End Sub

Sub Start()
    Dim X(Nparam - 1) As Double, Fvec(Ndata - 1) As Double, Tol As Double
    Dim Info As Long, I As Long
    '--- Input data
    For I = 0 To Ndata - 1
        Xdata(I) = Cells(9 + I, 1)
        Ydata(I) = Cells(9 + I, 2)
    Next
    '--- Start calculation
    Tol = 0.0000000001  '** 1e-10
    For I = 0 To 1
        '--- Initial values
        X(0) = Cells(6 + I, 1)
        X(1) = Cells(6 + I, 2)
        '--- Nonlinear least squares
        Call Lmdif1(AddressOf F, Ndata, Nparam, X(), Fvec(), Tol, Info)
        '--- Output result
        Cells(6 + I, 3) = X(0)
        Cells(6 + I, 4) = X(1)
        Cells(6 + I, 5) = Info
    Next
End Sub

You should define the object function as the external subroutine and call Lmdif1 with specifying the initial value and the required accuracy. Lmdif1 starts the iterative computation from the given initial values and finds the least squares solution located near.

The following result was obtained by running this program. The calculated values using the obtained parameters (orange line) and the input data (blue dots) are also shown in the graph below.

In this example, the correct parameters were obtained for both initial values. For practical use, however, the best possible approximation should be given as the initial value. Otherwise, it may be difficult to converge for some problems and initial values. Further, there may be a problem which has multiple local minimums. In that case, the routine may converge to undesired local minimum if the best initial value is not given.

Solving problem with VBA program (2)

The following is an example code using the reverse communication version VBA library subroutine Lmdif1_r.

Sub Start_r()
    Const M As Long = 14, N As Long = 2
    Dim X(Nparam - 1) As Double, Fvec(Ndata - 1) As Double, Tol As Double
    Dim Info As Long, I As Long, J As Long
    Dim XX(Nparam - 1) As Double, YY(Ndata - 1) As Double, IRev As Long, IFlag As Long
    Dim Xdata(Ndata - 1) As Double, Ydata(Ndata - 1) As Double
    '--- Input data
    For I = 0 To Ndata - 1
        Xdata(I) = Cells(9 + I, 1)
        Ydata(I) = Cells(9 + I, 2)
    Next
    '--- Start calculation
    Tol = 0.0000000001  '** 1e-10
    For I = 0 To 1
        '--- Initial values
        X(0) = Cells(6 + I, 1)
        X(1) = Cells(6 + I, 2)
        '--- Nonlinear least squares
        IRev = 0
        Do
            Call Lmdif1_r(Ndata, Nparam, X(), Fvec(), Tol, Info, XX(), YY(), IRev)
            If IRev = 1 Or IRev = 2 Then
                For J = 0 To Ndata - 1
                    YY(J) = Ydata(J) - XX(0) * (1 - Exp(-Xdata(J) * XX(1)))
                Next
            End If
        Loop While IRev <> 0
        '--- Output result
        Cells(6 + I, 3) = X(0)
        Cells(6 + I, 4) = X(1)
        Cells(6 + I, 5) = Info
    Next
End Sub

Instead of defining the object function as the external VBA function, when returned with IRev = 1, you need to compute the object function values at XX(), enter them in YY() and call Lmdif1_r again. For further details of reverse communication version, see here.

The same result with above is obtained by this program.

Solving problem with Solver

“Nonlinear Least Squares Problems” of XLPack solver add-in can be used to solve this problem.
The model function values are computed in column C using the parameter values in B8 and C8. For example, the formula =B$8*(1-EXP(-C$8*A11)) is entered in C11 cell. The residuals are computed in column D. For example, the formula =B11-C11 is entered in D11 cell. Solver computes the least squares solutions from these residual values.

Please refer to here for how to use solver.