For an assignment i am doing at uni i have been asked to produce a
spreadsheet that will solve a set of 5 simultaneous equations using
gaussian elimination.

I have set up the spreadsheet to do this, however, we have also been
asked to make it work if we get a zero on the leading diagonal. This
means that the equations would have to be rearranged.

This is the part im not sure how to do. Obviously i would be starting
with an 'IF' expression, but other than that i dont know where to
start.

Hopefully someone will be able to help me

Thanks in advance

Chris


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

Presumably your class should have covered the process of pivoting
http://mathworld.wolfram.com/Pivoting.html
to deal with this situation. Reordering rows is equivalent to
pre-multiplying by by a matrix that has only one 1 in each row/column,
with the rest zero.

Jerry

cjrrussell wrote:

For an assignment i am doing at uni i have been asked to produce a
spreadsheet that will solve a set of 5 simultaneous equations using
gaussian elimination.

I have set up the spreadsheet to do this, however, we have also been
asked to make it work if we get a zero on the leading diagonal. This
means that the equations would have to be rearranged.

This is the part im not sure how to do. Obviously i would be starting
with an 'IF' expression, but other than that i dont know where to
start.

Hopefully someone will be able to help me

Thanks in advance

Chris

In Excel to obtain the inverse of the MAtrix use MINVERSE() and to
multiply MMULT. They are array functions. Therefore if you have the
following equaitons:


2x + 3y + 5z = 6
5x + y + 3z = 8
x + y+ z = 15

you would have the following matrix:
A B C
1 2 3 5
2 5 1 3
3 1 1 1

You take the inverse of this matrix and them multiply by
D
1 6
2 8
3 15

You should get

6.4
24.9
-16.3

Select say E1:E3 and write in E1=MMULT(MINVERSE(A1:C3),D1:D3)) + ctr
shift + enter.

I hope this helps.

cjrrussell wrote:
For an assignment i am doing at uni i have been asked to produce a
spreadsheet that will solve a set of 5 simultaneous equations using
gaussian elimination.

I have set up the spreadsheet to do this, however, we have also been
asked to make it work if we get a zero on the leading diagonal. This
means that the equations would h ave to be rearranged.

This is the part im not sure how to do. Obviously i would be starting
with an 'IF' expression, but other than that i dont know where to
start.

Hopefully someone will be able to help me

Thanks in advance

Chris


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

Thanks for your replies guy.

Shaz, the method you suggest does not use gaussian elimination, so
unfortunatley that is no use. We have to use gaussian elimination for
this assignment.


Jerry, I'm not really sure what you mean. are you saying to multiply by
a unit matrix? If so thats not really gaussian elimination either is it?
If its not im not sure how that would help.

I know the technique on how to solve the problem, its just i am having
trouble putting that into excel.


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

IN Gaussian elimination (manually) you need to transpose rows or columns
Jerry was telling you how to set this up in Excel so you do not have to
write complex formulas to do the move
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cjrrussell" wrote
in message ...

Thanks for your replies guy.

Shaz, the method you suggest does not use gaussian elimination, so
unfortunatley that is no use. We have to use gaussian elimination for
this assignment.


Jerry, I'm not really sure what you mean. are you saying to multiply by
a unit matrix? If so thats not really gaussian elimination either is it?
If its not im not sure how that would help.

I know the technique on how to solve the problem, its just i am having
trouble putting that into excel.


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile:
http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

Ahh i see, i must have misunderstood what he was saying then.

Can someone explain any further then?

Thanks again


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

cjrrussell wrote...
Ahh i see, i must have misunderstood what he was saying then.

Can someone explain any further then?

I guess no one wants to do your homework for you. Nevertheless, since
it's pointless to perform Gaussian Elimination in spreadsheets which
provide matrix inversion functions, it seems your instructor has
assigned busy work, but it's YOUR busy work.

Jerry's approach is arguaby most applicable. If at some stage you had

1 0 2 x
0 0 5 y
0 3 4 z

Then if you premultiply this by the matrix

1 0 0
0 0 1
0 1 0

you'd get

1 0 2 x
0 3 4 z
0 0 5 y

As for choosing the appropriate matrix to swap rows, shouldn't you do
some of the work?

I've got no problems doing the work and im not asking for the answer!!

The problem i am having is expressing the answer in excel. I have very
little experience with using functions in excel.

I know that if i get a zero on the leading diagonal the i need to
rearrange the rows, but how i tell excel to do this is where im
struggling.


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

cjrrussell wrote...
I've got no problems doing the work and im not asking for the answer!!

The problem i am having is expressing the answer in excel. I have very
little experience with using functions in excel.

I know that if i get a zero on the leading diagonal the i need to
rearrange the rows, but how i tell excel to do this is where im
struggling.

None of the rest of us know the extent to which this is just a
mathematical exercise or a spreadsheet modeling exercise. If it's the
former, then unless your instructor is a fool, s/he'll be willing to
discuss this sort of Excel implementation issue with you. If the
latter, then it really is up to you to figure out.

That said, the general way to check for zero in the i,i entry in a
matrix M would be

INDEX(M,i,i)=0

and the general way to find the first row j i with a nonzero entry in
the i_th column would be

MATCH(1,(ROW(M)-ROW(INDEX(M,1,1))=i)*(INDEX(M,0,i)<0),0)

Note that a formula containing this expression would need to be entered
as an array formula, holding down [Ctrl] and [Shift] keys before
pressing [Enter].

Thanks Harlan

The main point of the excercise is to produce a spreadsheet that will
solve a set of 5 well conditionined equations. Our tutor has said that
as an extra, to get a top mark, make it work when you get a zero on the
leading diagonal.

Of course he is will to discuss this, but we are on christmas break at
the moment and im only doing it to try to get ahead of myself.

Anyway you have given me something to thing about and something to
discuss with him in the new year.

Thanks again


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

Not sure if this would be helpful, but "Numerical Recipes in C" has a code
example. Maybe you can find some ideas here.

Gauss-Jordan Elimination
http://www.library.cornell.edu/nr/bookcpdf/c2-1.pdf

--
Dana DeLouis
Win XP & Office 2003


"cjrrussell" wrote
in message ...

I've got no problems doing the work and im not asking for the answer!!

The problem i am having is expressing the answer in excel. I have very
little experience with using functions in excel.

I know that if i get a zero on the leading diagonal the i need to
rearrange the rows, but how i tell excel to do this is where im
struggling.


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile:
http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838

jeez thanks Dana but C programming is way over my head!!

But thanks I will still have a look anyway


--
cjrrussell
------------------------------------------------------------------------
cjrrussell's Profile: http://www.excelforum.com/member.php...o&userid=19788
View this thread: http://www.excelforum.com/showthread...hreadid=494838