Chart or Graph data based on hours

[vullkunnraven]

Question:

I would like to chart (pie or graph) sales based on the hour they occured.

In excel, I have column A - the time stamp, and column B, the sale. Note that each sale has its own unique time stamp.

Any idea how to express this visually? I don't mind rounding down to the hour.

Thanks!!!


<table style="border-collapse: collapse; width: 131pt;" width="175" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 83pt;" width="111" height="20">Time</td> <td style="width: 48pt;" width="64">Sale</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:02:37PM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9:52:09AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:10:15PM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">10:30:29AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">11:31:31AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4:40:41AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:28:48AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">7:08:34AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:04:07AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">11:50:01AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9:48:58PM</td> <td align="right">1</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 131pt;" width="175" border="0" cellpadding="0" cellspacing="0"><col style="width: 83pt;" width="111"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 83pt;" width="111" height="20">
</td><td style="width: 48pt;" width="64">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr></tbody></table>

 

[gardnertoo]

To gather up the sales for each hour, the following will work:

Book1
	A	B	C	D	E	F	G
1	Time	Sale		Start of Hour	End of Hour	Category Names	Sales this Hour
2	5:02:37 PM	1		12:00 AM	1:00 AM		0
3	9:52:09 AM	1		1:00 AM	2:00 AM		0
4	5:10:15 PM	1		2:00 AM	3:00 AM		0
5	10:30:29 AM	1		3:00 AM	4:00 AM		0
6	11:31:31 AM	1		4:00 AM	5:00 AM	4:00 AM	1
7	4:40:41 AM	1		5:00 AM	6:00 AM	5:00 AM	2
8	5:28:48 AM	1		6:00 AM	7:00 AM		0
9	7:08:34 AM	1		7:00 AM	8:00 AM	7:00 AM	1
10	5:04:07 AM	1		8:00 AM	9:00 AM		0
11	11:50:01 AM	1		9:00 AM	10:00 AM	9:00 AM	1
12	9:48:58 PM	1		10:00 AM	11:00 AM	10:00 AM	1
13				11:00 AM	12:00 PM	11:00 AM	2
14				12:00 PM	1:00 PM		0
15				1:00 PM	2:00 PM		0
16				2:00 PM	3:00 PM		0
17				3:00 PM	4:00 PM		0
18				4:00 PM	5:00 PM		0
19				5:00 PM	6:00 PM	5:00 PM	2
20				6:00 PM	7:00 PM		0
21				7:00 PM	8:00 PM		0
22				8:00 PM	9:00 PM		0
23				9:00 PM	10:00 PM	9:00 PM	1
24				10:00 PM	11:00 PM		0
25				11:00 PM	12:00 AM		0
Sheet1

Then you can graph it. The column F formulas allow you to create "blank" series names so that the gaps between data in a pie chart don't get labels. To gain the benefit, discard the labels Excel wants you to use, and instead use the XY Chart Label utility (which isn't just for XL type charts). If it were me, I think I would use a column chart instead. The image below is the result of graphing your sample data in both formats. The column chart gives you an instant feel for what time of day sees the bulk of the sales. The pie chart does not. Also, the pie chart encourages your audience to group the data falsely. It appears that 11am, 5pm, and 9pm form a group, just because their wedges are adjacent. In the column graph, it is more clear that those data points are separated by several hours, and should not be considered a group. You could achieve some better results with color selection, but in the column chart you don't have to work for it.

 

[latinmaxima]

I have a very similar issue in which I am needing the above example to display information where Column B would either be 7008, 7009 or 7018 to represent three inbound lines. For example:


A B
Hour Ext
0:14 7018
0:45 7018
1:30 7018
1:34 7008
1:35 7008
2:57 7009


I have everything else nearly the same as the example above but I can not make "Column G" display only if the equivalant of B is equal to one of those ext's (7008, 7009 or 7018).


Any sugestions?

 

[gardnertoo]

If you put the number 7008 in cell F1, then this formula can go in cell F2 and copy down: =SUMPRODUCT(--($A$2:$A$25>=$D2),--($A$2:$A$25<$E2),--($B$2:$B$25=F$1))

This will give you the number of 7008's in each hour. Do this for each Ext number, then sum the rows. Or don't sum, and graph as a stacked column so you can see that 7008 was a bigger or smaller share of the 8am calls, etc.

 

[latinmaxima]

Would this still be applicable for A:A (the entire column some days have 1500 calls and other days have 800 calls. I do plan on making a stacked graph to show each ext throughout the day. I just haven't really been in excel for a few years this deeply. Thanks for the help.

 

[gardnertoo]

You can use a formula that goes beyond the number of cells you're likely to find data in, but this formula returns an error when applied to an entire column. Try something like this small sample:

Book1
	A	B	C	D	E	F	G	H
1	Hour	Ext		Start of Hour	End of Hour	7008	7009	7018
2	0:14	7018		12:00 AM	1:00 AM	1	0	2
3	0:15	7103		1:00 AM	2:00 AM	3	1	1
4	0:27	7008		2:00 AM	3:00 AM	0	2	0
5	0:39	7304		3:00 AM	4:00 AM	0	0	0
6	0:45	7018		4:00 AM	5:00 AM	2	0	0
7	0:58	7103		5:00 AM	6:00 AM	0	0	1
8	1:11	7304		6:00 AM	7:00 AM	0	0	0
9	1:21	7103		7:00 AM	8:00 AM	0	0	0
Sheet1

Not shown in this post is the data down to row 25, but the formula is looking all the way to row 2500. The empty rows just get ignored, and the right numbers get calculated.

 

[latinmaxima]

Thank you I will try this today!

 

[vullkunnraven]

Awsome - Thanks gardnertoo

 

[latinmaxima]

I went through this this morning as I was swamped yesterday and it comes up with 0 and an error for refering to empty cells. Can the difference be that I have other info in the cells between Column C Column J. Column D is actually Column J on my file and I have changed the appropriate reference cells accordingly in the formula but still am not getting the correct number. Thanks in advance for you help and time. If anything can I send you an example via email to help?

 

[gardnertoo]

Best to keep it in this thread so others can jump in. What you can do is download the Excel Jeannie utility and post a part of your sheet to the board.

When you say you're getting an error, do you mean an incorrect answer, or something like #N/A or #VALUE!