There are two different methods you can use to set up a regression analysis to run and update automatically.
- In the dependent variable column, put #N/A after the last observation about 20 rows or however many you want.
- In the independent variable columns, put #N/A at the end after the last observation for say 20 rows or however many you want.
- In the regression wizard, select the X and Y to include the rows with #N/A. (See Image Below)
As data becomes available, replace #N/A with values and everything will auto calculate.
- Define a named range for Y and X. (This can be done by selecting the range you would like to name, click the Formula tab in Excel, and then click "Define Name".)
- In the regression wizard, type the named range for X and Y into the regression wizard. (See Image Below)
As data becomes available, edit the named range to include the new rows and the regression will auto calculate.
Here is a similar discussion on "microsoft.com" site on setting up a regression model with an automatic update:
Please sign in to leave a comment.