Matrices and Excel?

Question

I'm trying to solve systems of equations using matrices function on microsoft excel. For example:

Solve the following system of equations using matrices:

2x+10y+6z = 74
4x+22y+20z = 188
3x+17y+20z = 163

I have no experience with matrices, and don't know what formula in excel to use when I plug these numbers in. Can anyone help??

Answer 1

In order to solve a system of equations through Excel, you need to use a couple of formulas. I'm not sure if you're familiar with matrices, so let me give you a brief explanation.

In general, a matrix is table consisting of numbers. This table contains 'm' row and 'n' columns. For example, a 2x3 matrix has 2 rows and 3 columns. Now let's apply this to the problem, and using a little bit of Linear Algebra.

In terms of Linear Algebra, a system of equations is defined by the following relationship:

AX = B, where:

A = matrix of constants
x = matrix of uknown variables
B = matrix of answers

In this case, we can write those matrices like this:

A =

2, 10, 6
4, 22, 20
3, 17, 20

X =

x
y
z

B =

74
188
163

ultimately, we are solving for the matrix X. To do this, we take the inverse of A and distribute to both sides. Thus, we have:

X = (A^-1)*B

Now all we need to do is perform the following matrix multiplication and we have our solution.

Here are the steps to do this in Excel.

1) Create the matrix A by inputing the components of A into a 3x3 block

2) Define the matrix. You do this by going to Insert -> Name -> Define. Label the matrix, let's call it 'A'

3) Do the same thing for matrix B

4) Define the inverse of matrix A (i.e. A^-1). To do this, you first need to highlight a 3x3 block of cells on the spreadsheet. Then type the following formula:

=MINVERSE(A)

where A denotes the labeled matrix. If you used another name to label the matrix, you would place that name inside the parentheses. Also, make sure that when you enter this formula, YOU MUST press CTRL+SHIFT+ENTER simulatneously. This tells Excel that you are doing an 'array (or matrix) calculation' I got the following result for A^-1

25/3, -49/6, 17/3
-5/3, 11/6, -4/3
1/6, -1/3, 1/3

Again, you label this matrix, let's call it INVERSE. Now we can solve for X. You need to use another formula to perform matrix multiplication. Again, highlight a 3x1 cell block on the spreadsheet and type the following formula:

=MMULT(INVERSE, B)

The following result is X and has the following values:

5
4
4

This means that x = 5, y = 4, z = 4.

You can use a graphing calculator as well using the function "rref" under the MATH functions

Below is a website to help you with matrices

-----------

Hope this helps

Answer 2

RE: Matrix multiplication in Excel? I am trying to multiply two matrices in Excel. I use the command =MMULT(), but it just gives me the first element. Does anyone have an idea what I might be doing wrong? Yes, I made sure the number of columns of the first is equal to the number of rows in the second.

Answer 3

Your equations are in the form ax + by + cx = d

a b c and d make the matrix, so your matrix would be:

| 2 10 6 74 |
| 4 22 20 188 |
| 3 17 20 163 |

As for using Excel to solve them, check the following:

http://people.hofstra.edu/faculty/Stefan_Waner/RealWorld/Excel/tuts/ExcelPivot.xls

Answer 4

i can use a graphing calculator but have never seen how to do it using excell sorry