Need help deciphering LINEST function for Excel.

This is an array version of LINEST which allows polynomial regression. The ^{1,2,3,4,5,6} portion is raising each entry in the range F1:F709 to the powers of 1, 2, 3, 4, 5, and 6.

Suppose you have two ranges (G1:G709 and F1:F709 in your case) in mine:

The 6 order polynomial regression can be calculated as follows:

This is identical to what the charting tool does when you add a trend line and choose the option Polynomial, Order 6:

(Note that Moving Average is a separate option)

In my example Res are the known Y-values and T are the known X-values and I am displaying all the stats and suppressing the errors (blank items).

The formula in the purple cell and over the entire rectangle is:

{=IFNA(LINEST(Res,T^{1,2,3,4,5,6},,1),"")}

I have setup the formula to output all the statistics. You can see what each is here: https://support.microsoft.com/en-us/office/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60097&ui=en-us&rs=en-us&ad=us

The numbers in the first two columns above are:

HansV wrote:

the numbers such as -1.3379E-14 are so small that the actual numbers must be 0, and the result returned by Excel is a rounding error. So the result basically says that the data are approximated by a straight line y=-0.54712*x+1136.352

(joeu2004 will probably correct me)

Okay. But IMHO, the key point is: if the data is best represented by a straight line, or if that is what the user wants, we should use a bona fide linear regression, not the last two terms of a 6-deg polynomial regression.

I believe that the significance of the terms with infinitesimal coefficients depends on the data. Since the user did not provide all of the data, we cannot say one way or another.

But in general, the infinitesimal coefficients become more significant as "x" increases. In this example, note that -1.3379E-14 is multiplied by x^6.

Those two points are demonstrated by the following example.

The data in column L is the NAV for the Fidelity Corp Bond ETF (FCOR) for 170 regular trade days from 7/20/2020 through 3/22/2021. The historical data can be exported from finance.yahoo.com. Column J contains the regular trade day numbers 1 through 170.

The following shows the LINEST results for the 6-deg polynomial regression (A2:G2), as well as the LINEST results for the bona fide linear regression (F3:G3). Note the difference in the slopes, which are the coefficients of the x^1 terms in column F.

(Aside.... I show rounded coefficients for presentation purposes. Usually, I would show 15 significant digits.)

The two regressions are shown in the chart below. Note that the 6-deg polynomial does not approximate a straight line, despite (really because of) the infinitesimal coefficients.

Formulas:

A2:G2: { =LINEST($L$2:L171, $J$2:J171^{1,2,3,4,5,6}) }

F3:G3: { =LINEST($L$2:L171, $J$2:J171) }

In contrast, the chart below shows the 6-deg polynomial regression starting with various terms. For example:

f(x^1/6) = $F$2*x^1 + $G$2*x^0.

Note that the straight line determined by the sum of the last two terms -- f(x^1/6) in light orange -- is a poor approximation of the data, to say the least.

Also note that f(x^5/6) = $B$2*x^5 +...+ $G$2*x^0 in dark tan literally "falls short" of approximating the data for higher values of x on the right. That demonstrates that the infinitesimal coefficient for the x^6 term is not insignificant, for the purpose of a best-fit to the data.

Aside.... Likewise, a bona fide 5-deg polynomial regression is a better approximation of the data than f(x^5/6). The formula for those coefficients would be:

B4:G4: { =LINEST($L$2:L171, $J$2:J171^{1,2,3,4,5}) }

and a formula for the estimated "y" values might be =SERIESSUM(J2, 5, -1, $B$4:$G$4).

PS.... I hope my ad hoc notational shorthand does not confuse anyone. f(x^1/6), for example, is intended to be read as "function of (a series starting with) x^1 of 6 (i.e. the series starting with x^6)". It does not represent an arithmetic expression like "x^1 divided by 6" or "x to the 1/6th power".

It's the other way round: G1:G709 is the range with the y-values and F1:F709 is the range with the x-values.

F1:F709^{1,2,3,4,5,6} represents x, x^2, x^3, x^4, x^5 and x^6.

The result of this LINEST formula will be spread over 7 cells in a row. The first number is the coefficient of x^6, the second one the coefficient of x^5, etc., and the 7th and last number is the constant (intercept).

A simplified example:

The y-values in G1:G6 are the result of =1*F1^6+2*F1^4-8

The numbers in I1:O1 are the coefficients and intercept.

So if I were to use the LINEST function on my data I get this,

.

I went on Desmos.com and wrote out the 6th term polynomial but like I don't understand how the numbers are being outputted. Now for the first of the seven terms in column J how is -1.3379E-14 produced mathematically?