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)
Newton Excel Bach, not (just) an Excel Blog 
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,
=SUMPRODUCT(coefficients,x^powers)
1st derivative
=SUMPRODUCT(coefficients,powers,
x^((powers-1)*(powers>=1)))
2nd derivative
=SUMPRODUCT(coefficients,powers,
((powers-1)*(powers>=1)),x^((powers-2)*(powers>=2)))
3rd derivative
=SUMPRODUCT(coefficients,powers,
((powers-1)*(powers>=1)),((powers-2)*(powers>=2)),
x^((powers-3)*(powers>=3)))
etc. UDFs are slow. They should be avoided whenever possible in calculation-intensive workbooks.
LikeLike
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.
LikeLike
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)).
LikeLike
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?
LikeLike
Pingback: Daily Download 19: Solving polynomials | Newton Excel Bach, not (just) an Excel Blog