Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regressions with data in (changing) rows
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regressions with data in (changing) rows
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 -- Bernard Liengme www.stfx.ca/people/bliengme Microsoft Excel MVP remove CAPS in email address "Ying-Foon Chow" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regressions with data in (changing) rows
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 "Ying-Foon Chow" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regressions using dates | Excel Worksheet Functions | |||
How do I sort or filter rows without column A data changing? | Excel Discussion (Misc queries) | |||
running regressions in Excel 2003 | Excel Worksheet Functions | |||
Sorting 4 rows of data and changing the text of the highest value. | Excel Discussion (Misc queries) | |||
I used Lotus Help for regressions in MS Excel 99. Now what? | Excel Worksheet Functions |