## 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     www.energyefficientbuild.com.    Mar 7, 2013
user Somewhat C :(http://productforums.google.com/forum/?zx=dk50ymjgzrap#!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: https://docs.google.com/spreadsheet/ccc?key=0As849t1zRsg1dFIxSUkzNHZ1a3RpRUN1ZW55d0E1MkE#gid=0
------------------------------------------------------------------------------------------------------------------
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)

Note:
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