An earlier post presented various methods for finding the roots of polynomial functions based on the use of linear interpolation. It is sometimes advantageous to use a quadratic interpolation function, and methods using this approach will be presented in this and following posts. The earlier functions and the new quadratic interpolation functions have been incorporated in the spreadsheet ItSolve Functions.xls.

Any three points on a plane (other than co-linear points) will define a unique quadratic curve of the form ax^{2} + bx + c = 0. The User Defined Function (UDF) FITQUAD returns an array of the three coefficients, a, b and c:

A = ((Y2 – Y1) * (X1 – X3) + (Y3 – Y1) * (X2 – X1)) / ((X1 – X3) * (X2 ^ 2 – X1 ^ 2) + (X2 – X1) * (X3 ^ 2 – X1 ^ 2))

B = ((Y2 – Y1) – A * (X2 ^ 2 – X1 ^ 2)) / (X2 – X1)

C = Y1 – A * X1 ^ 2 – B * X1

This quadratic equation may then be solved in the usual way to find the x values for which it evaluates to zero. The procedure for applying this method to evaluating the roots of higher order functions is:

- Select three known points on the function to be solved, in the region of the solution of interest, with at least one point on each side of the x-axis
- Fit a quadratic curve to these three points
- Find the root of the quadratic curve in the range of interest
- Find the y value of the higher order function at this x value.
- Replace the furthest outlying of the three trial points with this new point.
- Repeat until the error is acceptably small.

The screenshot below shows this procedure carried out on the spreadsheet, using the FitQuad function:

Click to view full size

The process is automated using the UDF QuadSolve():

Inverse Quadratic Interpolation with QuadSolve

### Like this:

Like Loading...

*Related*

Interesting analysis, although it does require more assumptions for convergence than Newton’s method. A next step might be to incorporate in Brent’s method which is popular in quant finance eg for finding implied volatility.

For a worksheet function approach to find the root you can try:

=IRR(LINEST(C13:C15,B13:B15^{1,2}))*1+1

LikeLike

Hi Lori.

Brent’s method is planned for the 3rd and final post in this series.

LikeLike

Suspected that’s where you might be heading, it’s not straightforward so looking forward to the final post.

Just one simplification you’re probably aware of: instead of finding the root of the quadratic y(x), you can solve for the inverse quadratic x(y) with y=f(x)=0 (cf. wikipedia). This just amounts to swapping x’s and y’s in the formulas posted and setting c=0 which should be a bit more efficient.

An equivalent for this is =TREND(B13:B15,C13:C15^{1,2},{0,0})

LikeLike

Lori – see latest post 🙂

https://newtonexcelbach.wordpress.com/2010/04/04/the-inverse-quadratic-method-2/

(and thanks for the TREND hint)

LikeLike

Pingback: Daily Download 21: Assorted Solvers | Newton Excel Bach, not (just) an Excel Blog