Evaluating higher order polynomials …

… and a better solution for quadratics.

Having written a better solver for high order polynomials, that raises a few other issues.  The error in the roots found by the RPolyJT was checked by simply substituting each root as the x value in: ax^n + bx^(n-1) + cx^(n-2) + …+e = 0.  The problem is that finding the difference between two similar large numbers significantly reduces the precision of the result.  A much better approach is known as the Horner Scheme, which uses Synthetic Division to arrive at the value of the polynomial without subtraction of the values raised to high powers.

This method has now been added to the Polynomial spreadsheet with the following changes:

  • RPolyJT has been modified to call the new function EvalErrorHC() to evaluate the error in the roots found.
  • EvalErrorHC may also be used as a UDF from the spreadsheet.
  • EvalPolyHC will evaluate any polynomial for a list of values of x (real or complex), which may take any value (i.e. not necessarily estimated roots).

For similar reasons, the standard quadratic formula will not give accurate results when b^2 and 4ac are large and of similar magnitude.  Better accuracy is given by a method included in the Fortran code used as the basis of the RPolyJT function.  The Quadratic function in the Polynomial spreadsheet has now been revised to use this method.

The revised spreadsheet may be downloaded (including full open source code) from:


Results of the new RPolyJT and EvalErrorHC functions are shown in the screen shot below:

Click for full size view

This entry was posted in Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

4 Responses to Evaluating higher order polynomials …

  1. Pingback: Linest, NPV, IRR and solving polynomials | Newton Excel Bach, not (just) an Excel Blog

  2. aleks bjork says:

    the usage of Jenk Traub method is not clear. I am not expert at using visual basic. but when i downloaded your excel sheet and wanted to solve a 5th order polynomial it wouldnt work. i am sure i am doing something wrong.
    i created the 6 coefficients as you state, however, when write the command it doesnt return the 5 roots that i want. i clicked ctrl shift enter as u stated but it doesnt happen.



  3. dougaj4 says:

    aleks – Do you get an error, or do you get just one value?
    If you get just one value you need to select a range with 5 rows and 2 columns, with your function cell in the top left corner, press F2 to enter “edit” mode, then press ctrl-shift-enter.

    See https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/ for more details about using array formulas.

    If you get an error, have you enabled macros? Do the examples in the spreadsheet work?

    If those suggestions don’t work could you send a copy of your spreadsheet to dougaj4 at gmail, or post the coefficients here.


  4. Pingback: Daily Download 19: Solving polynomials | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s