How to Solve Linear Equations with the Matrix Method in Excel
You can solve a system of linear equations by turning the coefficients of the variables into a square matrix, as noted in Hoffman and Kunze's "Linear Algebra." For example,
2x+4y=0
3x+2y=2
yields the matrix
2__4
3__2.
Here, the underscores are merely spacers. If A is the matrix above, X is the variable vector (x y), and B is the vector (0 2), then the matrix multiplication representation is AX=B. Therefore, if A* is the inverse of A, then X equals A*B, the matrix product of A* and B. Excel can solve for the elements of X if they are unique.
Other People Are Reading
Instructions
-
-
1
Order the terms in your linear equations so the variables line up on the left side of the equations and the constants are on the right side, as in the example in the introduction above. The coefficients on the left side will be your matrix A.
-
2
Enter the elements of the matrix A into the upper left corner of an Excel spreadsheet starting at cell A1. So if A is a 2x2 matrix, you'll enter the bottom-right coefficient in cell B2.
-
-
3
Enter the elements of the vector B vertically into the cells immediately to the right of the elements of matrix A.
-
4
Below the matrix A cells, select empty cells of the same shape as A. For example, if A is 2x2, select an area that has two rows and two columns.
-
5
Move your mouse pointer up to the "formula bar" and click inside. The formula bar is the long window just below the toolbars, which shows the contents of the current cell. In most versions of Excel, an equal sign is to the left of it.
-
6
Enter =MINVERSE( into the formula bar.
-
7
Use your pointer to highlight the coefficients of A, so that the matrix location becomes an argument for the MINVERSE function.
-
8
Type the closing parenthesis for the MINVERSE function, and then hold down the Control and Shift buttons (or just the Command button on a Mac) and press Return. The inverse of the matrix A should appear in the cells, you initially highlighted, if A is invertible.
-
9
Multiply B by the inverse of A to get X by using the MMULT function. Do this by first highlighting the cells to the right of the cells you filled with the elements of B (highlighting the same number of cells as B, of course).
-
10
Activate the formula bar again, as you did earlier. Type =MMULT() inside. Then fill the parentheses with the location of the inverse of A and the location of B, in that order, and separated by a comma. You can do this easily by using your mouse pointer to select the cells holding the inverse of A, typing the comma, and then selecting the cells holding the elements of B.
-
11
Hold down the Control and Shift buttons (or just the Command button on a Mac) and press Return, as you did before. The results appearing in the cells that you highlighted initially should now display the vector solution X for the system of linear equations AX=B. The elements will be the values of the variables in the same order you entered their coefficients in A. For example, if you entered A with the coefficients of x first, and of y second, that's the order they'll appear in the vector X.
-
1
Tips & Warnings
You can manually type in the braces that appear around array commands like MINVERSE; you have to use the Control/Command method.
