Re: Finding roots of non-linear equations in Excel

  • Thread starter Thread starter Dana DeLouis
  • Start date Start date
D

Dana DeLouis

Hello. I may not understand this correctly, but would zero be the only
value? You are trying to find a value of A1, where A1 = Atan(...A1...).
What I am thinking is that ATAN varies from +- Pi/2, or +- 1.57. Divide
that by your 2, and the right-hand side can only vary from +- .785..
A quick plot shows that there are no values of A1, except zero, where these
two plots meet.

What's interesting is if you meant Tan, instead of ATan. Here, the plot
will go crazy approaching A1=4 because the denominator A1^2-16 approaches
zero.
Hope I said this right.
Dana DeLouis

TCO said:
I would like to find the first 20 or so POSITIVE roots of the following equation:

A1=ATAN((2*A1*4)/(A1^2-16))/2

I have the "iteration" option turned on in the Solver preferences. The
Click to expand...
first root is of course zero, however, I need only the next 20 or so
positive roots.
 
Dana DeLouis said:
Hello. I may not understand this correctly, but would zero be the only
value? You are trying to find a value of A1, where A1 = Atan(...A1...).
What I am thinking is that ATAN varies from +- Pi/2, or +- 1.57. Divide
that by your 2, and the right-hand side can only vary from +- .785..
A quick plot shows that there are no values of A1, except zero, where these
two plots meet.

What's interesting is if you meant Tan, instead of ATan. Here, the plot
will go crazy approaching A1=4 because the denominator A1^2-16 approaches
zero.
Hope I said this right.
Dana DeLouis


first root is of course zero, however, I need only the next 20 or so
positive roots.
Click to expand...

Hi Dana,

I should re-write the function slightly as follows:

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

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. If you plot this function in Excel, you can see them. 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. 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?) which becomes part of my stand-alone worksheet.

The first 50 or so roots of the above equation would then be used in other, very straightforward calculations.

Thanks for your interest and help,

Tim

Thanks for your help.
 
tan(0.155x)-(0.42x)/(x^2-0.0438)=0

Hello. Is the tan only on tan(0.155x) as written?

or did you mean everything? ...

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

Dana


I should re-write the function slightly as follows:

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

I am trying to determine positive values of x where the left hand side is
Click to expand...
zero. The number 0 satisfies the equation, but there are other roots at
1.64, 10.4, 20.4, 30.5, etc.

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

tan(0.155x)-(0.42x)/(x^2-0.0438)=0
Click to expand...

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. . . .
Click to expand...

Why do you believe they're probably written in VBA? In my experience
most people with the education and experience to know how to d
numerical programming don't use any dialect of BASIC if they can avoi
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?) . . .
Click to expand...

Nope. Any & all VBA code runs in foreground.
. . . which becomes part of my stand-alone worksheet.
Click to expand...
...

For any continuous function, you need to find an x interval in whic
the function of interest evaluates positive at one of its bounds an
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 an
secant method could all be used (binary search is slowest but als
surest - Newton and secant methods could go off on tangents). Th
tricky part is thus identifying the intervals, and it's nearl
impossible in general to guarantee that any interval that contains
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
 
Oh. Never mind. You meant the equation as you gave as that would give 1.64
as one solution.

Be aware that the solution you see of about 10.4 I believe is not correct.
What you most likely see on a plot is a jump from +Infinity to - Infinity.

Dana
 
hgrove wrote...
TCO wrote... ...
Click to expand...
...

Note that the TAN(...) term is a periodic, locally increasing functio
and that the rational polynomial term with x increasing from zero i
initially decreasing so returning negative values, hits a pole valu
when x = SQRT(0.0438), the square root of the constant term in it
denominator, and becomes a positive decreasing function thereafter.
believe all intersections of the graphs of the TAN term and th
rational polynomial are transversal, so there should be one real zer
in each TAN period except possibly when the pole of the rationa
polynomial corresponds exactly to one of TAN's poles. So much for th
bracketting problem
 
Here's my quick and dirty attempt. For some reason, the Newton method is
having a real hard time with the first one. It returns 1.61, when the
answer should be like you say of about 1.64. The others seem to be ok. I
really don't see "why" it is not working here. I don't see where it would
be a problem. Maybe the Secant method might be better here as Harlan said.
Again, I don't see why it is insisting it's 1.61, when I agree it should be
1.64. I'll try to look at it some more later.

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, 1)
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

Returns:
1.61524722584487
20.4011269580444
40.6034104656708
60.8495486405519

Again, I think your solutions of 10.4 & 30.5 are in error.

HTH
Dana DeLouis
 
Oh wait! I just remembered.
In your equation...
tan(0.155x)-(0.42x)/(x^2-0.0438)=0
Click to expand...

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
Click to expand...

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 th
function in question. Your function needs to guard against that
 
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 > said:
TCO wrote...
...

You have an interesting interpretation of 'slightly'.


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?


Nope. Any & all VBA code runs in foreground.

...

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.
Click to expand...

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 said:
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
Click to expand...

Dana,

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

Tim
 
Back
Top