Or, easiest of all, send me the data file and $100 (if it me takes more than an hour, I'll invoice you @ $100 an hour for the balance).
On 4/5/08, Robert Naiman <[EMAIL PROTECTED]> wrote: > I don't doubt that might well be a way to do this that you would find > more satisfying, but I would estimate that finding and learning how to > use the appropriate thing would take longer than "brute force." > > As to whether there is an easy way to convert the quarters into a > single time series, as President Clinton might have said, that all > depends on what you mean by "easy." > > Here's one way. Brute force. You're not going to like it. :) > > Insert a new column to the left of Q1. Label it "t" for time. > > In the first position of the new column, enter the number 1. Beneath, > enter, 5, 9, 13... until you have as many entries as in the column to > its right. An easy way to do this is: in the second cell, instead of > entering 5, enter ="1" + 4, where "1" is the cell reference for the > cell above that has the 1 in it. Copy and paste all the way down until > you match the column on the right. > > Copy the column of data for Q2, and paste that under the data for Q1. > > You see how we're going to create a single column of data. But it's > not going to be in the right order. That's what the new column is for, > to keep track of t, so we can reorder at the end. > > In the first open slot on the left, enter 2. below, enter 6, 10, > 14...which again you can do by entering ="2" + 4 as above in the next > empty slot, copying all the way down until you match. > > Copy the column of data for Q3, and paste that under the data for Q2. > > Enter 3, 7, 11... > > Copy the column of data for Q4, and paste that under the data for Q3. > > Enter 4, 8, 12... > > Now in the second column you have a single column of data, in the wrong > order. > > Copy and paste the first (t) column on top of itself, using paste > special/values, so you have all numbers instead of formulas. > > Now sort the two columns together, on the basis of the first column. > (data/sort) > > Voila. > > > On Sat, Apr 5, 2008 at 1:14 PM, Jim Devine <[EMAIL PROTECTED]> wrote: > > Thanks, Robert, but it's exactly that kind of "brute force" method I > > was trying to avoid. You'd think that a sophisticated spreadsheet > > program from a highly-talented, hip, and up-to-date company like > > Microsoft would have some sort of automatic function to do exactly > > what I want (convert a long list of monthly numbers into quarterly > > averages). But maybe Bill Gates' billions are a reward for providing > > some other useful service to humanity. > > > > Is there an easy way to convert a table with Q1 Q2 Q3 Q4 across and > > years down (that your "brute force" method produces) into a single > > (one column) time-series list? or is there a way to use the 4 quarter > > by N years table in a regression as if it were a single time-series? > > > > By the way, thanks for the following. I didn't know I could do that: > > > > > Now, copy and paste from that cell you've created to the whole column. > > > Excel automatically inserts the appropriate formula into each cell, > > > i.e. it adds the three entries to the left. > > > > > > > > On Sat, Apr 5, 2008 at 10:59 AM, Robert Naiman > > <[EMAIL PROTECTED]> wrote: > > > I apologize in advance if this answer is too dumb, but here's how I'd > > > do it. "Brute force," as the mathematicians say. > > > > > > If the data is a level, like jobs created, then the quarterly data is > > > just the sum of the three months. If it's an average, like > > > unemployment, then (unless you want to be really picky) the quarterly > > > data is the average of the three months. > > > > > > Either way, there's a simple formula that generates one cell of > > > quarterly data from three cells of monthly data. > > > > > > Suppose the former case, and the data looks like this: > > > > > > J F M A M J... > > > x y z > > > > > > insert a column where April is, pushing April one column to the right. > > > > > > In the first empty cell created, put "= x+y+z", where x,y,z are not > > > the actual numbers, but cell references. you can generate these > > > automatically by clicking on the respective cells as you are typing > > > your formula. > > > > > > Now, copy and paste from that cell you've created to the whole column. > > > Excel automatically inserts the appropriate formula into each cell, > > > i.e. it adds the three entries to the left. > > > > > > Now, insert a new column after June, September, and December. > > > > > > Copy the column after March and paste it into the three new empty > > > columns (three paste operations.) (Alternatively, from a single cell > > > in the first column, same effect.) Again, Excel automatically does the > > > right thing. > > > > > > Now, if you don't want the months anymore, copy the whole table and > > > paste it onto itself, using paste special/values. this replaces the > > > formulas you created with the actual values. > > > > > > Now you can safely delete all the columns corresponding to the > > > original months, leaving you with 4 columns of data, which you can > > > label Q1, Q2, Q3, Q4. > > > > > > This may seem involved, but actually the whole thing is a one minute > > > operation. I did this sort of think a zillion times when I was using > > > Excel to teach intro econ statistics. > > > > > On Sat, Apr 5, 2008 at 12:26 PM, Jim Devine <[EMAIL PROTECTED]> wrote: > > > > does anyone know how to easily convert monthly data into quarterly > > > > data using MS Excel? > > > > > > > > > > -- > > Jim Devine / "Segui il tuo corso, e lascia dir le genti." (Go your own > > way and let people talk.) -- Karl, paraphrasing Dante. > > _______________________________________________ > > pen-l mailing list > > [email protected] > > https://lists.csuchico.edu/mailman/listinfo/pen-l > > > _______________________________________________ > pen-l mailing list > [email protected] > https://lists.csuchico.edu/mailman/listinfo/pen-l > -- Sandwichman _______________________________________________ pen-l mailing list [email protected] https://lists.csuchico.edu/mailman/listinfo/pen-l
