Equation Solver / Excel, Mathematica 2

Ummm... it might be easier to solve if P or E70 were in the equation. I like MathCad's solver.

Arrgh!!!... I forgot to add it in. I think that I've been staring at numbers for too long! P is a scalar that gets added into the Tasm equation. Here it is again:

Tasm=(J28*E28+J29*E29+J30*P*E30+J31*P*E31+J32*P*
E32+J35*P*E35+J36*P*E36+J37*P*E37+J38*P*
E38+J39*P*E39)+E68*E67*(SQRT((((E28/H28)^2)*(1-J28)^2)+
(((E29/H29)^2)*(1-J29)^2)+(((P*E30/H30)^2)*(1-J30)^2)+
(((P*E31/H31)^2)*(1-J31)^2)+(((P*E32/H32)^2)*(1-J32)^2)+
(((P*E35/H35)^2)*(1-J35)^2)+(((P*E36/H36)^2)*(1-J36)^2)+
(((P*E37/H37)^2)*(1-J37)^2)+(((P*E38/H38)^2)*(1-J38)^2)+
(((P*E39/H39)^2)*(1-J39)^2)))

or E70 if you prefer:

Tasm=(J28*E28+J29*E29+J30*E70*E30+J31*E70*E31+J32*
E70*E32+J35*E70*E35+J36*E70*E36+J37*E70*
E37+J38*E70*E38+J39*E70*E39)+E68*E67*(SQRT((((E28/H28)^2)*(1-J28)^2)+(((E29/H29)^2)*(1-J29)^2)+
(((E70*E30/H30)^2)*(1-J30)^2)+(((E70*E31/H31)^2)*
(1-J31)^2)+(((E70*E32/H32)^2)*(1-J32)^2)+(((E70*
E35/H35)^2)*(1-J35)^2)+(((E70*E36/H36)^2)*(1-J36)^2)+
(((E70*E37/H37)^2)*(1-J37)^2)+(((E70*E38/H38)^2)*
(1-J38)^2)+(((E70*E39/H39)^2)*(1-J39)^2)))

With a single unconstrained equation like the one you show, you should be able to solve it using the GoalSeek capability of Excel (Tools / GoalSeek).

Alternatively, you could use GoalSeek's big brother, Solver (Tools / Solver). This is a standard part of Excel, but as a one-off operation before you can use it you need to load the Solver add-in (Tools / Add-Ins). With this approach, you need to be reasonably close to the correct answer before you invoke Solver. How close you need to be depends upon how non-linear your equations are.

A third approach would be to set up a small iteration scheme away in a corner of your spreadsheet. As ZappedAgain points out, it is a bit hard for us to make definite recommendations when we don't know what you are trying to solve for, but your equation does not look all that horrendous. Provided you can automatically generate an adequate starting estimate you could implement a fixed (say) five iterations using Newton's method, and that should get you close enough. If not, increase the fixed number of iterations to ten, or a hundred.

Depending on the context, the advantage of this third approach is that it is hot-wired into your spreadsheet, and does not require you to manually invoke GoalSeek or Solver. (Yes, I know that these can be invoked automatically using event handlers in VBA, but I doubt that TS82 want so go there.) The disadvantage is that for a robust implementation you need to detect non-convergence and highlight it to the user.

I like the idea of the iterative solution, but how do I implement it? Doesn't newton's method require that I know the derivative of my equation?

here's how to get excel to iterate

Go into tools/options/calcualtion and switch iteration on.

a1 =b1+1

b1 =a1


It'll count up to 100 all by itself.

Now all you have to do is figure out how to use that to solve N-R. I have done that a long time ago, it can be done.

In my experience goal seek is worth trying first but often won't work.

Still, the obvious way to solve it is in VBA, just start from a very low guess and increment it, when you go past zero halve the difference and go back the other way.

Solver is worth a go but again is badly crippled.



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

I think, or rather DERIVE FOR WINDOWS thinks, that the derivative with respect to E70 of the expression to the right of "Tasm=" is

