Is there a function for the best-fit polynomial for array of data?

[David.Cheshire]

I wish to use a function to analyse an array of data with x values in one column and y values in a second column and return the coefficients of an nth order polynomial that is the best fit to that data.

I have read all the articles in this forum and other places online and I still don’t understand whether there is such a function in Calc. Some seem to say no, others seem to say yes.

I know this can be done by graphing the data and fitting a trendline, displaying the trendline equation and then manually copying down the trendline coefficients, or taking a screenshot and OCRing it to get the coefficients. But this is not very satisfactory.

I hope someone will tell me what the function is called and where I can find directions for using it.

[Villeroy]

LINEST
https://forum.openoffice.org/en/forum/download/file.php?id=20612

[David.Cheshire]

That is not really what I was looking for. It is also not obvious from the example how the array function is entered; when I try to edit it, a box pops up that says “You cannot change only part of an array”. Are there instructions for using this method in the Calc Help pages? Or somewhere.

In Google Sheets, the function =ArrayFormula(LINEST(B2:B12,A2:A12^{1,2})) returns an array of coefficients for a second order polynomial directly without the user having to create the column of x² values. Or columns of x³ etc for higher order polynomials.

[robleyd]

From the Help:

LINEST returns a table (array) of statistics as below and must be entered as an array formula (for example by using Ctrl + Shift + Return rather than just Return).

The link also has information on editing, copying and adjusting array formulae.

[David.Cheshire]

Thanks for the link, but I must be misunderstanding something. The description of LINEST at the link says “Returns a table of statistics for a straight line that best fits a data set” but I am asking about polynomials (curves) that fit data where the relationship is not linear.

[keme1]

Mostly same answer as @Villeroy provided, but in a slightly more graphic form.