How to Setup Regression Analysis to Update Automatically

There are two different methods you can use to set up a regression analysis to run and update automatically.

Method 1:

  1. In the dependent variable column, put #N/A after the last observation about 20 rows or however many you want.
  2. In the independent variable columns, put #N/A at the end after the last observation for say 20 rows or however many you want.
  3. 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.

 

Method 2:

  1. 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".)
  2. 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.

Have more questions? Submit a request

0 Comments