Re: [Pen-l] query: Excel

[dsquared@xxxxxxxxxxxxxxxx]

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" <jdevine03@xxxxxxxxx>
> To: "Progressive Economics" <pen-l@xxxxxxxxxxxxxxxxxx>
> 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
> <naiman@xxxxxxxxxxxxxxxxxxxxx> 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 <jdevine03@xxxxxxxxx> 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
> pen-l@xxxxxxxxxxxxxxxxxx
> https://lists.csuchico.edu/mailman/listinfo/pen-l

>

_______________________________________________
pen-l mailing list
pen-l@xxxxxxxxxxxxxxxxxx
https://lists.csuchico.edu/mailman/listinfo/pen-l