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
