R-Squared & Linear Regression?

Question

I have one column of data for 3,500 days (Column A). I created a second column of numbers, 1 to 3500 (Column B).

In Excel, I then entered the formulae =RSQ(A1:A3,B1:B3) and =RSQ(A1:A4,B1:B4) etc. up to 100 days. As of today, the Linear Regression Trendline (LRT) that best fits my data is a 91-day LRT. It has an R-squared value of .9685.

To set outer bands for tomorrow's possible data, should I use "Standard Error" (SE) or "Standard Deviation" (SD)? After brief backtests, if I have a LRT with an R-squared > .9, my data normally reverses if it gets near 2 SE from that LRT unless the LRT is for a short time period (i.e., between 3 and 10 days). If I use SD, however, it appears that my data rarely gets anywhere near 2 SD from a LRT with an R-squared > .9--unless a short time period (i.e. 3-10 days).

If I should use SE, and I multiply the SE for the last 91 days by a z-value of 1.96, is the confidence interval (CI) 95%? If not, how can I find a CI of 95% (like 2SD) for SE value?

Answer 1

I'm wondering why you created the data 1-3500? I would think you would use the set of data available and figure the standard deviation and average. With that information, you would be able to predict tomorrows value at a certain confidence level. I forget is that the p test or the t test. its been a while since I took statistics.

Answer 2

Using linear regression, it doesn't really make sense to say that the trendline "has a value of 115". I recommend looking at a website that explains linear regression in some detail. However, the line is expressed as y = a + bx where y is the dependent variables, x are the independent variable, and a and b are the parameters of the fitted equation. This really gets too complicated to explain in detail here, so I recommend that you look at some detailed info on linear regression to get the answer.