Chart events/incidents by day of week and time

It would help a lot if you include a sample file. It looks like what you want to do is a histogram. This can be made dynamic. A lot depends on what the source data looks like. The solution will probably involve excel tables (only because they know how many rows they have so any formulas based on them deal with exactly the amount of data they need) and probably a couple of helper cells to put times into "buckets" for plotting.

Here is some information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

Come back with a sample spreadsheet, and I'm sure someone will jump in to help.

Here is what I did. First I made the data into an Excel Table so that when you add new data, it automatically becomes part of the table and all the formulas that reference the table still work: they don't have to be changed.

The first task was to separate the times into “buckets.” I decided on 4 hour buckets. If you want something else, you can adjust the formula. To separate the time into 4-hour chunks, I used the formula =MROUND([@[incident time]],240/1440). There are 1440 minutes in a day, and I want to take them in 240 minute (4 hours) increments. MROUND (N,X) rounds N to the nearest increment of X.

Then I got the Weekday using the formula =WEEKDAY([@[incident date]]). This gives 1 for Sunday through 7 for Saturday.

I then made a composite: =[@Weekday]+[@Time]. Time is always less than 1 (since it is only part of a whole day) so theses numbers cycle from 1 to 7 and start back with 1.

Column H is for cosmetic purposes. We’ll get to it in a moment.

Columns L and M are where most of the work is done.

In Column L, I enumerated every 4 hours from Sunday Midnight 1 12AM to Saturday evening 7 8 PM

Column M has the formula: =COUNTIFS(Table_Data[Composite],L3) which counts the number of times an incident fell into the bucket.

Column K is cosmetic. =L3-INT(L3) this formula gets the time part for the bucket.

Column J is cosmetic. =IF(INT(L2)<>INT(L3),INDEX($H$2:$H$8,INT(L3),1),"") this formula gets the day part of the bucket and looks up what day of the week it is. To keep the chart from getting too cluttered, I only want to show the day once and not for every hour.

I then plotted J3:K44 and L3:L44.

There is a rounding issue. MROUND and adding 240/1440 are not coming out to the same number. One is coming out 6.333333333333330 the other is coming out 6.333333333333340. I changed the formulas to use Time(4,0,0) instead of 240/1440 and I get the same results.

I fixed the issue by truncating both values to 10 decimal places. I needed an additional helper column to make this work. The new column is column M and the countif formula works off it. You can hide columns L and N.

To change to some other time interval, In column D change this formula: =MROUND([@[incident time]],TIME(4,0,0))

In column L, change this formula: =L3+TIME(4,0,0) and extend it down as well as the formulas in columns N, J and K

You will also have to change the ranges that the chart references.

I changed another formula. The composite is =TRUNC(IF([@Weekday]+[@Time]>=8,1,[@Weekday]+[@Time]),10). What was happening is that MROUND was taking late Saturday evening hours and rounding them to the 8th day. I "recycled" it to midnight Sunday.