Several Linear Equations with EXCEL
Displayed here is the use of EXCEL to solve sets of linear algebraic equations using the 'Solver'.
Suppose we want to solve the problem
where Aij is a square nxn matrix, fi is a vector of length n. xi is a vector of length n, whose elements we wish to determine. Take the example with the matrix Aij and vector fi as shown.
In EXCEL we have to set up the problem so that the elements of xi can be chosen to minimize something which should be zero. Prepare the spreadsheet as follows. Put the matrix in the square array A2:B3 and the vector in the column D2:D3. The column C2:C3 is saved for the elements of xi.
| A | B | C | D |
| 3.2 | 4.1 | 3 | |
| -5.0 | 0.8 | 4 |
Then in column E put the functions which should be zero.
A2*$C$2 + B2*$C$3 - D2
A3*$C$2 + B3*$C$3 - D3
By putting the $ in the cell E2 it is possible to copy the cell down and it will be correct. If the $ are left off of cells C2 and C3 then the second element would be incorrect if copied from cell E2. Try it. Finally, in E4 put the value to be minimized.
=sqrt(E2*E2+E3*E3)
This can also be written as
=sqrt(SUMSQ(E2:E3))
To check the work, put the value 1.2 in cell C2 and 2.3 in cell C3. The values in cell E2:E4 are
10.27
-8.16
13.117107
Hand calculation shows that these are right. Next return the values of C2 and C3 to 0, under 'Tools' choose 'Solver', and prepare the input to look like this.
| Set Target Cell | $E$4 |
| Equal to Value of | 0. |
| By changing cells | C2:C3 |
Click 'Solve' and the following results appear:
| column C | column E |
| -.6071123 | -2.014E-06 |
| 1.20555056 | 1.8965E-6 |
| 2.7666E-6 |
Since we are using the optimization routine, we need to check by reducing the tolerance, allowing more iterations, and resolving the problem. Under Tools' choose 'Options...', then 'Calculation', and set the number of iterations to 1000 and the tolerance to 10-8. Then choose 'OK', choose the 'Solver', click 'Solve' and the following results appear.
| column C | column E |
| -.6071119 | -8.0444E-07 |
| 1.20555094 | 1.9405E-07 |
| 8.2751E-07 |
The answer differs in the 7th and 8th digit, respectively, so that the answer is regarded as a satisfactory solution.