Re: Finding roots of non-linear equations in Excel

From: TCO (TCO_at_discussions.microsoft.com)
Date: 08/04/04 

Date: Wed, 4 Aug 2004 10:27:03 -0700

"Dana DeLouis" wrote:

> Hi Harlan. Thank you for pointing that out! You're right. It appears that
> when one guesses smack on a pole, the slope is very steep. Therefore, the
> "next guess" for x (using the slope) points straight down at about the same
> x value. When the next guess is similar to the previous x, it appears that
> one "may" be on a pole. This is not fully tested, but here is my first
> attempt at a correction. I was trying to keep it a little simple. I got
> the Secant method to work, but then you have the problem of having a zero in
> the denominator some times in the equation. So, I don't know which is
> "easier."
> Anyway, here is my new attempt...not fully tested of course. :>)
>
> Sub Testit()
> Debug.Print Fx(0.155, 0.42, 0.0438, Sqr(0.0438))
> Debug.Print Fx(0.155, 0.42, 0.0438, WorksheetFunction.Pi / 2 / 0.155)
> Debug.Print Fx(0.155, 0.42, 0.0438, 12)
> End Sub
>
> Returns the 3 "nearest" solutions:
> 0
> 1.64156859310234
> 20.4011547204587
>
>
> Function Fx(a, b, c, guess)
> '// = = = = = = = = = = = = = = = =
> '// Dana DeLouis
> '// Counter used to prevent cycleing of small errors
> '// Adjust counter "Limit" of 15 if you think necessary
> '// = = = = = = = = = = = = = = = =
>
> Dim x As Double
> Dim L As Double
> Dim H As Double
> Dim t As Double
> Dim Counter As Long
> Dim LastGuess As Double
> Const d As Double = 0.000000000001
> Const Limit As Long = 15
>
> x = guess
> LastGuess = x + 1 ' Just make it different
>
> Do While LastGuess <> x And Counter <= Limit
> LastGuess = x
> t = c - x * x
> x = x-(t*(b*x+t*Tan(a*x)))/(b*(c+x^2)+a*t^2*(1/Cos(a*x))^2)
> Counter = Counter + 1
> If Abs(x - LastGuess) <= d And Counter = 1 Then
> 'Could be a pole!
> L = Fx(a, b, c, guess - 0.001)
> H = Fx(a, b, c, guess + 0.001)
> If Abs(L - guess) < Abs(H - guess) Then x = L Else x = H
> End If
> Loop
> Fx = x
> End Function
>
>
> Dana DeLouis
>
>
>

Dana,

You were correct to assume that form of the equation. Thanks for your help with this.

Tim

Loading