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
