Excel Solver

Document Sample
Excel Solver Powered By Docstoc
					Simultaneous Equations
The cleanest way to solve simultaneous linear equations is by using matrix functions.

By Matrix Algebra
Given a system of equations that can be expressed as:


       where A is the coefficient matrix,
             x is the variable vector,
             b is the constant vector

the solution is obtained by matrix inversion and matrix multiplication, viz.,

               A-1 A x = x = A-1 b

The matrix inverse function is MINVERSE().

The matrix multiplication function is MMULT().

Both of these are matrix operations, so the formulas that use them must be entered with Shift-Ctrl-Enter instead of simply E

Example: Solve the following system of equations.

               u + v + x + y + z = 5.5
               u + 2v +x - 0.5y + 2z = 22.5
               2v + 2x - y - z = 30
               u + 0.25v + x - y = 17.5
               2u - x + 0.75y + 0.5z = -11

               The coefficients for the variables are arranged in matrix form with one row per equation (light blue background).
               The constants are arranged in the adjacent vector (light red background).

               The MMULT function is used to multiply the inverse of the coefficient matrix and the constants vector.
               Select the range of cells that are to contain the results, enter the matrix formula, and press Shift-Ctrl-Enter.
               The results will be inserted into the selected cells.
               The formula may be seen in any cell in the Solution column.

                                           Variables                           Constants
                    u           v              x         y           z             b                Solution
Coefficients             1             1            1           1          1          5.5      u=           1
                         1             2            1        -0.5          2         22.5      v=           4
                         0             2            2          -1         -1           30      x=         7.5
                         1          0.25            1          -1          0         17.5      y=          -8
                         2             0           -1        0.75        0.5          -11      z=           1
By Circular References
Circular references can be used to solve simultaneous equations, but the method can be touchy.

Before using circular references, iterative calculations must be enabled. This is an option that is off by default.
To enable circular references (2007), select Excel Options. Click on Formulas and check the box to enable iteration.

Sheets based on circular references can be unstable, although there are a few "tricks" that can improve performance.

If Excel tries to evaluate a formula based on a cell that does not have a legitimate value, it will return an error.
Then any cell that depends on that one will also return an error. Using IFERROR justs feeds in dummy values
so there is something from which the iterative process can begin. Convergence depends on selecting
reasonable dummy values, so try to choose something plausible.

Example:       =IFERROR("put formula here", "put dummy value here")
               The dummy value could be coded directly into the IFERROR statement or it could refer to another cell (or formul
               An external reference would be good for a more general approach. That would make it easier to vary the value if

There is always the possibility of multiple solutions or a very sensitive solution that requires good initial
guesses at the solution in order to converge. Obtaining a solution is such cases could be difficult.

The matrix methods are much more stable, so it may be worth the effort to linearize the system prior to seeking a solution.
t-Ctrl-Enter instead of simply Enter.

equation (light blue background).

d the constants vector.
 , and press Shift-Ctrl-Enter.
is off by default.
box to enable iteration.

n improve performance.

 return an error.
n dummy values

uld refer to another cell (or formula).
make it easier to vary the value if there are stability problems.

m prior to seeking a solution.
Coefficients                            Constants   Solution
    1           1      1     1     1       5.5      u=          1
    1           2      1   -0.5    2      22.5      v=          4
    0           2      2    -1    -1       30       w=         7.5
    2           0     -1   0.75   0.5     -11       x=         -8
    1          0.25    1    -1     0      17.5      y=          1