solving a matrix in Excel

[Jon]

Hi,

I am wondering how to solve an 8x8 matrix using Excel equations.

Supposing the situation is

column
A B C D E F G H I
1 7 4 2 9 .... 2| 5
2 10 3 6 ... | 1
r 3 2 ... |
o 4 .
w 5 . data
6
7
8

In other words, it is solving the system

A1*x[1]+B1*x[2]+C1*x[3]+D1*x[4]+E1*x[5]+F1*x[6]+G1*x[7]+H1*x[8]=I1
A2*x[1]+B2*x[2]+C2*x[3]+D2*x[4]+E2*x[5]+F2*x[6]+G2*x[7]+H2*x[8]=I2
A3*x[1]+B3*x[2]+C3*x[3]+D3*x[4]+E3*x[5]+F3*x[6]+G3*x[7]+H3*x[8]=I3
..
..
A8*x[1]+B8*x[2]+C8*x[3]+D8*x[4]+E8*x[5]+F8*x[6]+G8*x[7]+H8*x[8]=I8

I have already done this but my answers are wrong. It took several
manipulations and created about 16 blocks on the spreadsheet in 230
rows. In its final form the solution should have a diagonal of all 1's
between A1 and H8 and the rest zeros, except column I should be the
solutions to each x[1],x[2],..,x[8].

Does anyone know what I'm talking about? Any help would be appreciated.
Is there a formula that will compute the matrix all in one formula?

Jon

 

[keepITcool]

Jon,

as you supposed.. excel DOES have a function for it:
see help for MDETERM().. and related MMULT() and MINVERSE()

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Harlan Grove]

I am wondering how to solve an 8x8 matrix using Excel equations. ....

A1*x[1]+B1*x[2]+C1*x[3]+D1*x[4]+E1*x[5]+F1*x[6]+G1*x[7]+H1*x[8]=I1

....
A8*x[1]+B8*x[2]+C8*x[3]+D8*x[4]+E8*x[5]+F8*x[6]+G8*x[7]+H8*x[8]=I8

....

So y = A x. *Left* multiply both sides of the equation by the inverse of A
and what do you get? See MMULT and MINVERSE in online help.

 

[Jon Giffen]

Hi,

I solved the matrix using standard row operations. Then I used x=A^-1
*b in Excel and all I got was garbage. My first results showed
virtually no error. The commands I used in Excel were,

=MINVERSE(AA1:AH8) (Saved in AA11:AH18)
=MMULT(AA11:AH18,AI1:AI8) (Saved in AI21:AI28)

To review, the problem is to solve 8 equations for 8 unknowns. The
unknowns are x[1],x[2],x[3],..,x[8] and the augmented matrix is,

AA1 AB1 AC1 ... AH1 | AI1
AA2 AB2 AC2 ... AH2 | AI2
AA3 AB3 AC3 ... AH3 | AI3
..
..
AA8 AB8 AC8 ... AH8 | AI8

What did I do wrong?

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dare]

The augmented matrix

AA1 AB1 AC1 ... AH1 | AI1
AA2 AB2 AC2 ... AH2 | AI2
AA3 AB3 AC3 ... AH3 | AI3
..
..
AA8 AB8 AC8 ... AH8 | AI8

I attempted to solve with the two operations,

=MINVERSE(AA1:AH8) saved in AA11:AH18
=MMULT(AA11:AH18,AI1:AI8)

in Excel, but all I got was garbage. Some ove the cells were very
large (1E14). When I tediously solved the matrix using elementary row
operations, there was virtually no error. Did I do something wrong?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Alan Beban]

The augmented matrix

AA1 AB1 AC1 ... AH1 | AI1
AA2 AB2 AC2 ... AH2 | AI2
AA3 AB3 AC3 ... AH3 | AI3
.
.
AA8 AB8 AC8 ... AH8 | AI8

I attempted to solve with the two operations,

=MINVERSE(AA1:AH8) saved in AA11:AH18
=MMULT(AA11:AH18,AI1:AI8)

in Excel, but all I got was garbage. Some ove the cells were very
large (1E14). When I tediously solved the matrix using elementary row
operations, there was virtually no error. Did I do something wrong?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

The methodology is correct; the solution vector should be given by

=MMULT(MINVERSE(AA1:AH8),AI1:AI8)

Perhaps you'd like to post the elements of the constituent matrices so
we can reproduce your problem. Excel is not always very good at
extracting the inverse when some of the results are very large or very
small.

Alan Beban

 

[Dare]

reference of root:
(1,-1,1,-1,1,-1,1,-2)*X-1=0

below the data is 9 elements long per row, with the last element of the
row the augment of the matrix:

row 1:
-0.87618856 -0.287065453 -0.013432611 0.091462494 -0.013432611 0.0914624
94 -0.013432611 0.093827036 -0.090909091

row 2:
-0.287065453 -0.285522631 0.136657108 -0.147725177 0.136657108 -0.147725
177 0.136657108 -0.148060573 0.090909091

row 3:
-0.013432611 0.136657108 -0.77085744 0.12890946 0.22914256 0.12890946 0.
22914256 0.139759521 -0.090909091

row 4:
0.104037807 -0.147725177 0.12890946 -0.847780517 0.12890946 0.152219483
0.12890946 0.160738542 0.090909091

row 5:
-0.013432611 0.136657108 0.22914256 0.12890946 -0.77085744 0.12890946 0.
22914256 0.139759521 -0.090909091

row 6:
0.091462494 -0.147725177 0.12890946 0.152219483 0.12890946 -0.847780517
0.12890946 0.160738542 0.090909091

row 7:
-0.013432611 0.136657108 0.22914256 0.12890946 0.22914256 0.12890946 -0.
77085744 0.139759521 -0.090909091

row 8:
0.093827036 -0.148060573 0.139759521 0.160738542 0.139759521 0.160738542
0.139759521 -0.830155456 0.181818182

the nature of the problem is outlined on my site at,

http://mypeoplepc.com/members/jon8338/polynomial/

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!