• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting time series data for chart

C

CmdrWillRiker

New Member
I'm trying to clean up and present some data but am unfortunately not that great with Excel's charting functions (more familiar with STATA). I probably could find an answer to this online but I don't even know what to search for. What I have is data formatted in a chart as follows (all the way from 1982 to 2012, and with all 12 months obviously):

[pre]
Code: 
Year	Jan	Feb	Mar
1982	18047	17981	17857
1983	16705	16706	16711
1984	17630	17728	17806
1985	18009	17966	17939
1986	17686	17663	17624
[/pre]

I would like it to chart data in chronological order (12 entries per year). I can get Excel to graph separate lines for each year or each month (12 and 31 lines, respectively), but not one continuous line for the whole series. I can get what I want by manually copying and pasting each value but I have dozens of data sets that need the same treatment. Help!
 
Here's how I would do it. I don't promise it's the best method, but I do think it's easy to implement (although, I'll let you be the judge). If you want to plot this data as one series, then your end goal is to get everything into one line.


So, I would start by creating a new sheet in the same workbook. At the top of that sheet create four columns: ID, Year, Month, value.


ID is a helper column. First, we want to know how many data points we're going to have in total. If there's 31 years and 12 months, then we're going to have 372 data points (31 * 21). So in the ID column, we're going to just create a list from 0 - 371 (it's 371 because we start at 0 - we'll see why that works in a moment).


So under the ID column (A2, probably) type a 0. then below that, type a 1. Then select both of those and drag down until you fill 372 spots (cell A373).


On to the Year column. For this column you want each year to appear in a set of 12. So you want 1982 to appear 12 times, then 1983, and so on.... To acheive this, type this function in cell B2:


=1982 + INT(A2/12).


This function will start at 1982 and then add one year every 12 cells (as you drag down). Because we started with 0 in the ID column, we don't have to worry about adding or substracting one to that formula. It should work as is. Drag down.


Then in the Month column, type 'Jan' and 'Feb' into the cells C2 to C3. Then drag down to autofill.


Ok, here's the fun part. You're now going to lookup the information from your data sheet and put it into the Value column. I would use a formula like so:


=VLOOKUP(Sheet2!B2, [entire range of your data] , MATCH(Sheet2!C2, [just header row of your data],0)+1,0)


So for that first set of brackets, select you're entire data set (remember to hit F4 to create an absolute reference). For the second set, just select that top row that lists "Jan", "Feb." (F4 again.) Btw, above I add one because I assume when you selected that top row you're not including the leftmost cell next to Jan, which is likely blank. If you do include that, you won't want to add 1.


If all goes according to plan, that should turn all oo your data into one vertical line. Then, select the line and create your chart. If you want to add dates to the chart, consider making another helper column and using something like "=Date(1982, A2, 1)" and dragging down. You can format this new helper column to show only the month and year and have it appear as such on your chart.


Also, as I post this, I probably should have suggested using a Data Table. Oh well...
 
Hi,


Please see this file:


http://dl.dropbox.com/u/60644346/ChangingColumn%26RowNos.xlsx


Hope that helps,


Faseeh
 
You must log in or register to reply here.
Back
Top