Thursday, March 7, 2013

yogi_Count The Number Of Transaction By The Hour For Each Of The 24 Hours From The TimeStamp Log

                                          Google Spreadsheet   Post  #1069
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Mar 7, 2013
user Somewhat C :(!category-topic/docs/spreadsheets/lKCccEmuWzg)
Counting frequency of different times of day
I have some data that needs to be counted and I'm unsure how to do it the way that I want. Column A has date and time of occurrences. I need to count how many occurrences took place between:
0:00:00 - 1:00:00
1:00:00 - 2:00:00
2:00:00 - 3:00:00 
and so forth.
I have left sheet "data" as the data input only sheet and the sheet "Count" to do the counting on.
The date portion of this isn't important and can be ignored. I only care about counting the times. 

Any help on this would be greatly appreciated. The Spreadsheet in question is:
following is a solution to a bit more generalized problem 

1) my solution is an array formula that furnishes the range of hours and the related count of transactions in a 
    single formula 
2) I have shown the count for each of the 24 hours in Sheet1 itself;
    in my results, 24th hour is shown as hour 0
3) there is a difference between my computed results and Somewhat C's expected results ... so let us check those 
    to see where the problem might be
    (by the way I did run a simple check ... the TimeStamp column has 3247 dates in range A2:A3248 ... and the
     total number of transactions for all hours taken together in my case is 3247 -- which makes sense, but the
     count of transactions from Somewhat C's posted results for 3247 dates is 3835 -- which doesn't seem right ...
     but let us not jump the gun, let Somewhat C check it out and then take it from there)

there were over 3200 rows of data in the user's spreadsheet which I had originally used in this blog post ...
however, for this illustration, I have now reduced the number of rows to 40