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

English
#1

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.

#2

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

#3

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.

#4

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.

#5

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.

#6

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

#7

Thanks for your answer. I know this can be done by graphing the data and fitting a trendline, displaying the trendline equation and then copying the trendline coefficients. I do this by taking a screenshot and running an OCR to get the coefficients that I can paste into the spreadsheet. But it’s not an elegant way of getting the coefficients. To show the coefficients, the graph is following an algorithm in the background using LINEST to extract them from the data. It would be nice if the same algorithm was implemented as a spreadsheet function.

#8

Did you try LINEST() meanwhile?

#9

Hi Lupp, yes, I did try LINEST() and it works, of course. Now I have tried it, I will probably use it again, but having to create the column of x² values for a second order polynomial fit makes a mess of my tidy spreadsheet.

I found the explanation of LINEST in the Help section pretty well impossible to understand, and it doesn’t seem to cover using LINEST for polynomials at all. Perhaps we could work on a better version?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.