Gaussian Elimination

**cjrrussell** · 12-20-2005, 08:30 AM

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

Jerry W. Lewis · 12-20-2005, 09:10 AM

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

Shaz · 12-20-2005, 09:25 AM

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

**cjrrussell** · 12-20-2005, 09:41 AM

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.

Bernard Liengme · 12-20-2005, 10:35 AM

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

**cjrrussell** · 12-20-2005, 11:00 AM

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

Can someone explain any further then?

Thanks again

Harlan Grove · 12-20-2005, 03:10 PM

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?

**cjrrussell** · 12-21-2005, 05:18 AM

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.

Harlan Grove · 12-21-2005, 01:25 PM

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].

**cjrrussell** · 12-22-2005, 05:01 AM

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

Dana DeLouis · 12-22-2005, 10:35 PM

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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** · 12-23-2005, 04:59 AM

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

But thanks I will still have a look anyway

**luv_mish** · 09-12-2006, 02:12 PM

hey i've got the same assignment to do, that is to do gaussian elimination in excel. have u got the answer dude? can u plz send it to me.. this would really help. i've to submit that assigment in 2 days.. plz plz plzzz

e-mail: [email protected]

thanks in advance!!

**luv_mish** · 09-13-2006, 11:40 PM

can someone plz help me abt this assignment.. i really would be grateful

**teejai25** · 09-24-2006, 05:21 AM

I just completed a few problems using Excel, but I'm not 100% confident in the logic. For one of the problems I completed I had to switch two rows to make it work. I would do the same thing if I did it manually so it isn't a problem if the user knows the steps in solving Gaussian Elimination. I will share with you the first row of my spreadsheet and it should help get you going. If not, let me know.