Find Root of a polynomial

[DragonGuy]

Well I need to know if its possible to program a formula in Excel to return me the Root of a polynomial given a set of variables?
Example equation:
(1-p)*x^(j+k) - x^k + p = 0
where p,j,k are given I need to find the values for x.
Very though stuff I know but any help is greatly appreciated.

 

[Damon Ostrander]

Hi DragonGuy,

This form (f(x)=0) is a perfect situation for application of Newton's method (yes, it is named after Isaac Newton). You can investigate this in almost any calculus or numerical analysis text. I have implemented Newton's method for dozens of similar problems and it lends itself well to the form of a UDF in VBA and is very easy to implement.

 

[sann0638]

A blunt tool, using Newton-Raphson iteration. Cells are called p, j and k and the initial value for x is in B5. Paste this into B6, then copy it down a hundred or so times. Take the last value (in B100 or so). Could be done more easily in a macro, and only works if 0<p<1 (a probability?).

=B5-((1-p)*B5^(j+k)-B5^k+p)/((j+k)*B5^(j+k-1)-k*B5^(k-1))

 

[DragonGuy]

Thanks again for all the input. Damon can you elaborate on this the formula I would used the centax too. Thanks.
Sann00638 thanks for your detail explaination. Would you clearify to me as far as your meaning of Cell p,j,k does that mean I should set like A1 = p A2 = j A3 = k and what would I set B5 to be since I am setting my equation initially to 0 would I set B5 as 0 too. Thanks again for all your assistance. It is greatly appreciated.

Regards

 

[Damon Ostrander]

Hi again DragonGuy,

Okay, here is code for a UDF that uses Newton's method to solve your equation given the values for p, j, and k. Just stick this into a macro module and use it like this:

=PolyRoot(A1,B1,C1)

where A1 is the cell containing p, B1 is the cell containing j and C1 is the cell containing k.

You will notice that it has a variable F and a variable dFdX. You can make it solve any (other) equation in one variable by making F the f(x) that you want the root of, and dFdX is the derivative of F with respect to X. I chose to use the value of P as the initial guess for X, but that was totally arbitrary.

Function PolyRoot(P As Double, J As Integer, K As Integer) As Double

' Returns the root of the polynomial:

' (1-p)*x^(j+k) - x^k + p = 0

' Assumes J and K are integers, but can easily change this by
' declaring them as Double.

' Solution is based on Newton's method

Dim X As Double 'the root being sought
Dim F As Double
Dim dFdX As Double
Dim DeltaX As Double
Const Tol = 0.000000000001 'convergence tolerance
Dim Iter As Integer

X = P
If P = 0 Then GoTo Solution

'permit a maximum of 20 iterations
For Iter = 1 To 20
F = (1 - P) * X ^ (J + K) - X ^ K + P
dFdX = (1 - P) * (J + K) * X ^ (J + K - 1) - K * X ^ (K - 1)
DeltaX = F / dFdX
If Abs(DeltaX) < Tol Then GoTo Solution
X = X - DeltaX
Next Iter
MsgBox "No root found", vbExclamation, "PolyRoot result"
Solution:
PolyRoot = X
End Function

 

[paelinck]

Excel also has a powerful solver. You'll first have to install the add-in.
Marc