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

[David.Cheshire]

@Lupp

Harmonic, exponential or logarithmic approximations are not covered by linest.

LINEST does cover Harmonic, exponential and logarithmic approximations. LINEST doesn’t inherently “know” what problem it’s solving, it allows you to provide any set of independent variables that you think might fit your data. For example, if you think your data might fit a logarithmic model, create a column of ln(x) and use that as the x input data. LINEST will return the coefficients a and b of y = a ln(x) + b. The graphic shows an example of a ln(x) fit by LINEST.

[Lupp]

Please don’t paste images but attach your example files.

[David.Cheshire]

Sorry, I didn’t know.
LINEST ln(x) curve fit.ods (24.6 KB)

[fpy]

@Lupp

Please don’t [ONLY] paste images but [ALSO] attach your example files.

a preview is nice to have sometimes

[cwolan]

@David.Cheshire

For example, if you think your data might fit a logarithmic model, create a column of ln(x) and use that as the x input data. LINEST will return the coefficients a and b of y = a ln(x) + b.

This is in line with what the aforementioned Help topic “Trend Lines” says about the logarithmic regression equation.

@David.Cheshire

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.

@David.Cheshire

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.

In LibreOffice
=LINEST(B2:B12,A2:A12^{1,2}) as an array formula should do.
Cf. the attached file.

PolynomialRegression v2.ods (56.4 KB)

[David.Cheshire]

Thank you, this is exactly the answer to my initial question. Is this use of LINEST as array formula documented in the help files?

[Zizi64]

@David.Cheshire

Is this use of LINEST as array formula documented in the help files?

It is documented in the description:
https://wiki.documentfoundation.org/Documentation/Calc_Functions/LINEST

…and in the HELP. The Linest is located in the Array functions group:
https://help.libreoffice.org/24.8/en-GB/text/scalc/01/04060107.html?&DbPAR=SHARED&System=WIN

[cwolan]

Consider LO Release Notes 7.4:

• Automatic cell matrix expansion for formula expressions that return an array/matrix. For formula expressions entered into a cell that obviously return an array/matrix, the result cell range is expanded as if ⇧ Shift + Ctrl + ↵ Enter was used to close an array formula. The previous behaviour of only one cell being used to display only the top left element can still be forced by selecting/marking a single cell prior to input. tdf#149378 (Eike Rathke, Red Hat)

As of version 7.4, the LINEST function confirmed with only Enter may exibit the new behaviour.