Office 2007 does now have that function (it is actually a pretty sweet program) but none of the preceding ones do. You could make a pivot table but that seems rather like overkill.
> ----- Original Message ----- > From: "Jim Devine" <[EMAIL PROTECTED]> > To: "Progressive Economics" <[email protected]> > Subject: Re: [Pen-l] query: Excel > Date: Sat, 5 Apr 2008 11:14:27 -0700 > > > 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
