Small error in polynomial coefficients calculation

[JezShed]

Hi all,
I absolutely love using Softmaker compared to MS Office and it's my first "port of call" for every single job now. However I noticed a strange inaccuracy when calculating polynomial coefficients using the LINEST function. See attached file.

In case anyone is not familiar with polynomial coeffcient calculation, it allows you to calculate a curve that fits a set of data. So 2 pairs of numbers (x,y) can have a straight line fitted perfectly. 3 pairs of (x,y) numbers can have a simple 2nd order polynomial (quadratic) curve fitted perfectly. 4 pairs of numbers can have a 3rd order polynomial (cubic) curve fitted perfectly. And in my example, I have 5 pairs of x,y numbers that are used to create a 4th order polynomial curve.

In theory, if you create a 4th order polynomial from 5 pairs of x,y points, then the curve will EXACTLY lie on the points, so if you entered one of the x values into your shiny new polynomial then the EXACT correct y value will pop out.

I was working on some large datasets and noticed that I was getting small errors in my calculated curves, even when the x values exactly matched the values that were used to calculate the polynomial coefficients. In theory, there should be no error.

So I loaded the same spreadsheet into Excel and the error was much smaller, often zero.

The attached file is a vastly cut-down sheet that shows the problem. There's a simple table of 5 x,y pairs. There's the 5 polynomial coefficients that are calculated using the LINEST function. And there is a test area where we can test out the polynomial to see how close it can get to the original y values when inputting your desired x values.

In Planmaker 2021 and 2024, the error for the demo value is around 11ppm (parts per million). In Excel the error is almost zero.

I've noticed that the error comes from the coefficients that are calculated using the LINEST function. There are very very slight differences between the coefficients that Planmaker and Excel produce from the same set of x,y pairs.

Can anyone else reproduce this? Your thoughts are much appreciated.

[SuperTech]

Thank you for reporting this problem with the sample file. I am able to reproduce it and forwarded the details to our developer team.

[RKanarek]

Dear JezShed,

Just a short note to say the following:
a. I agree with your assessment on SMO vs. MS.
b. I found your post very interesting; thanks!
c. Thanks for having problems fixed before I encounter them!

Let's hope you have many more issues!