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
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
Thanks again,
--mark
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
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>
--
Biff
Microsoft Excel MVP
"Dave O" <Cycl...@yahoo.com> wrote in message
news:391939f9-acf9-4b85...@x41g2000hsb.googlegroups.com...
=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
--mark