Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Introducing rows in between every observation for huge panel data sets.
This may be hard to explain, but here it goes.
I need to fix up some data I have for some econometric analysis. I have average years of schooling for every country in the world from 1960-2000, staggered every 5 years (i.e. 1960, 1965, 1970, etc. 9 observations total). So I need to introduce 4 empty rows in between every observation for each of the 130+ countries in my dataset so I can have continuous years from 1960-2000 (40 observations for each country). Then I will have to interpolate for the empty cells by taking averages (for example, if I have 1960 = 1 and 1965= 1.209, take (1.209 - 1)/5 = 0.0418, then replace 1961 observation with 1+0.0418 = 1.0418, then 1962 (1.0418+ 0.0418) and so on. Let me know if there is a macro way of doing these "somewhat simple operations". My data looks like this: country year tyr15 tyrf15 tyrm15 Afghanistan 1960 1 0.385 2.363 Afghanistan 1965 1.209 etc. .. 1970 .. .. Algeria 1960 .. Zimbabwe 1960 tyr15: average years of schooling for population age 15+ tryf15: '' '' females age 15+ trym15 '' '' male age 15+ I would have to interpolate for all three of these variables. Any kind of help would be extremely welcome. I'll be merging the corrected data into STATA after I'm done. If anyone knows how to do this in STATA, please let me know if it is easier (eg. running a loop). I have no background in programming, so I don't even know how to run the actual macro. So any instructions of how to do this operation would be very helpful. Thanks a lot. P.S. If anyone wants to see the data, I could send you the excel file so you can take a look at it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Introducing rows in between every observation for huge panel data sets.
Put this code in a standard code module and run it
Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = iLastRow To 3 Step -1 .Rows(i).Resize(3).Insert Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... This may be hard to explain, but here it goes. I need to fix up some data I have for some econometric analysis. I have average years of schooling for every country in the world from 1960-2000, staggered every 5 years (i.e. 1960, 1965, 1970, etc. 9 observations total). So I need to introduce 4 empty rows in between every observation for each of the 130+ countries in my dataset so I can have continuous years from 1960-2000 (40 observations for each country). Then I will have to interpolate for the empty cells by taking averages (for example, if I have 1960 = 1 and 1965= 1.209, take (1.209 - 1)/5 = 0.0418, then replace 1961 observation with 1+0.0418 = 1.0418, then 1962 (1.0418+ 0.0418) and so on. Let me know if there is a macro way of doing these "somewhat simple operations". My data looks like this: country year tyr15 tyrf15 tyrm15 Afghanistan 1960 1 0.385 2.363 Afghanistan 1965 1.209 etc. . 1970 . . Algeria 1960 . Zimbabwe 1960 tyr15: average years of schooling for population age 15+ tryf15: '' '' females age 15+ trym15 '' '' male age 15+ I would have to interpolate for all three of these variables. Any kind of help would be extremely welcome. I'll be merging the corrected data into STATA after I'm done. If anyone knows how to do this in STATA, please let me know if it is easier (eg. running a loop). I have no background in programming, so I don't even know how to run the actual macro. So any instructions of how to do this operation would be very helpful. Thanks a lot. P.S. If anyone wants to see the data, I could send you the excel file so you can take a look at it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Introducing rows in between every observation for huge panel data
I'm going to take your "...I don't even know how to run the actual macro..."
literally and add some info to what Bob put up. To get Bob's code into a standard module: Open the workbook. Press [Alt]+[F11] to open the VB Editor. From that menu choose Insert | Module. Copy and paste the code Bob provided into the code module and close the VB Editor. To run the macro use the Excel Menu: Tools | Macro | Macros and then choose the ProcessData entry in the list and click the [Run] button. " wrote: This may be hard to explain, but here it goes. I need to fix up some data I have for some econometric analysis. I have average years of schooling for every country in the world from 1960-2000, staggered every 5 years (i.e. 1960, 1965, 1970, etc. 9 observations total). So I need to introduce 4 empty rows in between every observation for each of the 130+ countries in my dataset so I can have continuous years from 1960-2000 (40 observations for each country). Then I will have to interpolate for the empty cells by taking averages (for example, if I have 1960 = 1 and 1965= 1.209, take (1.209 - 1)/5 = 0.0418, then replace 1961 observation with 1+0.0418 = 1.0418, then 1962 (1.0418+ 0.0418) and so on. Let me know if there is a macro way of doing these "somewhat simple operations". My data looks like this: country year tyr15 tyrf15 tyrm15 Afghanistan 1960 1 0.385 2.363 Afghanistan 1965 1.209 etc. .. 1970 .. .. Algeria 1960 .. Zimbabwe 1960 tyr15: average years of schooling for population age 15+ tryf15: '' '' females age 15+ trym15 '' '' male age 15+ I would have to interpolate for all three of these variables. Any kind of help would be extremely welcome. I'll be merging the corrected data into STATA after I'm done. If anyone knows how to do this in STATA, please let me know if it is easier (eg. running a loop). I have no background in programming, so I don't even know how to run the actual macro. So any instructions of how to do this operation would be very helpful. Thanks a lot. P.S. If anyone wants to see the data, I could send you the excel file so you can take a look at it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel graph 3 sets of data. 2 sets as lines 1 as column? | Charts and Charting in Excel | |||
VLOOKUP in large Data sets of more than 16384 rows | Excel Worksheet Functions | |||
Why not introducing worksheet for business students in MS EXCEL? | Excel Discussion (Misc queries) | |||
How do you use the explode and collapse panel to hide rows? | Excel Worksheet Functions | |||
checkboxes hiding or introducing new worksheets | Excel Programming |