A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

solving a matrix in Excel



 
 
Thread Tools Display Modes
  #1  
Old July 4th 04, 03:48 AM posted to microsoft.public.excel.programming
Jon[_17_]
external usenet poster
 
Posts: 1
Default solving a matrix in Excel

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

Ads
  #2  
Old July 4th 04, 04:34 AM posted to microsoft.public.excel.programming
keepITcool
external usenet poster
 
Posts: 2,253
Default solving a matrix in Excel

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 >


Jon > wrote:

> Hi,
>
> I am wondering how to solve an 8x8 matrix using Excel equations.
>
>
> 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
>
>


  #3  
Old July 4th 04, 07:05 AM posted to microsoft.public.excel.programming
Harlan Grove
external usenet poster
 
Posts: 733
Default solving a matrix in Excel

"Jon" > wrote...
>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.


  #4  
Old July 6th 04, 03:10 AM posted to microsoft.public.excel.programming
Jon Giffen
external usenet poster
 
Posts: 1
Default solving a matrix in Excel

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!
  #5  
Old July 6th 04, 03:30 AM posted to microsoft.public.excel.programming
Dare
external usenet poster
 
Posts: 2
Default solving a matrix in Excel

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!
  #6  
Old July 6th 04, 03:59 AM posted to microsoft.public.excel.programming
Alan Beban[_2_]
external usenet poster
 
Posts: 783
Default solving a matrix in Excel

Dare wrote:

> 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
  #7  
Old July 7th 04, 07:45 AM posted to microsoft.public.excel.programming
Dare
external usenet poster
 
Posts: 2
Default solving a matrix in Excel


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!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Solving Sudoku using Excel azmanblues Excel Discussion (Misc queries) 5 July 28th 09 05:32 PM
Solving Sudoku with EXCEL LynnI via OfficeKB.com Excel Worksheet Functions 4 January 29th 08 03:40 AM
Mathematic problem solving using Excel Stonewall Excel Worksheet Functions 2 August 15th 06 08:16 PM
solving equations using Excel arnold_charming Excel Discussion (Misc queries) 0 December 7th 05 04:30 PM
i've got a simple excel problem that needs solving... Massive Excel Discussion (Misc queries) 6 May 16th 05 08:46 AM


All times are GMT +1. The time now is 10:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.