Secant Method

[jacamar21]

Looking for a code to solve the secant method.

xnew = xold1-(f(xold1)*(xold2-xold1))
-------------------------
(f(xold2)-f(xold1))

xnew needs to replace xold1, xold1 needs to replace xold2, and xold2 is discarded. The iterations are limited by a tolerance and a maximum variable. Any help on this would be very much appreciated. thank you

[Damon Ostrander]

Hi jacamar21,

Here is a VBA user-defined function (UDF) that implements the Secant method:

Function Secant(X0 As Double, X1 As Double) As Double

' Returns the root of a function of the form F(x) = 0
' using the Secant method.

' X1 is a first guess at the value of x that solves the equation
' X0 is a "previous" value not equal to X1.
' This function assumes there is an external function named FS that
' represents the function whose root is to be solved

Dim X As Double 'the current guess for root being sought
Dim Xold As Double 'previous guess for root being sought
Dim DeltaX As Double
Dim Iter As Integer 'iteration counter
Const Tol = 0.00000001 'convergence tolerance

Xold = X0
X = X1

'permit a maximum of 100 iterations
For Iter = 1 To 100
DeltaX = (X - Xold) / (1 - FS(Xold) / FS(X))
X = X - DeltaX
If Abs(DeltaX) < Tol Then GoTo Solution
Next Iter

MsgBox "No root found", vbExclamation, "Secant result"

Solution:
Secant = X
End Function

As you can see, you must provide a function FS that is the function you desire the root of. I used the following function for my test of Secant:

Function FS(X As Double) As Double
'Example function cubic equation
FS = X^3 - X - 1
End Function

and I used Secant to solve this by entering it into a cell like this:

=Secant(1.4,1.3)

where 1.4 and 1.3 are the two "previous" (guess) values for x. I got a result of 1.324718, which is correct.

[Jay Petrulis]

On 2002-05-21 12:07, Damon Ostrander wrote:
Hi jacamar21,

Here is a VBA user-defined function (UDF) that implements the Secant method:

Function Secant(X0 As Double, X1 As Double) As Double

' Returns the root of a function of the form F(x) = 0
' using the Secant method.

' X1 is a first guess at the value of x that solves the equation
' X0 is a "previous" value not equal to X1.
' This function assumes there is an external function named FS that
' represents the function whose root is to be solved

Dim X As Double 'the current guess for root being sought
Dim Xold As Double 'previous guess for root being sought
Dim DeltaX As Double
Dim Iter As Integer 'iteration counter
Const Tol = 0.00000001 'convergence tolerance

Xold = X0
X = X1

'permit a maximum of 100 iterations
For Iter = 1 To 100
DeltaX = (X - Xold) / (1 - FS(Xold) / FS(X))
X = X - DeltaX
If Abs(DeltaX) < Tol Then GoTo Solution
Next Iter

MsgBox "No root found", vbExclamation, "Secant result"

Solution:
Secant = X
End Function

As you can see, you must provide a function FS that is the function you desire the root of. I used the following function for my test of Secant:

Function FS(X As Double) As Double
'Example function cubic equation
FS = X^3 - X - 1
End Function

and I used Secant to solve this by entering it into a cell like this:

=Secant(1.4,1.3)

where 1.4 and 1.3 are the two "previous" (guess) values for x. I got a result of 1.324718, which is correct.

Hi Damon,

Nice job on this one.

Can you suggest a way to identify when a sequence starts diverging? It usually will be identifiable immediately, and if not stopped, will go to overflow or undeflow quickly.

What I have done in the past is store the prior result (DeltaXold, for instance) and if DeltaX is greater than DeltaXold, then go back to DeltaXold and "nudge" it a bit before resuming.

Any specific guidance to offer? I have a number of posted examples where this is done, but am not satified with the results thus far.

Thanks,
Jay