How is a polynomial trendline calculated in a graph?

[sutterp]

Could someone please clarify how Openoffice or Libreoffice calculates a polynomial trendline?

According to libreoffice documentation, https://help.libreoffice.org/Chart/Trend_Lines the last section states that linest(y_range,xrange,type,stats) returns the equation.

However, the numbers stated in the graph's trend line equation (coefficients and r^2) are vastly different to what linest over the same data range reports.

What am I missing

thanks

Peter

[Zizi64]

There are an extension for the polinomial regression (because the AOO Calc does not have such feature, the AOO can not calculate the polinomial regression by default.)
https://extensions.libreoffice.org/exte ... rend-lines

I just installed it (for my Libreoffice 5.3.5) , and I got a third set of the results. I suppose it is related to the numeric accuracy of the office suite... (or not...)


((As I can remember it, there was a bug in the regression of the Graph module, but (i think) it is fixed by now.))

[sutterp]

Which of the three results is the most accurate now?
This almost destroys my beliefs in mathematics!!! I learned that if one as a question mathematically, one will always get the same answer.
Which one is it? LINEST? or which one of the graphs? Only one can be mathematically sound.

[MrProgrammer]

I learned that if one as a question mathematically, one will always get the same answer.

You're not using mathematics. You're using floating point arithmetic, which is a close approximation but not exact. Differences can occur depending on the data and the algorithm being used to calculate the result. Regression analysis is a computation which tends to highlight the shortcomings of floating point arithmetic and algorithms.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[sutterp]

Thanks for that.
I realize that floating point arithmetic has its shortcomings, especially when dealing with mixed very small and very large numbers.
However, the discrepancy between what the trendline equation states and what linest reports are too big for my liking. To me it appears that linest might be the more accurate, but not knowing how the trendline equation is calculated makes it difficult to decide.

[Lupp]

It's not just floating point. Any arithmetic with limited precision (and that's next to any arithmetic above school grade 5 I suppose) has its shortcomings. That's a blessing in disguise. Otherwise our excessive belief in arithmetics would be even more dangerous.

On the other hand there are some aspects which always should be considered if 'Charts' seem to be the means of choice. This the more if 'Trend Lines' shall be used. Polynomial approximation has its basic limits, too.

See attached example (For LibO only; AOO charts are outdated with respect to scaling and Trend Lines.):