non-zero solution

  • Thread starter Thread starter A.A.A
  • Start date Start date
A

A.A.A

Hi,
I do not know whether i can use excel in this or not. but I am trying
to find the non-zero solution to the following equation:

0.6*exp(t*1.0986)-0.6=0.99994572*t

but i always get a zero solution. How could i obtain the non-zero
solution?I know that there is a non zero value of t satisfying the
previous equation.
Plz help,
A.A.A
 
A.A.A -

Here's one way.

Let A1 contain values of t, and initially enter 1 (or your choice of
starting value) in A1.

Let A2 contain the left side: =0.6*EXP(A1*1.0986)-0.6

Let A3 contain the right side: =0.99994572*A1

Let A4 contain the difference, which you want to be zero: =A2-A3

Choose Tools | Goal Seek (in Excel 2007, Data | What-If Analysis | Goal
Seek), and set cell A4 to value 0 (zero) by changing cell A1.

If you need more precision, choose Tools | Calculation, and specify a
smaller "Maximum change." (In Excel 2007, choose Office Button | Excel
Options | Formulas | Maximum Change.)

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
 
One way is to use Goal seek. I wouldn't trust myself with any other method;
others probably will.

In A1, enter the number 1. Insert t as a defined name in the name box.

In Tools>Options>Calculation tab, Iteration block, set Maximum change to
0.0000000000001

In B1, enter your formula, but bring everything to one side of the = sign:

=0.6*EXP(t*1.0986)-0.6-0.99994572*t


Tools>Goal seek> Set cell B1>To Value 0> By changing cell A1

I get 0.000000059503948

If you check by filling in your original formula you'll see it is correct
 
<If you need more precision>

Indeed you do. I was way off in my first attempt (slightly different from
yours).
Changing the precision had a vast impact on the outcome.
 
I'm afraid this brute force approach doesn't work here. If you increase the
precision even more, you just get closer and closer to zero as a solution,
until you reach Excel's precision limits.
I didn't find any start vales that worked better.
I'm afraid a more subtile approach (if there is any) is necessary.

Harlan?
 
<I didn't find any start vales that worked better.>

With 25 I found 0.712394124423968

I have no idea wheter that's a sensible solution.
 
A plot of the two functions (- 1 <= x <= +1 )on one chart shows they cross
at 0 and somewhere between 0.5 and 1.0
Solver gave me a result of x = 0.712431

Same with Goal Seek when starting value in A1 was 0.5
Happy to send a file if you wish
 
Niek said:
<I didn't find any start vales that worked better.>

With 25 I found 0.712394124423968

I have no idea wheter that's a sensible solution.
Click to expand...

Here is the equation re-written in Newton form to get a full
machine-precision solution.

0.712432795276095

Sub Demo()
Dim A, B, C
Dim J As Long
Dim G 'Guess
Dim Ng 'New Guess

A = 0.6
B = 1.0986
C = 0.99994572

Ng = 1
Do
G = Ng
Ng = A * (Exp(B * G) * (B * G - 1) + 1) / (A * B * Exp(B * G) - C)
J = J + 1
Loop While G <> Ng And J < 20
'// J is used to abort from any cycling
Debug.Print Ng
End Sub
 
Niek said:
<I didn't find any start vales that worked better.>

With 25 I found 0.712394124423968

I have no idea wheter that's a sensible solution.
Click to expand...

Here's a quick check via a math program.
Looks good to about 4 digits...

{a -> 0.6, b -> 1.0986, c -> 0.99994572}

(-(a*b) - c*ProductLog[-1, -((a*b)/(E^((a*b)/c)*c))])/(b*c) /. %

0.7124327952760949

- - -
Dana DeLouis
 
Thanks Dana,

I think Excel's limited precision plays a role here.
This equation seems to be very sensitive to even the last digits.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Dana DeLouis said:
Niek said:
<I didn't find any start vales that worked better.>

With 25 I found 0.712394124423968

I have no idea wheter that's a sensible solution.
Click to expand...

Here's a quick check via a math program.
Looks good to about 4 digits...

{a -> 0.6, b -> 1.0986, c -> 0.99994572}

(-(a*b) - c*ProductLog[-1, -((a*b)/(E^((a*b)/c)*c))])/(b*c) /. %

0.7124327952760949

- - -
Dana DeLouis
Click to expand...
 
Back
Top