A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Finding roots of non-linear equations in Excel



 
 
Thread Tools Display Modes
  #1  
Old July 27th, 2004, 05:24 PM
TCO
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

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 first root is of course zero, however, I need only the next 20 or so positive roots.

Any ideas how to get this?
  #2  
Old July 27th, 2004, 09:43 PM
hgrove
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

TCO wrote...
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 first root is of course zero, however, I need
only the next 20 or so positive roots.


Unlcear what you want, but if you had read online help for Solver you
would have found out that Solver's Iteration parameter controls the
iterative procedure Solver uses to find zeros of functions and has
nothing whatsoever to do with specifying the number of zeros you want.

If you want multiple solutions, then the only way to get 'em is to
isolate them manually and use constraints to restrict Solver to those
ranges when isolating the zeros.


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

  #3  
Old July 27th, 2004, 10:50 PM
TCO
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel



"hgrove " wrote:

TCO wrote...
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 first root is of course zero, however, I need
only the next 20 or so positive roots.


Unlcear what you want, but if you had read online help for Solver you
would have found out that Solver's Iteration parameter controls the
iterative procedure Solver uses to find zeros of functions and has
nothing whatsoever to do with specifying the number of zeros you want.

If you want multiple solutions, then the only way to get 'em is to
isolate them manually and use constraints to restrict Solver to those
ranges when isolating the zeros.


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



I read the online solver pseudo-help. I saw that there was no specific mention of returning multiple roots. I saw the range restrictions. That is why I went to the Forum for other suggestions.

I thought there might be someone out there who has written a routine or something like that for automatic ranging and multiple root finding. I've see 'em for sale, and in Numerical Recipes (C, Fortran). This is definitely Excel for scientists and engineers, pretty a-typical stuff.
  #4  
Old July 28th, 2004, 07:16 PM
Dana DeLouis
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

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" wrote in message
...
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

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

Any ideas how to get this?



  #5  
Old July 29th, 2004, 06:36 PM
TCO
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel



"Dana DeLouis" wrote:

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" wrote in message
...
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

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

Any ideas how to get this?





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.
  #6  
Old July 29th, 2004, 07:16 PM
Dana DeLouis
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

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


snip

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.

snip


  #7  
Old July 29th, 2004, 07:46 PM
hgrove
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

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/

  #8  
Old July 29th, 2004, 08:08 PM
Dana DeLouis
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

Oh. Never mind. You meant the equation as you gave as that would give 1.64
as one solution.

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


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

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



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.



  #9  
Old July 29th, 2004, 08:41 PM
hgrove
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

hgrove wrote...
TCO wrote...

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

...

Note that the TAN(...) term is a periodic, locally increasing function
and that the rational polynomial term with x increasing from zero is
initially decreasing so returning negative values, hits a pole value
when x = SQRT(0.0438), the square root of the constant term in its
denominator, and becomes a positive decreasing function thereafter. I
believe all intersections of the graphs of the TAN term and the
rational polynomial are transversal, so there should be one real zero
in each TAN period except possibly when the pole of the rational
polynomial corresponds exactly to one of TAN's poles. So much for the
bracketting problem.


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

  #10  
Old July 29th, 2004, 09:07 PM
Dana DeLouis
external usenet poster
 
Posts: n/a
Default Finding roots of non-linear equations in Excel

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


"Dana DeLouis" wrote in message
...
Oh. Never mind. You meant the equation as you gave as that would give

1.64
as one solution.

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


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

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



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.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge help again DrB Worksheet Functions 4 June 20th, 2004 05:59 PM
Field code to pick up Excel sheet Lyndie Mailmerge 1 June 2nd, 2004 11:57 AM
Problems using Excel 97 worksheet in Excel 2002 plfoley Worksheet Functions 1 May 2nd, 2004 09:54 AM
Cant open excel files by double clicking it Maged N. Roshdy Setting up and Configuration 1 February 4th, 2004 10:35 AM
Excel Freezes at Start-Up (RegEdit to the rescue?) [email protected] Setting up and Configuration 0 October 22nd, 2003 12:38 PM


All times are GMT +1. The time now is 06:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.