Finding roots of non-linear equations in Excel

Oh wait! I just remembered.
In your equation...
tan(0.155x)-(0.42x)/(x^2-0.0438)=0

I wasn't sure what standard you wanted for the "Signs" of your variables.
I had it set up where you would enter -.0438, instead of .0438 like you
said.

Debug.Print MyFunction(0.155, 0.42, -0.0438, 1)
returns:
1.64156859310234
as expected.

Since you gave all "positive" numbers in your example
The constants (0.155, 0.42, and 0.0438)...etc

I rewrote it so the negatives in the equation that you gave is assumed.

Function MyFunction(a, b, c, guess)
'// = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Counter used to prevent cycling of small errors
'// Adjust counter limit of 10 if you think necessary
'// = = = = = = = = = = = = = = = =

Dim LastGuess As Double
Dim x As Double
Dim Counter As Long

x = guess
LastGuess = x + 1 ' Just make it different

Do While LastGuess x And Counter = 10
LastGuess = x
x = x + ((-c + x ^ 2) * (b * x + (c - x ^ 2) * Tan(a * x))) / (b * (c
+ x ^ 2) + a * (c - x ^ 2) ^ 2 * (1 / Cos(a * x)) ^ 2)
Counter = Counter + 1
Loop
MyFunction = x
End Function

Sub Testit()
Debug.Print MyFunction(0.155, 0.42, 0.0438, 10)
Debug.Print MyFunction(0.155, 0.42, 0.0438, 20)
Debug.Print MyFunction(0.155, 0.42, 0.0438, 40)
Debug.Print MyFunction(0.155, 0.42, 0.0438, 60)
End Sub

Now I get the correct solutions:
1.64156859310234
20.4011547204587
40.6034140054363
60.8495496932679

However, be aware of the signs in your constants.

HTH
Dana DeLouis

Dana DeLouis wrote...
...
Function MyFunction(a, b, c, guess)
...
x = x + ((-c + x ^ 2) * (b * x + (c - x ^ 2) * Tan(a * x))) / (b * (c
+ x ^ 2) + a * (c - x ^ 2) ^ 2 * (1 / Cos(a * x)) ^ 2)
...

I love Newton's method - it's so easy to break.

MyFunction(0.155,0.42,0.0438,SQRT(0.0438))

returns 0.209284495364592, but

=0.42*0.209284495364592/(0.209284495364592^2-0.0438)-TAN(0.155*0.209284495364592)

returns 7.30124E+12, which is a bit off of zero. Also,

MyFunction(0.155,0.42,0.0438,PI()/2/0.155)

returns 10.1341698502897, but

=0.42*10.13416985/(10.13416985^2-0.0438)-TAN(0.155*10.13416985)

returns -1.63246E+16, also a bit off zero.

Fact is Newton's method stinks in the neighborhoods of any poles of the
function in question. Your function needs to guard against that.


---
Message posted from http://www.ExcelForum.com/

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

"hgrove " wrote:

TCO wrote...
...
I should re-write the function slightly as follows:

tan(0.155x)-(0.42x)/(x^2-0.0438)=0

You have an interesting interpretation of 'slightly'.

I am trying to determine positive values of x where the left hand
side is zero. The number 0 satisfies the equation, but there are
other roots at 1.64, 10.4, 20.4, 30.5, etc. . . . I am looking for a
way of seamlessly integrating a root finding Macro into an Excel
spreadsheet that would find the roots to the above equation.
The constants (0.155, 0.42, and 0.0438) will be the only user-
defined values that change from run-to-run.

I have found root solvers on the net. Most all are stand-alone
apps, probably written in VBA. . . .

Why do you believe they're probably written in VBA? In my experience,
most people with the education and experience to know how to do
numerical programming don't use any dialect of BASIC if they can avoid
doing so. Where's the 'Numeric Recipes in BASIC' book?

. . . I need the code that could be transferred into my own
Macro (I assume this would be better than a "Function" because
the Macro can run in the background within a spreadsheet?) . . .

Nope. Any & all VBA code runs in foreground.

. . . which becomes part of my stand-alone worksheet.
...

For any continuous function, you need to find an x interval in which
the function of interest evaluates positive at one of its bounds and
negative at its other bound. Once such an interval has been located,
it's no big deal to find the zero. Binary search, Newton's method and
secant method could all be used (binary search is slowest but also
surest - Newton and secant methods could go off on tangents). The
tricky part is thus identifying the intervals, and it's nearly
impossible in general to guarantee that any interval that contains a
zero contains only one zero.

But if you're interested, see chapter 9 of either

http://lib-www.lanl.gov/numerical/bookcpdf.html

or

http://lib-www.lanl.gov/numerical/bookfpdf.html

depending on whether you prefer C or FORTRAN.


---
Message posted from http://www.ExcelForum.com/



I can re-write:

tan(0.155x)-(0.42x)/(x^2-0.0438)=0

as:

x=ATAN((0.42*x)/(x^2-0.0438))/0.155

which other than the values of the constants and variable designations (i.e., A1 vs. x) should have the same functional form as what I originally wrote. Sorry about the confusion.

Also, you are correct, the Numerical Recipes book is Fortran and C. I mentioned Basic only because my goal is to utilize Excel for an a-typical spreadsheet computation. Thanks for the LANL link to the current Fortran copy.

Tim

"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