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:

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

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?