Graphing a large dataset

[CTM2012]

Hi,

I have a dataset containing 2 columns. The first is an hourly time series in the form m/dd/yyyy h:mm, which is one year long (1/1/2011 0:00 to 12/31/2011 23:00). The second column contains a numeric value for each hour. I want to create one graph (xy scatter with smoothed lines) for each day of the year - so each graph would have 24 values, one for each hour of the day. My question is - is there an efficient way to do this (perhaps using a macro or some other shortcut)? Or do I need to create all 365 graphs manually?

Thanks very much for your help,
Caitlin

[FDibbins]

wow 365 graphs is a LOT of graphs. do you need to see them all...are you printing them all...what if the reason for needing so many? my reason for asking, is that there is probably a way to create a single dynamic graph, that, when you enter a month, it displays the graph for just that month.

would something like that work for you?

[CTM2012]

Ok, that sounds like it might work. Although, graphing the data for an entire month would probably look too jumbled. Would it be possible to create a dynamic graph where you could enter the day, and the graph for that day would be displayed?

Thanks,
Caitlin

[FDibbins]

yes that sounds entirely possible, and would robably generate a much more meaningful chart. im thinking of something along the lines of a 31-day vlookup table for the chart, or something like that, where you enter a start and end day, and the table populates with data for the chart

see what you can come up with, and give me a shout if you need more help

[CTM2012]

Sounds good! But I've never used vlookup before, so if you could give me a bit more detail about how I'd set up that kind of table, it would be much appreciated. Thanks!

[Peter.M]

Hi there,

Is this what you need?

DateTimeVlookup.xlsx

[CTM2012]

Wow, that's perfect! Thanks so much!! Could I ask you for one more thing - we may need to compare multiple values over the same time period, so could you show me how to modify that sheet so there are 3 or 4 'value' columns, which can all be displayed on the same chart?

You're a life saver

[Peter.M]

DateTimeVlookup2.xlsx

The chart's getting a bit busy now! I presume it will look neater with real data though

[CTM2012]

Thank you so much! You just saved me hours and hours of time

[CTM2012]

Hello again,

I'm trying to do a similar thing with daily data instead of hourly data. I have a daily time series lasting one year (1/1/2007 to 12/31/2007), with numerical values for each day in columns. I want to be able to select a month and display the daily data for that month in a chart (i.e. 28, 30, or 31 values, depending on the month).

I tried modifying the hourly spreadsheet you sent me, but I'm not sure how to change the index and match formulas. I tried looking online but it's a bit tricky for a beginner like myself.

Also - I want to display the month and year as the chart title.

Could you please take a look at the sheet I've attached and tell me where I'm going wrong?

Thanks so much!Daily Template.xlsx