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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Finding roots of non-linear equations in Excel
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/ |
#13
|
|||
|
|||
Finding roots of non-linear equations in Excel
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 |
#14
|
|||
|
|||
Finding roots of non-linear equations in Excel
"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 |
#15
|
|||
|
|||
Finding roots of non-linear equations in Excel
"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 |
|
Thread Tools | |
Display Modes | |
|
|
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 09:35 AM |
Excel Freezes at Start-Up (RegEdit to the rescue?) | [email protected] | Setting up and Configuration | 0 | October 22nd, 2003 12:38 PM |