# 4. Linear least squares problems

Examples of solving linear least squares problems by XLPack are shown below.

### Example: Polynomial approximation

The following data is the test data of NIST (National Institute of Standards and Technology) which can be found at the web site http://www.itl.nist.gov/div898/strd/lls/data/Norris.shtml

```Data:       y          x
0.1        0.2
338.8      337.4
118.1      118.2
888.0      884.6
9.2       10.1
228.1      226.5
668.5      666.3
998.5      996.3
449.1      448.6
778.9      777.0
559.2      558.2
0.3        0.4
0.1        0.6
778.1      775.5
668.8      666.9
339.3      338.0
448.9      447.5
10.8       11.6
557.7      556.0
228.3      228.1
998.0      995.8
888.8      887.6
119.6      120.2
0.3        0.3
0.6        0.3
557.6      556.8
339.3      339.1
888.0      887.2
998.5      999.0
778.9      779.0
10.2       11.1
117.6      118.3
228.9      229.2
668.4      669.1
449.2      448.9
0.2        0.5
```

Let’s try to fit a polynomial of degree 1 (straight line approximation).

```    f(x) ≅ c1 + c2x

```

Two methods using worksheet function WDgels and VBA subroutine Dgels are shown below.

### Solving problem with worksheet function

Enter the data X and Y into the appropriate cells in the worksheet and compute matrix A (Aij = φj(xi) (i = 1 to m, j = 1 to n)) (in this case, Ai1 = 1, Ai2 = xi). Then select (n + 2) cells where the solution and other results are to be entered (c in the figure).

Click fx in the formula bar. Then select WDgels worksheet function from category XLPack.

Supply the appropriate values for the requested parameters Trans(“N” (solve Ax=b) or “T” (solve (A^)x=b), Cov (“N” (do not compute variance-covariance matrix), “D” (compute variance only) or “C” (compute variance-covariance matrix)), M (number of data points), N (number of parameters), Nrhs (number of columns of right hand side matrix), A() (range of matrix A) and B() (range of vector y).

Then press Ctrl+Shift+Enter. The parameters are obtained in the selected cells.

In this example, the graph is also created using the calculation result. The input data is shown by the circles and the calculation result is shown by the line.

Now, we’ve got the parameters c1=-0.26232 and c2=1.002117. These values are exactly same with the values of NIST.

### Solving problem with VBA program

Let’s solve the same example problem by VBA program. The following is an example code using VBA library subroutine Dgels.

```Sub Start()
Const MMax = 100, NMax = 5
Dim M As Long, N As Long
Dim A(MMax, NMax) As Double, B(MMax) As Double
Dim Info As Long, I As Long, J As Long
'--- Input data
M = 36: N = 2
For I = 0 To M - 1
For J = 0 To N - 1
A(I, J) = Cells(4 + I, 3 + J)
Next
B(I) = Cells(4 + I, 2)
Next
'--- Compute least squares solution
Call Dgels("N", M, N, A(), B(), Info)
'--- Output result
Cells(6, 5) = Info
If Info = 0 Then
For J = 0 To N - 1
Cells(4 + J, 5) = B(J)
Next
Else
MsgBox "** Error ** Info = " + Str(Info)
End If
End Sub

```

After entering the data into the cells, the same result was obtained by running the macro Start as follows.

The most simple example is shown here. However, if the variety of statistical values are need to be calculated, the method using VBA program may be better. The example in the sample worksheet may be the good reference.

Top