(take a deep breath)
e67*e68*e70*(e30^2*h31^2*h32^2*h35^2*h36^2*h37^2*h38^2*h39^2*(j30-1)^2+h30^2*
(e31^2*h32^2*h35^2*h36^2*h37^2*h38^2*h39^2*(j31-1)^2+h31^2*(e32^2*h35^2*h36^2
*h37^2*h38^2*h39^2*(j32-1)^2+h32^2*(e35^2*h36^2*h37^2*h38^2*h39^2*(j35-1)^2+h
35^2*(e36^2*h37^2*h38^2*h39^2*(j36-1)^2+h36^2*(e37^2*h38^2*h39^2*(j37-1)^2+h3
7^2*(e38^2*h39^2*(j38-1)^2+e39^2*h38^2*(j39-1)^2)))))))*ABS(h28*h29/(h30*h31*
h32*h35*h36*h37*h38*h39))/SQRT(e28^2*h29^2*h30^2*h31^2*h32^2*h35^2*h36^2*h37^
2*h38^2*h39^2*(j28-1)^2+h28^2*(e29^2*h30^2*h31^2*h32^2*h35^2*h36^2*h37^2*h38^
2*h39^2*(j29-1)^2+e70^2*h29^2*(e30^2*h31^2*h32^2*h35^2*h36^2*h37^2*h38^2*h39^
2*(j30-1)^2+h30^2*(e31^2*h32^2*h35^2*h36^2*h37^2*h38^2*h39^2*(j31-1)^2+h31^2*
(e32^2*h35^2*h36^2*h37^2*h38^2*h39^2*(j32-1)^2+h32^2*(e35^2*h36^2*h37^2*h38^2
*h39^2*(j35-1)^2+h35^2*(e36^2*h37^2*h38^2*h39^2*(j36-1)^2+h36^2*(e37^2*h38^2*
h39^2*(j37-1)^2+h37^2*(e38^2*h39^2*(j38-1)^2+e39^2*h38^2*(j39-1)^2)))))))))+e
30*j30+e31*j31+e32*j32+e35*j35+e36*j36+e37*j37+e38*j38+e39*j39
(expire gently, then concatenate all the broken lines)

But this might not be all that helpful.

If you don't want to accept Prof Newton's help, you will need to devise another numerical scheme to solve your equation. This needs to take into account the nature of your equation, about which you have better knowledge than me.

One possible scheme follows, but it applies only if your equation and your ability to select starting values meet certain requirements. It is usually known as the bisection method.

Mentally simplify your equation to the conceptual form
F(x)=0
If you can select two starting points, say x=a & x=b such that
(1) F(a) and F(b) have opposite signs, and
(2) F has only one zero point in the range a to b (ie the function F crosses the x-axis only once in that range)
then the bisection method can be used. Note that under these conditions we know that there is one and only one solution in the range a to b. The bisection method takes advantage of this, by doing the following.

(3) Calculate F(a) and F(b). Assume that it is F(a) that is negative and F(b) that is positive. (Slight changes will be required to what follows if the actual situation is the reverse of this.)
(4) Calculate c=(a+b)/2 (the "bisection").
(5) Calculate F(c).
(6) If F(c) is negative, set a equal to c. If F(c) is positive, set b equal to c.
(7) We have now halved the range within which the single solution lies. Repeat, starting at step 4.

After n cycles of this, the range within which we know the solution must lie has been reduced by a factor of 2^n. Make n large enough and you have an "exact" solution.

The advantage of this method is that, provided the initial requirements are satisfied, it will always come up with an answer, and the answer's accuracy is always known.

That's enough to get you away.

Thanks... that's the direction that i'm headed. I would really like this 'code' to be hard wired in so I don't have to goal seek everytime I change some numbers.

Greg is suggesting that you have Excel's Tools/Options/Calculation do the iterating for you. That can probably be made to work. However it is not what I had in mind when I talked about "setting up an iteration scheme on your spreadsheet". I envisaged a rectangular wad of cells, with each successive column being a successive iteration. It could look like this
[tt]
F(a0) a0 a1 a2 … an
F(b0) b0 b1 b2 … bn
c0 c1 c2 … cn
F(c0) F(c1) F(c2) … F(cn)
[/tt]
where cn = (an+bn)/2
and an+1 = an or cn as appropriate
and bn+1 = bn or cn as appropriate

The number of columns you create in this rectangle determines the number of iterations you are performing. Another advantage of the bisection method is that you do not encounter numerical difficulties if you take your iterations too far (unlike some other numerical schemes). So the only price you pay for hard-wiring a lot of iterations is that you might slow down the calculation process. The rectangular table of intermediate values looks ugly, which is why you would probably put it in some distant corner of your spreadsheet.

Thanks everyone for all of your help. I've never done iterations schemes with Excel before. I am more accustomed to using Matlab for things like that.

I finally got it to work! This is my first post to these forums, and all I can say is that I wish I would have came here earlier! I am very impressed!

I ended up using the method that Denial provided. I had a bit of trouble with Excel's built in version of the iteration function.

Thanks again everyone!

all,

this may be a good example of where TK Player for Excel can be used to solve the equation/problem. the software operates in the background and seamlessly with excel.

see
something to ponder . . .

good luck!
-pmover

Yup, he solved the problem at zero expense in Excel, so obviously he should buy another package to do the job again.

Brilliant. Are you tksolver guys on commission?

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

greg,

yep, it is good that the problem was resolved w/ excel, but with effort, time, and help from others - which is acceptable.

no, i'm not receiving a comission from uts.

the thought was merely a suggestion or alternate way of solving lengthy or complex equations; not to sell (not allowed in this forum). "something to ponder . . ."

-pmover