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)

In this figure, we demonstrate the 1st method which append #N/A at the end of the input data (X and Y)

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)

In this figure, we demonstrate using Excel names in-place of the X and Y cell-range

As data becomes available, edit the named range to include the new rows and the regression will auto calculate.


Please sign in to leave a comment.

Was this article helpful?
0 out of 12 found this helpful