There is a way to avoid the "brute force" but it involves a bit of programming using the record and play macros function, which takes a little getting used to and some trial and error. Whether you want to take that route depends on how many iterations of the brute force method you need to avoid. To put it simply, you record one instance of the brute force method and then program the macro to repeat it as many times as you require.
On 4/5/08, Jim Devine <[EMAIL PROTECTED]> 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 > <[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 > -- Sandwichman _______________________________________________ pen-l mailing list [email protected] https://lists.csuchico.edu/mailman/listinfo/pen-l
