Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
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/micro...?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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
<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 -- Biff Microsoft Excel MVP "Dave O" wrote in message ... 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/micro...?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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
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 "Dave O" wrote: 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/micro...?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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting occurrence of day of week AND hour of day
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 "T. Valko" wrote: <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 -- Biff Microsoft Excel MVP "Dave O" wrote in message ... 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/micro...?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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max value for each day, and hour of that occurrence | Excel Discussion (Misc queries) | |||
Counting occurrences in one column based on an occurrence in anoth | Excel Worksheet Functions | |||
Counting the number of cells within a certain hour. | Excel Discussion (Misc queries) | |||
Calculating a colmun to total a 40 hour work week | Excel Worksheet Functions | |||
Counting Occurrence of Text within Text in Cells in Range. | Excel Worksheet Functions |