Worksheet Functions to Solve Linear Equations

ID: Q69207

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for OS/2 versions 2.2, 2.21, 3.0

SUMMARY

It is possible to use the built-in matrix functions of Excel to solve systems of linear equations. The system of equations must have a single solution. The matrix must be square.

MORE INFORMATION

Example

The two linear equations


   x-3y=2 and 2y=6

can be represented on a worksheet in a matrix, as follows:


   A1: X   B1:  Y  C1: b
   A2: 1   B2: -3  C2: 2
   A3: 0   B3:  2  C3: 6

You can then use the MMULT() and MINVERSE() functions to solve the equations. The answer in this case will be a one-dimensional (1-D) vertical array containing two values. It is necessary to select two cells, vertically, then enter the following formula as an array.


   {=MMULT(MINVERSE(A2:B3),C2:C3)}

Note: The braces ({ }) were not entered, they indicate that the formula was entered as an array with CTRL+SHIFT+ENTER.

The above formula will produce the following results if entered in cells E1 and E2:


   E1: 11
   E2:  3

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 153-155

"Microsoft Excel for Windows Functions and Macros," version 2.1x, pages 83-84 and 86-87

Additional query words: 2.0 2.00 2.1 2.10 2.2 2.20 2.21 3.0 4.0 matrix MMULT() MINVERSE()

Keywords :
Version :
Platform :
Issue type :