Multiple day's data on a chart
Posted by Ken Cohen on November 27, 2000 1:39 PM
Given data like this: (I hope it posts better than it shows here)
November 3rd November 4th November 5th
12:01:26AM 573 12:00:21AM 505 12:01:21AM 392
12:01:26AM 574 12:00:21AM 506 12:01:21AM 393
12:01:26AM 575 12:00:21AM 507 12:01:21AM 394
12:01:27AM 576 12:00:30AM 506 12:01:53AM 393
12:01:27AM 577 12:03:49AM 507 12:02:26AM 394
12:02:23AM 576 12:04:34AM 506 12:02:45AM 393
12:02:43AM 575 12:04:55AM 507 12:15:00AM 394
12:06:29AM 576 12:06:14AM 506 12:15:13AM 393
12:06:58AM 577 12:06:22AM 507 12:27:33AM 394
12:11:37AM 578 12:06:35AM 506 12:27:55AM 393
12:14:30AM 579 12:16:22AM 507 12:39:25AM 394
(It should look something like this)
a b c d
|----Date1-----||----Date2----||----Date3----| . . .
| Time |Value || Time |Value ||Time |Value |
| Time |Value || Time |Value ||Time |Value |
Where time runs from 00:00 to 23:59
Each day's data can vary in the time and number of points collected.
I want to create a chart where each day's data is displayed as a unique series across a single 24 hour time scale.
An XY (scatter chart seems to be what I want, but since the number of points, and times of the points doesn't match up each day, I don't get accurate results.
Any suggestions?
TIA,
ken....
Posted by Tim Francis-Wright on November 28, 2000 8:59 PM
I think that an x-y scatter chart will do what you want.
Set up a chart with the first day's set of data.
You'll want to manually specify the minimum and
maximum X values to be 0 (for 12:00:00 AM
and (essentially) 11:59:59 PM), and want to
format the X axis number format to be the
appropriate time format.
Then you can copy each column-pair (c2:dxxx;
e2:fxxx; etc.) and paste them onto the chart
as separate series.
When I tried this, Excel didn't complain if
there were blank cells in the added series;
the chart ignored them.
If you need to automate this, the macro recorder
should prove useful. I got the following code:
Range("A2:B20").Select ' change 20 to whatever you need
Selection.Copy
ActiveSheet.DrawingObjects("Chart 1").Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels _
:=False, CategoryLabels:=True, Replace:=True, NewSeries:= _
True
Range("C2:D20").Select
Application.CutCopyMode = False
Selection.Copy
' etc.
Good Luck!
