0
$\begingroup$

I am curious about the Excel regression function and evaluation of training and test data. Unlike using a program like R or Python where you state the values for your test and training data, Excel does not have this option with its regression set up. Does this mean that it assumes 100% of the values selected for the independent and dependent variables are training data?

$\endgroup$
1
  • 1
    $\begingroup$ Yes. You can specify the array of rows used for training, and then test the result on the rest of rows you had left for testing manually. $\endgroup$ Mar 26, 2018 at 14:51

1 Answer 1

1
$\begingroup$

I'll elaborate on the first comment briefly.

When you run the regression model in Excel, be sure to select only that part of the data that you want to use as the training data set.  You can then generate the regression coefficients for the model.  Next, you will need to calculate the estimated values for the rest of the data (the test data set) manually.  From experience, one useful trick for this is to use the =SUMPRODUCT() formula, copy the coefficients to the top (or bottom) of each respective column, and then select the values in the row (using relative reference) and the values in this top row (using absolute reference).  E.g., say your independent variables are in columns A:D; you run the model and copy the coefficients to the first row of these columns, A1, B1, C1, D1; then for each row, you calculate =SUMPRODUCT(A10:D10,A$1:D$1)+ _int_, where _int_ is the intercept calculate in the regression output.

Hope this helps...happy to clarify as need be.

Addendum #1
As requested in the comments, here is an example using excel.  First, here is some code that can be saved as a *.csv file

,,x,y,z,w,
TRAIN,,1.062,0.32,-0.836,-0.088,
TRAIN,,-0.455,0.438,0.391,1.53,
TRAIN,,0.368,1.552,-1.069,0.414,
TRAIN,,-0.295,0.069,-0.658,1.007,
TRAIN,,-0.176,0.523,-1.215,-0.385,
TRAIN,,1.167,-1.756,0.883,0.349,
TRAIN,,-0.108,-0.796,0.117,-1.438,
TRAIN,,-0.083,2.001,-0.518,0.989,
TRAIN,,1.366,-1.55,-1.541,-0.532,
TRAIN,,2.077,0.558,-0.22,0.308,
,,,,,,
,(intercept),x,y,z,w,w-hat
,0.37884,-0.02445,0.398,0.44039,,
TEST,,0.584,-0.018,1.267,0.159,
TEST,,-0.558,-0.98,-0.841,-2.006,
TEST,,0.586,-0.733,-1.074,-0.774,
TEST,,0.957,1.811,0.616,1.638,
TEST,,-0.055,0.509,1.362,-0.935,
TEST,,1.032,1.131,-0.617,-0.312,
TEST,,0.38,1.056,-1.341,-0.307,
TEST,,1.296,0.755,0.438,0.412,
TEST,,-1.117,0.289,-1.203,0.009,
TEST,,0.333,-0.839,0.32,0.209,
TEST,,-0.537,-1.077,1.116,-1.31,
TEST,,1.095,-0.516,-0.87,0.051,
TEST,,0.153,-0.119,-0.745,-0.408,
TEST,,0.043,-0.395,-0.521,-1.787,
TEST,,-0.984,-0.477,-0.482,-0.476,
If you save this as a *.csv and open in Excel, you can copy the following formula
=SUMPRODUCT(C15:E15,C$14:E$14)+$B$14
into cell G15.  Then copy G15 down thru G29.

$\endgroup$
3
  • $\begingroup$ Thank you for the answer and for clearing up that the Regression function does not automatically run the training/test split. In the example provided should I assume that you ran regression on the 11th row down for the independent columns as you wouldn't want to include the 9 data points being used in the test data sample from the 2nd row (directly after the coefficients) to the 10th row? I don't hate to add more effort as you have been helpful, but would you mind a small csv with the format you are describing if my understanding is off? $\endgroup$
    – cphill
    Mar 26, 2018 at 16:43
  • $\begingroup$ I've prepared an Excel demo...but I can't find a way to share the file on this system...hoping one of the moderators will advise. $\endgroup$
    – Gregg H
    Mar 26, 2018 at 19:17
  • $\begingroup$ I've figured a "reasonable" work around...see the Addendum #1 in the answer above. $\endgroup$
    – Gregg H
    Mar 26, 2018 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.