CODEWITHSUNDEEP
algorithmexcelinterpolationcurve-fitting
user1214135
user1214135

Reputation: 635

Excel Polynomial Curve-Fitting Algorithm

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

Upvotes: 4

Views: 22511

Answers (4)

user1214135
user1214135

Reputation: 635

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

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

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

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

Upvotes: 5

ElKamina
ElKamina

Reputation: 7807

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

Upvotes: 0

lori_m
lori_m

Reputation: 5567

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.

Upvotes: 3

duffymo
duffymo

Reputation: 308743

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.

Upvotes: 0

Related Questions