1. ## 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 re-run the analysis.
Jeff

2. ## 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.

3. ## Re: Regression (2000)

I just click on "Tools", "Data Analysis", and "Regression", then plug in my variable ranges.

4. ## 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 built-in functions like LINEST, TREND or GROWTH. On the other hand, if you still want to continue using the data-analysis 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 X-input and Y-input as arguments. Clicking a button, may then redo the analysis.

5. ## Re: Regression (2000)

But doesn't th x-intercept change as well? It is my understanding that the LINEST, etc., functions, do not compute any changes in the x-intercept, only in the slope. Am I correct?
Thanks,
Jeff

6. ## 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 Y-range, then the X-range, define the Const as TRUE and define Stats as TRUE and then press CTRL-SHIFT-ENTER 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).

7. ## Re: Regression (2000)

Thanks, Hans. I wondeer why you don't get all the related info when simply going through the steps I outlined above-or do you have to highlight the 5x2 area for the results?

8. ## Re: Regression (2000)

Yes, indeed, you have to select the 5x2 area (or highlight it) before you click the function wizard.

9. ## 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 X-data is in range D16 and the Y-data 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 R-square, the square of the correlation coefficient): =INDEX(LINEST(E1:E6;D16;TRUE;TRUE);3;1)

10. ## Re: Regression (2000)

Hans,
How about the other statistical outputs you get using the way I was doing it-i. e., Tools, Data Analysis, etc. Can you give me a formula for, say, covariance, std. deviation, etc?
Thanks,
Jeff

11. ## 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, R-square, 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.

12. ## Re: Regression (2000)

Hans,
I tried everything you suggested, but I can't seem to get it to work on the attached spreadsheed. The Y-values 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

13. ## 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.

14. ## 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)
R-square = 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) = R-square

15. ## 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 wrong-I'm beginning to pull my hair out! I have attached another worksheet that has values (as opposed to links).
Thanks,
Jeff

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•