Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    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 re-run the analysis.
    Thanks in advance,
    Jeff

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

  3. #3
    Bronze Lounger
    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.

  4. #4
    4 Star Lounger
    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 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. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #6
    4 Star Lounger
    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 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. #7
    Bronze Lounger
    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 above-or do you have to highlight the 5x2 area for the results?

  8. #8
    4 Star Lounger
    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.

  9. #9
    4 Star Lounger
    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 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. #10
    Bronze Lounger
    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 it-i. e., Tools, Data Analysis, etc. Can you give me a formula for, say, covariance, std. deviation, etc?
    Thanks,
    Jeff

  11. #11
    4 Star Lounger
    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, 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. #12
    Bronze Lounger
    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 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
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    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.

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

Page 1 of 2 12 LastLast

Posting Permissions

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