Results 1 to 15 of 18
Thread: Regression (2000)

20010715, 16:46 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Regression (2000)
Anyone know if Excel has a feature that will automatically update a regression analysis if the underlying data changes? I'm getting tired of having to go back and rerun the analysis.
Thanks in advance,
Jeff

20010715, 18:39 #2
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
What type do you use ? For Linear regressions there is the LINEST function, for a polynomial there are some nifty tricks to 'link' the coefficients to a cell range.

20010716, 06:37 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
I just click on "Tools", "Data Analysis", and "Regression", then plug in my variable ranges.

20010716, 06:49 #4
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
cri is right. If you want to have the possibility to update the regression results when you change your data, you must use builtin functions like LINEST, TREND or GROWTH. On the other hand, if you still want to continue using the dataanalysis regression tool of the analysis toolpak, then you can make a reference to the analysis toolpak, and make a subroutine in VBA which has the Xinput and Yinput as arguments. Clicking a button, may then redo the analysis.

20010716, 09:34 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
But doesn't th xintercept change as well? It is my understanding that the LINEST, etc., functions, do not compute any changes in the xintercept, only in the slope. Am I correct?
Thanks,
Jeff

20010716, 11:02 #6
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
The LINEST function is an array function, meaning that you have to select an area of 5x2 cells first, then push the fx button (the function wizard) and select LINEST. Enter the Yrange, then the Xrange, define the Const as TRUE and define Stats as TRUE and then press CTRLSHIFTENTER to close the wizard dialog window. Yous should get a 5x2 output, containing the slope, intercept, and additional statistics concerning the goodness of fit (correlation coefficient, standard error etc.; go to the help and select LINEST for more details). If you change the data, X or Y, it doesn't matter, the output should change (actually, I checked it and it does change the intercept also).

20010716, 12:18 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
Thanks, Hans. I wondeer why you don't get all the related info when simply going through the steps I outlined aboveor do you have to highlight the 5x2 area for the results?

20010716, 12:34 #8
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
Yes, indeed, you have to select the 5x2 area (or highlight it) before you click the function wizard.

20010716, 12:41 #9
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
Actually, if you don't like array functions, there is another way to retrieve the individual elements of the 5x2 output, using the INDEX function.
e.g. assume the Xdata is in range D16 and the Ydata is in range E1:E6
the slope: =INDEX(LINEST(E1:E6;D16;TRUE;TRUE);1;1)
the intercept: =INDEX(LINEST(E1:E6;D16;TRUE;TRUE);1;2)
the explained variance (or Rsquare, the square of the correlation coefficient): =INDEX(LINEST(E1:E6;D16;TRUE;TRUE);3;1)

20010719, 18:30 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
Hans,
How about the other statistical outputs you get using the way I was doing iti. e., Tools, Data Analysis, etc. Can you give me a formula for, say, covariance, std. deviation, etc?
Thanks,
Jeff

20010719, 18:43 #11
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
You can get nearly the same statistics from the LINEST output as you obtain via the Tools >> Data Analysis >> Regression tool. Just go to the Help and search for the LINEST worksheet function. You'll see a matrix, with all kind of information, including the regression coefficients, Rsquare, the SSreg (= Sum of Squares of Regression), F, etc. You can use the formula INDEX to retrieve all the elements of that matrix.
e.g.
F=INDEX(LINEST(...), 4, 1)
SSreg = INDEX(LINEST(...), 5, 1)
The Help also briefly explains the meaning of these statistical parameters.

20010721, 12:02 #12
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
Hans,
I tried everything you suggested, but I can't seem to get it to work on the attached spreadsheed. The Yvalues should be from column C, and the X from column b. The purpose is to calculate the sensitivity of Mcf/Customer to Heating Degree Days (HDDs).
Thanks,
Jeff

20010721, 17:17 #13
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
As far as I can see, the reason for your problem is the missing value in row 23. The LINEST function does not allow missing values: you have to take out both the X and Y values in the column, e.g. by deleting the complete row or by copying the data to another column and delete all empty cells or cells that are not containing data.

20010721, 17:55 #14
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
This is what I get if I leave out that one missing data point:
slope = 0.015744119 = INDEX (LINEST(...), 1, 1)
intercept = 1.608763532 = INDEX (LINEST(...), 1, 2)
standard error slope = 0.000537824 = INDEX (LINEST(...), 2, 1)
standard error intercept = 0.300908984 = = INDEX (LINEST(...), 2, 2)
Rsquare = 0.90591489 = = INDEX (LINEST(...), 3, 1)
etc..
standard error Y = 1.855301725
F= 856.9520229
df = 89
SSreg= 2949.752683
SSresid = 306.3508596
In fact SSreg/(SSreg+SSresid) = Rsquare

20010722, 09:12 #15
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Regression (2000)
Still doesn't work for me. Whenever I type in the formula "=index(linest(c3:c94;b3:b94;true;true);1;1)" Excel responds that there is an error in my formula (specifically c94) and won't let me proceed. Where am I going wrongI'm beginning to pull my hair out! I have attached another worksheet that has values (as opposed to links).
Thanks,
Jeff