Evaluating Polynomial Functions

A polynomial function is a function of the form:
a + b*x + c*x^2 + d*x^3 …

and the derivative (the slope of the line at point x) of this function is given by:
b + 2c*x + 3d*x^2 …

The User Defined Function (UDF) =EvalPoly1() will evaluate any polynomial and its derivatives, and may be downloaded from: EvalPoly.zip

The UDF input is:
=evalpoly1(x,Coefficient range, No of derivatives)

Where x is the value the function is to be evaluated for, and Coefficient range is a single row range containing the function coefficients, in increasing powers of x.  If “no of derivatives” is zero the UDF returns a single value; otherwise it returns a single row array containing the specified data.  To view the array, select the number of cells required, with the UDF in the left hand cell, then press F2 followed by ctrl-shift-enter.

The download file now also includes a 2D array version of the function, which is much faster for large data sets:

=EvalPoly1A(xa, Coefficient range, No of derivatives)

Where xa is a single column range of x values.

Typical output for a quartic polynomial is shown in the screen-shot below:

Output of EvalPoly1 Function (click to view full size)

Output of EvalPoly1 Function (click to view full size)

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

5 Responses to Evaluating Polynomial Functions

  1. Harlan Grove says:

    First, the constants multiplied by the various powers of x are called coefficients, not parameters.

    This isn’t necessary. This can be done with SUMPRODUCT. Evaluated 4th order polynomial,


    1st derivative


    2nd derivative


    3rd derivative


    etc. UDFs are slow. They should be avoided whenever possible in calculation-intensive workbooks.


  2. dougaj4 says:

    Harlan – OK, paramaters are now coefficients, thanks for pointing that out.

    As for the nececessity of a UDF, well no it isn’t necessary, and the sumproduct formulas are very neat, but:

    1) it’s quicker to enter the UDF

    2) if speed is an issue the UDF can easily be re-written as a 2D array function that is quicker than the Sumproduct formula (now included in the download)

    3) My main purpose for writing the UDF was to serve as a convenient example in a future post on the Newton Raphson method, where the UDF will be called from another function rather than the worksheet.


  3. Harlan Grove says:

    Gettin’ picky, secant method is often preferred to Newton Raphson for 1 variable problems precisely because it just estimates differentials rather than evaluates derivatives. Fletcher Reeves and other conjugate gradient algorithms are usually superior in multiple dimensions.

    OTOH, if you want to find zeros of polynomials, pass the built-in IRR function the array of coefficients as if they were a cashflow. The 0th order coefficient needs to be adjusted so the polynomial equals 0. Then one zero of the polynomial would be given by =1/(1+IRR(coefficients)).


  4. dougaj4 says:

    Harlan – the end application I’ll be using it on is finding the deflection of beams on multiple non-linear spring supports. The derivatives will be found numerically. I’ll be looking at the secant method as well.

    Using the IRR function to find the roots of high order polynomials is a neat idea, I hadn’t seen that before. It seems to have a problem with roots that would require an IRR of less than -1. Any way round that?


  5. 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