Newtons Root Finding Method FOR/LOOP error

[BFlick11]

I'm having problems with the porblem stated below:

Write a VBA macro that uses Newton's method to solve for a root. The function should take 3 arguements: an intial guess for the root, the DeltaX to use for the numerical derivatives (use a central difference approximation), and a relative stopping criteria. The fucntion should return the root of the function,i.e. the value of x that satisfies f(x) = 0. Use the absolute value of the raltive change in two successive x values to test against your relative stopping criteria. For this problem, you don't need any other testing, e.g. number of loops or time taken - just relative stopping criteria (Hint: You obviously need to embed the iterations within some kind of loop)

Assume that the function is available in a VBA function termed myf(). myf() takes 1 arguemet: the x value where the function is to be evaluated. For example, if the function is 3x^2+2x-1, then assume that the following code is already available

Option Explicit
Function myf(x as double) as double
myf = 3x^2 + 2x-1
End Function

This is currently what I have:

Option Explicit
Sub Newtons()
Dim x As double
Dim s As double
Dim dx As double
Dim i as long
x=1
s=0
dx=2
For i=1 to 1000
c=x-(myf(x)/[(myf(x+dx)-myf(x-dx))/(2*dx)]
If x-c = s Then
End Function
End If
x=c
Next i
EndFunction

Thanks,
Brandon

[BFlick11]

correction:

c=(myf(x)/[(myf(x+dx)-myf(x-dx))/(2*dx)]

[BFlick11]

bumpppppppp

[TMS]

I have no idea what this is meant to do. It also does not deliver an output, hence I have included a debug.print statement to list the values in the loop. I've increased the loop to 5000 iterations, only because it didn't break out before a 1000. However, it still doesn't break out. I'm guessing you need to set the tolerance/variance a little wider ... but then, like I said, I have no idea what this is doing.

Please Login or Register  to view this content.

However, it is in a VBA format and it does loop.

Regards

[BFlick11]

Thats because S will never be 0

[TMS]

Not sure I understand your logic. You actually set s=0 in the initialisation phase prior to the loop.

x-c = s => x-c = 0 => x = c

So if x were ever equal to c, the loop would stop ... which it doesn't. Why have the test? Is the intention simply to run 1000 iterations? And then what? There's no output.

[dougaj4]

Here's what I changed:

The myf function should be:

Please Login or Register  to view this content.

The Newtons routine starts as a sub and ends with endfunction (which should be two words). I have made it a function, so you can call it from the spreadsheet as a User Defined Function (UDF).

Add declaration for c: Dim c as double

Set s to some small value, not zero: s = 0.000000001

the formula for c should be: c = x - (myf(x) / ((myf(x + dx) - myf(x - dx)) / (2 * dx)))

You need to check the absolute value of the difference between x and c: If Abs(x-c) < s Then

Then Assign the value of x to Newtons: Newtons = x

Then Exit Function, not End Function

Here's the final code:

Please Login or Register  to view this content.

Enter =Newtons() anywhere on the spreadsheet and it should display 0.3333, which is the right answer.

Some more stuff about Newton's Method with Excel here:

http://newtonexcelbach.wordpress.com...ial-equations/

[dougaj4]

By the way, with the quadratic function you are using, the value of Abs(x-c) converges pretty quickly, but with other functions (e.g. cubic) it will converge much more quickly if you use a much smaller value for dx (e.g. 0.00001)

If you get the function to return the number of iterations, rather than the final value of c, you can have a play with it.


Also I should have assigned c as the return value (i.e. the final iteration), not x, which was still the previous iteration:

Please Login or Register  to view this content.

[mikerickson]

I think that either Solver or GoalSeek uses something like Newton's method.

Is this for a programming course?
Or is this a project that has to be done in Excel.

Excel VBA is a good learning languange but Excel is not a very good platform for mathematical analysis like this.
(RoundOff error creeps in quickly. For example, if A2 holds the formula =A1+TIME(1,0,0), and is dragged down, you'll get significant round off error before the end of the day.)

If its an Excel project, looking at Goal Seek and Solver would probably be a better approach than Newton's Method.

[dougaj4]

Excel VBA is a good learning languange but Excel is not a very good platform for mathematical analysis like this.
(RoundOff error creeps in quickly. For example, if A2 holds the formula =A1+TIME(1,0,0), and is dragged down, you'll get significant round off error before the end of the day.)

If its an Excel project, looking at Goal Seek and Solver would probably be a better approach than Newton's Method.

I disagree on all counts. Round off error is not a significant problem with VBA (no more than any other language that uses doubles), Excel is an excellent platform for numerical analysis, and Goal Seek and Solver are OK for ad hoc use, but if you want to solve a particular problem regularly it's better to write a specific routine in VBA.

Apart from that, it's a good simple but useful application for learning how to apply Excel+VBA to more complicated problems.

[snb]

I would do all the arithmetic in the function.
I can't assess the correctness of the formula.

Please Login or Register  to view this content.