Why I get different coef values when using polynomial fit and compare to Microsoft Excel

Hi all;

I’m trying to fit several data to a polynomial using Math Numerics, for example this one:

double[] X = {3240.066852, 2981.202863, 2701.003929, 2390.936074, 2100.780834, 1820.581901, 1740.931443 }
double[] Y = {0.753, 0.740, 0.725, 0.708, 0.692, 0.678, 0.674}

  double[] coef = Fit.Polynomial(X, Y, 3);

the results coefficients are: 0.6513836656124895, -3.5408099319294885E-05, 3.6203701025291257E-08, -4.8139658971139951E-12

fitting the same data in Excel gave me this coefficient:
y = -0.00000000x3 + 0.00000004x2 - 0.00004318x + 0.65638305

they are similar, but the problem is that later when I’m trying to evaluate the polynomial function, I’m getting different results in the spread sheet and the code.

any ideas?
regards

There are different ways a polynomial fit can be computed, which can result in more or less accuracy. Numerics doesn’t actually have a specific algorithm for polynomial fitting but uses direct regression methods; you can choose the specific method with the optional DirectRegressionMethod argument, although in this case it seems to return exactly the same result with all methods.

How do you get the coefficients out of Excel? Maybe there is a loss of precision at this step (or already the the input). In the polynomial equation you’ve posted highest order coefficient is 0.0, which I suspect is not what Excel actually uses since for example with -5e-12 instead the fit gets better. However, the next to coefficients are also a bit different. Unfortunately this way we cannot actually compare how good the two fits really are, or which one would be better.

In LinqPAD (with it’s .Dump function):

double[] X = { 3240.066852, 2981.202863, 2701.003929, 2390.936074, 2100.780834, 1820.581901, 1740.931443 };
double[] Y = { 0.753, 0.740, 0.725, 0.708, 0.692, 0.678, 0.674 };

var pm = Polynomial.Fit(X, Y, 3, DirectRegressionMethod.QR);
pm.Coefficients.Dump();
double[] Ym = Generate.Map(X, pm.Evaluate).Dump();
GoodnessOfFit.RSquared(Ym, Y).Dump("r2 with Numerics (QR)");

var pe1 = new Polynomial(0.65638305, -0.00004318, 0.00000004, 0.0);
pe1.Coefficients.Dump();
double[] Ye1 = Generate.Map(X, pe1.Evaluate).Dump();
GoodnessOfFit.RSquared(Ye1, Y).Dump("r2 with Excel, exactly as given");

var pe2 = new Polynomial(0.65638305, -0.00004318, 0.00000004, -5.0e-12);
pe2.Coefficients.Dump();
double[] Ye2 = Generate.Map(X, pe2.Evaluate).Dump();
GoodnessOfFit.RSquared(Ye2, Y).Dump("r2 with Excel, assuming -5e-12");

image

Just out of interest I also quickly had a look at Octave (essentially MATLAB). Its polyfit function seems to agree with Numerics. So it seems to be Excel which is off.

I understand that there are different methods to fit a polynomial to a Data
I just wanted to be sure that I was doing the fit correctly with Math.Net, not missing anything and that the results were different because of that

I’ve output the coeff in excel with more precision:
y = -0.0000000000053676x3 + 0.0000000398822676x2 - 0.0000431823733612x + 0.6563830456803980

your explanation helped me a lot, thank you very much