Counting occurrence of day of week AND hour of day

[Javabumb]

Using Excel2007

I am using a dataset produced by a radar speed counter. The date field
appears as: m/d/yyyy h:mm

Each record also contains two entries for Peak Speed and Low Speed.

I want to create an array that holds the count for each day of the week
(1-7) and the hour of the day (0-23), resulting in 168 cells. I want to use
a THRESHOLD value to filter the day & hour values so that I can create a
chart that shows periods of significant speed violations for use by local law
enforcement.

Example; The day_hour range contains 50,000+ entries. I want to count the
number of entries in this range by matching the DAY of WEEK and the HOUR of
the DAY. I want to be able to count how many vehicles were detected on
Fridays at 4pm for example.

I have tried COUNTIF, IF, and everything else I can think of. I'm
frustrated, any help is appreciated.

--mark

 

[Dave O]

I generated the count of entries by weekday by hour by creating a
sample data set of datestamps in a4:a1029.

In cell d2:j2 I have weekday numbers 1 thru 7. In c3:c27 I have hours,
numbered 0 to 23. In cell d1 I have this formula:

=SUMPRODUCT(--(WEEKDAY($A$4:$A$1029)=D$2),--(HOUR($A$4:$A$1029)=$C3))

You can copy that formula and paste it into d3:j27. This is an
application of using SUMPRODUCT with a double unary operator as a
multi-conditional count formula. As you'll recall SUMPRODUCT
multiplies one list of numbers by another list of numbers, and sums
the results. The double unary operator converts the list to a 1 or a 0
in each list and then multiplies. When both conditions are true, 1
multiplied by 1 results in a count of 1. A 'false' return in either
condition returns a zero, which results in a zero when multiplied in
the SUMPRODUCT.

NOTE: in this example, weekday 1 = Sunday. You can change 1 to =Monday
if you'd prefer: you'd need to flip a switch in Excel.

Dave O
Eschew obfuscation

 

[Javabumb]

Dave, you have made my day, thanks a bunch. I set up the data and formula as
you outlined and everything suddenly fell in to place. I should be able to
add my "speed filter" to your function and get the final data that I'm
working toward. Though I am familiar with the sumproduct function, I'm no
expert by far. Where can I learn more?

Thanks again,

--mark

 

[Dave O]

Hi, Mark-
I wasn't sure how you wanted to include the peak and low speeds- if
you can post some sample data it would be easier to figure out. I'm
leaving for the day very shortly but if you get bogged down in
applying the peak and low speeds send me an email at cyclezen ATSIGN
yahoo daht calm. I check email there more frequently than I check
posts here.

SUMPRODUCT with the double unary operator is a powerful tool to keep
in your Excel arsenal- I learned about it at this usenet group:
http://groups.google.com/group/microsoft.public.excel.misc/topics?hl=en&lnk=srg

It amounts to a SUMIF, but allows you to many "ifs" instead of just
one- cool stuff.

Have a good weekend!

Dave O
Eschew obfuscation

 

[T. Valko]

<humor>

You'll regret this the day you get caught in a speed trap now that you've
told them the best time is to catch you! <VBG>

</humor>

 

[Javabumb]

I followed your sumproduct example to add a threshold speed that compares
against the Entering Speeds. That way by changing the threshold value I my
chart will update with the new values. I also converted your matrix table
into an array so that Excel would chart it as a single data item.

=SUMPRODUCT(--(WEEKDAY(Day_Hour)=$U3),--(HOUR(Day_Hour)=$V3),--(Enter_Speed

= Threshold))

By raising the threshold value I can see when drivers are driving fastest
and in time periods they are doing it. A child in this community I am
working for was struck and killed by a speeding driver. Several
interventions have been applied already, with the radar signs another
component in their attempts to slow people down as they pass through. The
radar sign displays different messages to drivers based on their approach
speed. I am analyzing the data from two stages of installation, one with
the radar capturing driver responses to existing posted speed signs and the
second to measure the effectiveness of the radar displays. A third,
unanticipated benefit of the data is that we may be able to detect patterns
of speeding that would enable local law enforcement to set traps for the
right times so the evil doers can be reined in. After a little more reading
on the tool you shared with me, I will go back to my first data sets and
analyze them for time-speed thresholds too.

Thanks again for helping do a good thing.

--mark

 

[Javabumb]

Fortunately for me I need a running start and a tall cliff to get my truck up
to the speed limit. I sometimes miss those carefree days of stomping on the
gas pedal, leaving some tread, and blowing off some doors. Now I'm afraid of
all the punks who drive like I used to ;-)

--mark