If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Chart frequency of date/time value...?
This is probably simple but frankly, Excel and math are not my strong
points and never were so please, I need some help. I have just one column that contains date/time values down the the second, i.e.: 11/6/2004 12:00:10AM 11/6/2004 12:00:24AM 11/6/2004 12:00:27AM I need to group this data somehow into ten or fiteen minute intervals and bar-graph the number of rows tallied per interval. Any help greatly appreciated! If anyone cares, btw, the data is actually from a firewall log and I need to determine traffic trends through the day(s). Thanks, -B |
#2
|
|||
|
|||
Joe -
This is a histogram. There are some examples he http://peltiertech.com/Excel/Charts/Histograms.html Here's how you'd go about it. Set up some times to define your intervals. For example, put 11/6/2004 12:00:00AM into cell B1. In cell B2, put the formula =B1+TIMEVALUE("00:10:00") for ten minute intervals. Copy cell B2, select from B3 down as far as you think you need intervals (say B13 for 2 hours), and paste. Select C1:C14 (one more cell than you have intervals: the last is for the count above the largest), in C1 type this formula, =FREQUENCY(A1:A20,B1:B13), and hold CTRL+SHIFT while pressing Enter to make it an array formula. A1:A20 is the list of times, so expand it as needed. If you do this right, Excel puts curly brackets around the formula: {=FREQUENCY(A1:A20,B1:B13)} Now you can select the data in B and C and run the chart wizard to make your chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Joe User wrote: This is probably simple but frankly, Excel and math are not my strong points and never were so please, I need some help. I have just one column that contains date/time values down the the second, i.e.: 11/6/2004 12:00:10AM 11/6/2004 12:00:24AM 11/6/2004 12:00:27AM I need to group this data somehow into ten or fiteen minute intervals and bar-graph the number of rows tallied per interval. Any help greatly appreciated! If anyone cares, btw, the data is actually from a firewall log and I need to determine traffic trends through the day(s). Thanks, -B |
#3
|
|||
|
|||
Great lead!!! Thanks...
On Wed, 10 Nov 2004 22:47:41 -0500, Jon Peltier wrote: Joe - This is a histogram. There are some examples he http://peltiertech.com/Excel/Charts/Histograms.html Here's how you'd go about it. Set up some times to define your intervals. For example, put 11/6/2004 12:00:00AM into cell B1. In cell B2, put the formula =B1+TIMEVALUE("00:10:00") for ten minute intervals. Copy cell B2, select from B3 down as far as you think you need intervals (say B13 for 2 hours), and paste. Select C1:C14 (one more cell than you have intervals: the last is for the count above the largest), in C1 type this formula, =FREQUENCY(A1:A20,B1:B13), and hold CTRL+SHIFT while pressing Enter to make it an array formula. A1:A20 is the list of times, so expand it as needed. If you do this right, Excel puts curly brackets around the formula: {=FREQUENCY(A1:A20,B1:B13)} Now you can select the data in B and C and run the chart wizard to make your chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Joe User wrote: This is probably simple but frankly, Excel and math are not my strong points and never were so please, I need some help. I have just one column that contains date/time values down the the second, i.e.: 11/6/2004 12:00:10AM 11/6/2004 12:00:24AM 11/6/2004 12:00:27AM I need to group this data somehow into ten or fiteen minute intervals and bar-graph the number of rows tallied per interval. Any help greatly appreciated! If anyone cares, btw, the data is actually from a firewall log and I need to determine traffic trends through the day(s). Thanks, -B |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Selecting Charts in a Macro | Herman Merman | Charts and Charting | 1 | August 18th, 2004 12:25 AM |
Chart menu visible property | Sandy V | Charts and Charting | 8 | May 17th, 2004 01:39 PM |
accessing chart objects via VB | CycleMark | Charts and Charting | 1 | October 27th, 2003 04:52 PM |
Styles for chart | Debra Dalgleish | Charts and Charting | 1 | October 3rd, 2003 12:27 PM |