Re: [Pen-l] query: Excel

["Jim Devine" <jdevine03@xxxxxxxxx>]

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