Creating a graph over time

**samanthab** · 02-13-2009, 08:39 AM

Hi i have alot of values that i would like to display in a graph over time.

The problem is, is that there are many different times (irregular intervals) that the value is recorded during each day.

whenever i create a graph and choose the x-axis format as the date i get a spike at each day, which is no good!

value Date Date Time
0 22/12/2008 12:33:35 22/12/2008 12:33:35
0 22/12/2008 20:53:25 20:53:25
9 23/12/2008 00:48:48 23/12/2008 00:48:48
0 23/12/2008 06:58:28 06:58:28
8 23/12/2008 07:46:28 07:46:28
0 23/12/2008 07:48:28 07:48:28
0 23/12/2008 10:14:49 10:14:49
0 24/12/2008 02:05:50 24/12/2008 02:05:50
7 24/12/2008 02:22:26 02:22:26
1 24/12/2008 04:28:46 04:28:46
0 24/12/2008 04:30:46 04:30:46
0 24/12/2008 20:53:21 20:53:21
0 26/12/2008 20:57:15 26/12/2008 20:57:15
2 29/12/2008 03:15:02 29/12/2008 03:15:02
0 29/12/2008 03:17:02 03:17:02
6 29/12/2008 05:23:02 05:23:02

The best option i have so far is using columns C and D from above for the x-axis... this works exactly how i want, the only problem is i have too much data for the xaxis to be readable.

So what i really want is maybe dates along the x-axis and when i hold the mouse over the graph it will say the time??

Any Ideas??

Cheers

**Cheeky Charlie** · 02-13-2009, 10:12 AM

What if you redefine the interval spacing on the x-axis?

CC

**samanthab** · 02-16-2009, 10:46 AM

Hi charlie, when i try to redefine the intervals.. formate axis--> scale the boxes are grayed out?

this is only works for me when i change the date format to a number...

you have any other ideas?

cheers

**Cheeky Charlie** · 02-16-2009, 11:25 AM

Hi Samantha,

This is expected, if you think about the way Excel deals with information: if you say "these are numbers, please divide them into intervals of 15" that makes sense, but "these are words, divide them into intervals"... what is 15 more than "tree"?

So basically, Excel needs to know that the data are numbers (dates are numbers, so this is OK); this means the program can cope with dividing them up for you. If you want to make these numbers look like dates to you and me you must then apply a format to these numbers (i.e. goes on top of the numbers to effect a certain look).

If you post your work, I might be able to help, I will discuss the steps I take so you can see how it works.

HTH

**samanthab** · 02-17-2009, 06:47 AM

Hi charlie, here is an example of the graph and the problems caused by having so much data (see attachement)

Graph.JPG

The only way i can read the time and dates is if i strech the graph.

as this makes the overall size/shape of the graph difficult to see i would like to avoid this!

so ideally i would like to remove the time values and keep the date, but its important to keep the shape of the graph as it is...

here is the same graph when i remove the times...
date.JPG

hope this makes more sense

**Cheeky Charlie** · 02-17-2009, 07:35 AM

Hi Samantha,

I think I understand the issue, what I would like is a copy of your workbook (not a picture...) so I can show you a solution.

CC

**samanthab** · 02-17-2009, 07:41 AM

oh Sorry

here you go...

sample.xls

its just a sample of the data

cheers

**Cheeky Charlie** · 02-17-2009, 08:00 AM

Ok,

As discussed, dates are numbers and in order to do anything numeric with them (summarise, ranges etc.) Excel needs to treat them as numbers. That means we need an x-y graph, not a line graph.

Because of the way times and dates are handled we don't need to separate the time from the date, so we only need two columns to build the graph.

Then all we need to do is define the scale and number format of the axis. Right click on x-axis:
Scale tab: the maximum and minimum should already be defined (make sure there is a tick in the box by them). Then - as I think you wanted a daily breakdown I changed the major unit to 1 (a day). To have weekly intervals, you would need a major unit of 7; decrease the minimum by integers if you want to define a specific start date for the axis - perhaps a Monday (39797?).
Numebr tab: I used a custom number format of dd mmm

I think this is now what you want

HTH