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?