Regressions with data in (changing) rows

[Ying-Foon Chow]

I wish I could attach the Excel file illustraing my question below:

For each date, I have 10 observations of the dependent variable (Y1 to
Y10) to be regressed on two explanatory variables (X1 to X10 and Z1 to
Z10). The data are entered as follows:

Column A is the date (say, 250 days).
Columns B to K contain the Y1 to Y10 for each date.
Columns M to V contain the Z1 to Z10 for each date.
Columns X to AG contain one row of X1 to X10.

I know how to get the intercept and slope (using INTERCEPT and SLOPE,
or LINEST) if I just regress Y on X or Y on Z only. But I need the
intercept and slope coefficients of regressing Y on X and Z for each
date.

The problem is that the observations X1 to X10 are the same for each
date, while Z1 to Z10 are changing over the dates (just like Y1 to
Y10). So I am not sure if I need to write a macro for this, or there
is a way of "combining" X1 to X10 with Z1 to Z10 for each date, and
then I can just use LINEST to regress Y1 to Y10 on X1 to X10 with Z1
to Z10 for each date.

Hope I have described the question clearly. Thanks in advance for any
help.

Best regards,
Y. F.

 

[Bernard Liengme]

If your Zi and Xi columns were side-by-side there would be no problem - see
the example in Help dealing with Floor space etc.

Why not reconstruct the data on a second sheet with formulas like =Sheet1!A1
to make the z and x pairs side by side?

Or have I misread the question?
best wishes

 

[B. R.Ramachandran]

Hi,

You can use LINEST to fit your Y values to the function a*Z+b*X+c. But you
have to reformat the spreadsheet to accomplish that, but it will be tedious
to do it as a whole since you have about 250 sets of data. You may try the
following method.

Insert three blank rows above the existing first row (So the existing data
will move down by three rows; so A2 will become A5, ....).

In A1 enter the row number of the data set you want to analyze (e.g., 5 for
the first date)
In A2 enter =INDIRECT ("A"&A1) [This is just to indicate which date you
are analyzing).
Enter the following formulas in B1, B2, and B3.
In B1 =OFFSET($A$1,$A$1-1,COLUMN(A1),1,1)
In B2 =OFFSET($A$1,$A$1-1,COLUMN(A1)+11,1,1)
In B3 =OFFSET($A$1,$A$1-1,COLUMN(A1)+22,1,1

Select B1:B3 and autofill the formulas across in the next 9 columns (i.e.,
upto K1, K2, and K3). This will copy the Y, X, and Z data for the required
date in rows 1, 2, and 3.

Now you can use LINEST function. Select a block of three cells horizontally
(say M2,N2,O2) and enter the following array formula
=LINEST(B1:K1,B2:K3,1)
This will display the constant and the two coefficients, in the order a, b, c.

Now you can change the entry in A1 to 6, 7, ......250 successively, and the
M2,N2,O2 block will display the parameters for the corresponding data-sets.

Regards,
B. R. Ramachandran