Excel Polynomial Curve-Fitting Algorithm

Question

What is the algorithm that Excel uses to calculate a 2nd-order polynomial regression (curve fitting)? Is there sample code or pseudo-code available?

score 5 · Accepted Answer · answered Jul 26 '12 at 20:57

I found a solution that returns the same formula that Excel gives:

Put together an augmented matrix of values used in a Least-Squares Parabola. See the sum equations in http://www.efunda.com/math/leastsquares/lstsqr2dcurve.cfm
Use Gaussian elimination to solve the matrix. Here is C# code that will do that http://www.codeproject.com/Tips/388179/Linear-Equation-Solver-Gaussian-Elimination-Csharp
After running that, the left-over values in the matrix (M) will equal the coefficients given in Excel.

Maybe I can find the R^2 somehow, but I don't need it for my purposes.

score 3 · Answer 2 · answered Jul 26 '12 at 15:53

3

The polynomial trendlines in charts use least squares based on a QR decomposition method like the LINEST worksheet function ( http://support.microsoft.com/kb/828533 ). A second order or quadratic trend for given (x,y) data could be calculated using =LINEST(y,x^{1,2}).

You can call worksheet formulas from C# using the Worksheet.Evaluate method.

answered Jul 26 '12 at 15:53

lori_m

5,487
1
18
29

I know about the linest worksheet function. What I wanted to know was the algorithm that linest uses (or the chart with a 2nd order polynomial trendline). I figured it out and answered my own question below, http://stackoverflow.com/questions/11672385/excel-polynomial-curve-fitting-algorithm/11677949#11677949 – user1214135 Jul 27 '12 at 14:26
If you're only doing a quadratic trendline your links should suffice but the trendline (LINEST) algorithm actually implements QR decomposition for greater accuracy - it doesn't solve the "Normal Equations" directly as stated in the kb article. – lori_m Jul 27 '12 at 15:50

score 0 · Answer 3 · answered Jul 26 '12 at 15:13

It depends, because there are a lot of ways to do such a thing depending on the data you supply and how important it is to have the curve pass through those points.

I'm guessing that you have many more points than you do coefficients in the polynomial (e.g. more than three points for a 2nd order curve).

If that's true, then the best you can do is least square fitting, which calculates the coefficients that minimize the mean square error between all the points and the resulting curve.

Do you know of an algorithm for least squares fitting? I'd like to throw together some code in C#. — user1214135, Jul 26 '12 at 15:21

score 0 · Answer 4 · answered Jul 26 '12 at 16:31

0

Since this is second order, my recommendation would be just create the damn second order terms and do a linear regression.

Ex. If you are doing z~second_order(x,y), it is equivalent to doing z~first_order(x,y,x^2,y^2, xy).

answered Jul 26 '12 at 16:31

ElKamina

7,747
28
43

I interpreted 2nd order to mean y = c0 + c1*x + c2*x^2. Your idea of second order is fitting a surface, not a curve. – duffymo Jul 26 '12 at 17:11

Excel Polynomial Curve-Fitting Algorithm

4 Answers4