9

I have a Google Spreadsheets where I would like to compute occurrences of date ranges. I have a column date_utc+1 which contains almost random date data.

What I would like to do is put the date values into bins of six hours each, i.e., 12/5/2012 23:57:04 until 12/6/2012 0:03:17 would be in the first bin, 12/6/2012 11:20:53 until 12/6/2012 17:17:07 in the second bin, and so forth. Then, I would like to count the occurrence of those bins, such as: 

bin_from            bin_to                freq
-----------------------------------------------
12/5/2012 23:57:04  12/6/2012 0:03:17     2
12/6/2012 11:20:53  12/6/2012 17:17:07    19
...                 ...                   ...

Partial hints are very welcome as well since I am pretty new to spreadsheeting.

pnuts
  • 17,883
  • 5
  • 55
  • 103
grssnbchr
  • 271
  • 4
  • 8

2 Answers2

6

I think I've got the solution for you. It's not done all automatically, but it will result in your proposal:

  • In your file the date is written as MM-DD-YYYY. I made the assumption that the date range covers december and NOT random dates throughout the year. Therefore I had to re-arrange the date format to DD-MM-YYYY.
  • As a guidance I used the following formula to get the unique date range: =UNIQUE(ARRAYFORMULA(DATEVALUE(I2:I)))
  • In the FREQUENCY sheet I used this formula to calculate the first interval:
    ='Original Data'!J2+TIME(18;0;0)
    You need to choose the intervals wisely.
  • Copy down this formula untill the desired interval range is completed:
    =A3+TIME(6;0;0)
  • Use the FREQUENCY formula to calculate your result:
    =FREQUENCY('Original Data'!I2:I;A3:A)

See example file I've prepared: Frequencies

Jacob Jan
  • 23,322
  • 16
  • 106
  • 195
-1

Using the query language and a group by clause should do the trick.

Something like: =QUERY(A1:A509, "select count(B) group by year(A)") I will try to create a working query later today.

EDIT:

I have inserted a new column left of column A. This column has the formula : =year(A2) & "-" & minute(A2). This is just a quick test to create some ranges, this column should reflect the logic of the bins.

The formula =Query(A:E;"select B, count(A) group by B") will calculate the frequencies.

-- disclaimer, this is just an example, some tweaking is required to make it calculate the exact functionality mentioned in the question --

Jasper Duizendstra
  • 211
  • 2
  • 8