Making a Graph that shows Frequency based on time

[jamboc]

This is what I am trying to do.

I want to produce a column graph that on the X axis shows time slots for a 24 hour period i.e 00:00 to 00:59, 01:00 to 01:59 etc with the Y axis then showing each occurrence in each time slot.

The data I have is a list of times, e.g 11:10 11:49 14:32. So what I want is for excel to first count the number of occurrences from these times that they fall into a particular time slot, so from the data above there are 2 entries that would fall into the 11:00 to 11:59 slot and so on the graph would show 2 units in that slot and one unit in the 14:00 to 14:59 entry.

The idea for the graph is to see if a particluar time of the day is when something occurrs more often, but it dosent need to be quite specific to the minute more to the hour, but the data gained contains the time in full and needs to be kept this way for other reasons aswell.

Any ideas or examples would be greatly appreciated.

Regards

James

[Bryan Hessey]

This is what I am trying to do.

I want to produce a column graph that on the X axis shows time slots for a 24 hour period i.e 00:00 to 00:59, 01:00 to 01:59 etc with the Y axis then showing each occurrence in each time slot.

The data I have is a list of times, e.g 11:10 11:49 14:32. So what I want is for excel to first count the number of occurrences from these times that they fall into a particular time slot, so from the data above there are 2 entries that would fall into the 11:00 to 11:59 slot and so on the graph would show 2 units in that slot and one unit in the 14:00 to 14:59 entry.

The idea for the graph is to see if a particluar time of the day is when something occurrs more often, but it dosent need to be quite specific to the minute more to the hour, but the data gained contains the time in full and needs to be kept this way for other reasons aswell.

Any ideas or examples would be greatly appreciated.

Regards

James

The attached was setup recently to do this over a days period, if your data is for one day only then you would need columns B and C only and reduce the SumProduct accordingly to (in C3, then formula-fill)

=SUMPRODUCT(--(MOD($A$3:$A$53,1)<$B3)*(--(MOD($A$3:$A$53,1)>=$B2)))

Hope this helps
---

[jamboc]

Thanks for that, it was a great help. The data although over many days throughout a year, I am only concerned with showing at this time just the frequency the event occurred, i.e to see if the time between 14:00 and 15:00 has more occurrences than another period. Although I may increase the scope of this later to match it to partcular day of the week aswell. This to maybe see that even though 14:00 to 15:00 is maybe the most common time that on a sunday say 12:00 to 13:00 is actually more common. So I'll probably be asking for help again!

However one question, the data i have is something that i add to as these events occur and have left space on the sheet to continue adding more, the results from this formula are displayed on a seperate sheet. If i use the formula and put the range for the data including the blank spaces it generates more entries than really exist, i.e its counting the blank entries aswell, is there a way round this other than having to keep modify the formula every time i add another time? Not sure how clearly I have explained that.

Once again though thanks for your help!

Regards

James

[Bryan Hessey]

Thanks for that, it was a great help. The data although over many days throughout a year, I am only concerned with showing at this time just the frequency the event occurred, i.e to see if the time between 14:00 and 15:00 has more occurrences than another period. Although I may increase the scope of this later to match it to partcular day of the week aswell. This to maybe see that even though 14:00 to 15:00 is maybe the most common time that on a sunday say 12:00 to 13:00 is actually more common.

the modified formula will ignore dates, so you can delete columns D onwards, set your required time periods in B, and the results appear in C (I presume you are aware of Formula Fill down column C)

So I'll probably be asking for help again!

However one question, the data i have is something that i add to as these events occur and have left space on the sheet to continue adding more, the results from this formula are displayed on a seperate sheet. If i use the formula and put the range for the data including the blank spaces it generates more entries than really exist, i.e its counting the blank entries aswell, is there a way round this other than having to keep modify the formula every time i add another time? Not sure how clearly I have explained that. James

I have just done this for another OP, my response was

Do you mean that you want your chart Source Data to be variable depending on the number of columns used? -

if so, then you need, under trigger, to set

ActiveChart.SeriesCollection(1).Values = "=Sheet2!R1C1:R1C" & LastColUsed

(untested) or something similar.

Are you familiar with WorkSheet_Change etc?

Can I presume that you know how to count the columns required, and trigger these into the Data Range of your chart? (a Macro Record will help you a lot here, set the Data Series Range to two different settings, and check the macro).

If you need further help just re-post.

---

[jamboc]

Thanksagain for your great help, i'll get back to you once i have had a bit more time to experiment with your second suggestion, at least i've got the first bit cracked from what you suggested. Which is a great help and really appreciate your help.

Not sure if you'll have any suggestions for one other thing i want to do, I have an Image, its a jpeg/bmp of a VFR aviation map. The data I am collecting refers to points on this map, so the times and dates of these occurrences occur at particular points on this map. Is there a way that i can present this onto the map, so that a user either through excel or webviewer would be able to highlight over a pin point/button say or something similar, that would then enable them to see the data associated with this point from the data in the worksheet or even database if i used access. I know it sounds quite of a big ask, and maybe still outside my level of ability but just some ideas of ways that i could maybe achieve this for me to investigate further. I understand there is a buyable mapping function for excel2003, but as i understand it, it uses its own maps which isnt really any use as it needs to be specific to this particular type of map and image that i am using. The points dont need to be spot on perfect(although this would be a bonus maybe in the future) as the data that refers to the points of occurence isnt that accurate in the first place, more an estimate. At the moment I am just manually placing points on the image using image editing software, but obvioulsy limits what information can be displayed next to each point.

I just want to finish with, that for all the bad press the internet gets sometimes, coming here shows that for everybad internet thing there are more than double the amount of great people and things about the internet. It is so pleasing to see people like yourself willing to share their knowledge with others.

Regards

James

[Bryan Hessey]

Thanksagain for your great help, i'll get back to you once i have had a bit more time to experiment with your second suggestion, at least i've got the first bit cracked from what you suggested. Which is a great help and really appreciate your help.

Once you have used the column B & C it becomes very easy, especially with the clutter of columns D onwards gone.

Not sure if you'll have any suggestions for one other thing i want to do, I have an Image, its a jpeg/bmp of a VFR aviation map. The data I am collecting refers to points on this map, so the times and dates of these occurrences occur at particular points on this map. Is there a way that i can present this onto the map, so that a user either through excel or webviewer would be able to highlight over a pin point/button say or something similar, that would then enable them to see the data associated with this point from the data in the worksheet or even database if i used access. I know it sounds quite of a big ask, and maybe still outside my level of ability but just some ideas of ways that i could maybe achieve this for me to investigate further. I understand there is a buyable mapping function for excel2003, but as i understand it, it uses its own maps which isnt really any use as it needs to be specific to this particular type of map and image that i am using. The points dont need to be spot on perfect(although this would be a bonus maybe in the future) as the data that refers to the points of occurence isnt that accurate in the first place, more an estimate. At the moment I am just manually placing points on the image using image editing software, but obvioulsy limits what information can be displayed next to each point.

Regards

James

You can set a picture as the backdrop to your chart, see Format Plot Area and Fill Effects, Picture.

For charts, I think that if Andy Pope hasn't already done it, then it probably can't be done, see his site at

http://andypope.info/charts/piezeros.htm

and also see

http://www.peltiertech.com/Excel/Cha...html#CondChart


and the attached (which I found somewhere along the way) might provide a clue as to what you can do with a little help.

Good luck in your search for what is possible.
---