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:25:01 -0700

"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
> >spread*** 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 spread***?) . . .
>
> Nope. Any & all VBA code runs in foreground.
>
> > . . . which becomes part of my stand-alone work***.
> ...
>
> 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 spread*** computation. Thanks for the LANL link to the current Fortran copy.

Tim