Need help deciphering LINEST function for Excel.

Question

Hello, so I have some data I am manipulating for a C# project. I am trying to calculate a 6 term polynomial and need to create a moving average and use regression to create this moving average. I was told by a previous worker for the company I work for who does not work here anymore to use

=LINEST(G1:G709,F1:F709^{1,2,3,4,5,6})

It's my understanding that G1:G709 refers to the known x data and F1:F709 refers to the known y data with the exponents referring to the terms (I think). Now I am kind of confused as to what actually is going on here. Like I want to write a function that replicates the LINEST function but idk what is actually going on per say.

If someone could help explain what is going on here that would be great.

Answer 1

Ahh that makes sense, with the data we have we are literally trying to get the line as straight as possible which explains why the values are so small except for the Y-Intercept of 884.815.

Answer 2

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".

Answer 3

Thanks, joe.