Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Find Root of a polynomial

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    =B5-((1-p)*B5^(j+k)-B5^k+p)/((j+k)*B5^(j+k-1)-k*B5^(k-1))

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •