How to Setup Regression Analysis to Update Automatically

Jacquie Nesbitt

October 14, 2016 18:16

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

Method 1

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)

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.

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)

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.

Mohamad
October 22, 2020 15:00 (Edited October 22, 2020 15:03)

Here is a similar discussion on "microsoft.com" site on setting up a regression model with an automatic update:

https://answers.microsoft.com/en-us/msoffice/forum/all/regression-analysis-tool-excel/30a20cd1-35d4-4258-8f83-027965fe55cf

0

Comment actions Permalink

Please sign in to leave a comment.