Chart showing busiest time of day

[glaupie]

Hi there,

I need some tips as to what the best way would be to represent the following in a chart:

I have a table showing each day of the month (1st, 2nd, 3rd to 31st) as headers, and 24 one-hour intervals as rows (00:00 - 01:00, 01:00 - 02:00, ..... 23:00 - 24:00). Each cell then shows the percentage of total faxes for the day, that was received during that interval.
At the bottom (below the 23:00 - 24:00), I then have a formula which determines the busiest time of the day, for each day.

I want to represent this (busiest time of each day) in a chart, but nothing I do seems to work properly. I'm not sure if I need to format the time-period differently, or which chart type to use.

My data table will thus look like this (transposed):

[TABLE="class: grid, width: 129"]
<tbody>[TR]
[TD]1st[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD]11:00 - 12:00[/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]4th[/TD]
[TD]11:00 - 12:00[/TD]
[/TR]
[TR]
[TD]5th[/TD]
[TD]14:00 - 15:00[/TD]
[/TR]
[TR]
[TD]6th[/TD]
[TD]11:00 - 12:00[/TD]
[/TR]
[TR]
[TD]7th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]8th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]9th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]10th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]11th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]12th[/TD]
[TD]16:00 - 17:00[/TD]
[/TR]
[TR]
[TD]13th[/TD]
[TD]11:00 - 12:00[/TD]
[/TR]
[TR]
[TD]14th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]15th[/TD]
[TD]14:00 - 15:00[/TD]
[/TR]
[TR]
[TD]16th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]17th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]18th[/TD]
[TD]16:00 - 17:00[/TD]
[/TR]
[TR]
[TD]19th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]20th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]21st[/TD]
[TD]10:00 - 11:00[/TD]
[/TR]
[TR]
[TD]22nd[/TD]
[TD]14:00 - 15:00[/TD]
[/TR]
[TR]
[TD]23rd[/TD]
[TD]11:00 - 12:00[/TD]
[/TR]
[TR]
[TD]24th[/TD]
[TD]12:00 - 13:00[/TD]
[/TR]
[TR]
[TD]25th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]26th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]27th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]28th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]29th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]30th[/TD]
[TD]13:00 - 14:00[/TD]
[/TR]
[TR]
[TD]31st[/TD]
[TD]16:00 - 17:00[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas?

Thanks,

Gerhard

 

[cyrilbrd]

Hi, what is the importance of 1st, 2nd, 3rd? why not a pivot table with the sum of occurrences per time period? could chart that as a line chart.
would that work?

 

[Andrew Poulsom]

To create any sort of chart you need to split the times into 2 columns like this:

Excel 2003
	A	B	C
1	1st	13:00	14:00
2	2nd	11:00	12:00
3	3rd	13:00	14:00
Sheet1

 

[glaupie]

Thank you - guess that is the way to go.

G