Re: [Pen-l] query: Excel

[Sandwichman <lumpoflabor@xxxxxxxxx>]

Or, easiest of all, send me the data file and $100 (if it me takes
more than an hour, I'll invoice you @ $100 an hour for the balance).

On 4/5/08, Robert Naiman <naiman@xxxxxxxxxxxxxxxxxxxxx> wrote:
> 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 <jdevine03@xxxxxxxxx> 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
>  >  <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
>


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