Plotting frequency variations in time

[jumpjack]

I downloaded the list of earthquake shocks in last 2 weeks.
I would like to plot how frequency of shocks changes in time (in a second instance, also depending on shock intensity).
I know how to use FREQUENCY formula in a matrix, but I can't figure out how to dinamycally change its scope.

I have 1000 rows; in first column I have date+time, in second column the magnitudo.
For one day I can have 1 or 1000 shocks, it is not known; how do I teach excel to consider only values between two dates to perform frequency calculation, in such a way I can change this range not by editing the formula, but by editing some cells? (So I can link a slider to these cells).

I know how to take into account a fixed number of rows, but as time between shocks is not constant, in 100 rows I can have 1 day or 1 hour range! I want to keep constant the time-range, not the rows-range.

 

[Hui]

Jumpjack

Firstly, Welcome to the Chandoo.org Forums

Can you attach a sample file with some data?

 

[jumpjack]

I need frequency evolution for last 2 columns.

25/08/2016 6.51    42,622    13,218    9,9    2,7
25/08/2016 6.38    42,786    13,202    9,6    1,2
25/08/2016 6.38    42,745    13,222    9    2,1
25/08/2016 6.36    42,78    13,159    11,7    1,6
25/08/2016 6.32    42,616    13,277    11,5    2,6
25/08/2016 6.22    42,739    13,171    10,8    1,8
25/08/2016 6.19    42,803    13,144    10,3    2,1
25/08/2016 6.15    42,698    13,239    10,4    1,4
25/08/2016 6.05    42,795    13,204    9,8    1,9
25/08/2016 5.58    42,62    13,226    10,4    2,2
25/08/2016 5.56    42,628    13,237    10,9    2,3
25/08/2016 5.51    42,745    13,255    9,1    1,7
25/08/2016 5.47    42,789    13,208    9,4    2,1
25/08/2016 4.22    42,764    13,227    10,3    2,2
25/08/2016 4.16    42,683    13,258    10,9    2
25/08/2016 4.13    42,615    13,294    6,8    2,9
25/08/2016 4.12    42,691    13,234    10,2    3,3
25/08/2016 4.03    42,578    13,239    10,6    2,2
25/08/2016 4.03    42,733    13,199    9,7    2,3
25/08/2016 4.01    42,761    13,203    9,8    2
25/08/2016 3.55    42,753    13,182    10,2    2,1
25/08/2016 3.52    42,709    13,203    1,4    1,8
25/08/2016 3.50    42,753    13,198    8,9    2,1
25/08/2016 3.49    42,736    13,197    10,1    2,1
24/08/2016 11.12    42,714    13,182    15,6    2
24/08/2016 11.06    42,807    13,156    10,4    2,3
24/08/2016 11.05    42,643    13,228    2,4    2,8
24/08/2016 10.58    42,737    13,231    5    1,1
24/08/2016 10.57    42,624    13,236    11,1    2,3
24/08/2016 10.50    42,736    13,179    11    1,8
24/08/2016 10.44    42,742    13,233    15,9    2,7
24/08/2016 10.41    42,752    13,107    7,1    2,1
24/08/2016 10.36    42,72    13,214    15,1    2,2
24/08/2016 10.32    42,603    13,233    11    3
24/08/2016 10.28    42,757    13,119    6    2,7
24/08/2016 10.24    42,604    13,305    10,1    1,8
24/08/2016 10.22    42,756    13,118    8,2    2,8
24/08/2016 10.19    42,739    13,236    8    2,5
24/08/2016 10.15    42,716    13,219    9,6    3
24/08/2016 10.11    42,788    13,18    7,1    2,2
24/08/2016 10.09    42,654    13,309    15,8    3,1
24/08/2016 10.06    42,744    13,179    10,5    3,2
24/08/2016 10.01    42,793    13,213    9,7    2,3
24/08/2016 10.00    42,71    13,236    4,2    2,4
24/08/2016 9.53    42,781    13,2    10,8    2,3
24/08/2016 9.49    42,637    13,242    10,8    2,2
24/08/2016 9.48    42,61    13,202    15,2    2,2
24/08/2016 9.45    42,61    13,286    14,4    2,5
24/08/2016 8.19    42,637    13,304    7,3    2,5
24/08/2016 8.19    42,737    13,162    10,7    2,3
24/08/2016 8.02    42,792    13,232    6,8    2,9
24/08/2016 8.01    42,776    13,185    10,1    2,9
24/08/2016 8.00    42,729    13,212    9    2,9
24/08/2016 7.53    42,806    13,168    9,1    3,3
24/08/2016 7.52    42,643    13,289    2,5    3,2
24/08/2016 7.49    42,776    13,237    10,5    2,4
24/08/2016 7.48    42,777    13,204    9,9    3,1
24/08/2016 7.47    42,738    13,23    10,4    2,5
24/08/2016 7.45    42,712    13,262    9,7    2,7
24/08/2016 7.43    42,754    13,215    9,3    1,9
24/08/2016 7.41    42,711    13,172    10,9    1,8
24/08/2016 7.39    42,719    13,248    9,5    2,3
24/08/2016 7.29    42,728    13,227    8    2,3
24/08/2016 7.28    42,802    13,208    9,1    2,5
24/08/2016 7.28    42,706    13,164    11,1    2,8
24/08/2016 7.26    42,791    13,145    10,5    2,4
24/08/2016 7.25    42,719    13,235    4,3    3,1
24/08/2016 6.19    42,787    13,114    3,5    2,6
24/08/2016 6.11    42,697    13,205    12,1    3
24/08/2016 6.09    42,757    13,15    6,2    2,5
24/08/2016 6.07    42,753    13,239    10,3    2,5
24/08/2016 6.05    42,664    13,294    5,9    2,3
24/08/2016 6.03    42,744    13,173    8,2    2,2
24/08/2016 6.01    42,734    13,209    9,8    2,4
24/08/2016 6.00    42,612    13,304    10,9    2,8
24/08/2016 5.58    42,623    13,242    10,9    2,4
24/08/2016 5.49    42,616    13,24    11,4    2,2
24/08/2016 5.45    42,649    13,209    11,6    2,8
24/08/2016 5.37    42,601    13,284    10,7    3
24/08/2016 5.36    42,797    13,149    7,2    3,4
24/08/2016 5.31    42,674    13,209    10,4    3,4
24/08/2016 5.28    42,728    13,181    10,2    2,8
24/08/2016 5.24    42,756    13,222    6,4    3,2
24/08/2016 5.04    42,768    13,168    5,8    2,5
24/08/2016 5.02    42,46    13,287    9,8    3,6
24/08/2016 4.59    42,721    13,231    9,5    2,9
24/08/2016 4.55    42,601    13,289    7    3
24/08/2016 4.52    42,73    13,179    11,3    3
24/08/2016 4.50    42,771    13,195    6,8    2,3
24/08/2016 4.47    42,781    13,148    5,2    2,5
24/08/2016 4.44    42,735    13,185    9,7    3,7
24/08/2016 4.38    42,635    13,225    9,9    3,5
24/08/2016 4.33    42,628    13,24    10,1    3,5
24/08/2016 4.30    42,64    13,218    9,3    3
24/08/2016 4.25    42,641    13,241    9,4    3,6
24/08/2016 4.24    42,569    13,255    10,3    2,9
24/08/2016 4.12    42,782    13,209    6,6    2,9
24/08/2016 4.06    42,769    13,125    7,6    4,4
24/08/2016 4.05    42,732    13,155    9,1    2,7
24/08/2016 4.03    42,665    13,215    9,9    2,8
24/08/2016 4.00    42,738    13,225    10,2    3,2
24/08/2016 3.59    42,722    13,219    9,9    3,5
24/08/2016 3.55    42,797    13,149    6,6    2,7
24/08/2016 3.53    42,665    13,258    5,2    3
24/08/2016 3.52    42,711    13,217    7,9    3,1
24/08/2016 3.49    42,788    13,204    6,8    2,8
24/08/2016 3.45    42,732    13,155    3,6    2,8
24/08/2016 3.42    42,773    13,209    5,8    2,8
24/08/2016 3.40    42,617    13,245    10,6    4,1
24/08/2016 3.31    42,753    13,162    6,2    2,9
24/08/2016 3.30    42,737    13,214    9,6    2,7
24/08/2016 3.26    42,75    13,186    8,5    2,8
24/08/2016 3.22    42,769    13,19    6,3    3,1
24/08/2016 3.19    42,786    13,21    5,7    3,7
24/08/2016 3.17    42,762    13,159    8,4    3,8
24/08/2016 3.16    42,726    13,166    8,8    3,5
24/08/2016 3.13    42,7    13,218    3,4    3,2
24/08/2016 3.11    42,635    13,249    10,1    3,4
24/08/2016 3.09    42,696    13,232    9,6    3,7
24/08/2016 3.08    42,612    13,271    15,4    4
24/08/2016 3.05    42,736    13,154    9,7    2,6
24/08/2016 3.01    42,765    13,223    6,6    3,1
24/08/2016 2.59    42,799    13,143    9    4,1
24/08/2016 2.55    42,651    13,279    1,2    3,5
24/08/2016 2.54    42,739    13,177    6    3,1
24/08/2016 2.54    42,742    13,203    9,5    3,1
24/08/2016 2.51    42,768    13,222    6,2    3,7
24/08/2016 2.48    42,795    13,16    6,8    3
24/08/2016 2.44    42,637    13,245    10,5    2,8
24/08/2016 2.33    42,794    13,154    8,7    5,3
24/08/2016 2.30    42,805    13,116    3,8    3,4
24/08/2016 2.28    42,626    13,279    10,5    3,2
24/08/2016 2.19    42,635    13,232    10,1    3,8
24/08/2016 2.13    42,773    13,168    10,7    3,7
24/08/2016 2.10    42,758    13,188    10,7    3,8
24/08/2016 2.09    42,742    13,199    8,6    3,6
24/08/2016 2.07    42,658    13,318    9,6    3,8
24/08/2016 2.05    42,607    13,276    10,3    3,9
24/08/2016 2.01    42,798    13,255    8,5    3,9
24/08/2016 1.57    42,717    13,229    10,8    3,4
24/08/2016 1.56    42,614    13,275    4,8    4,4
24/08/2016 1.52    42,657    13,233    10,8    3,1
24/08/2016 1.46    42,761    13,153    5,5    3,7
24/08/2016 1.41    42,781    13,152    7,1    3,9
24/08/2016 1.40    42,793    13,197    3,5    3,8
24/08/2016 1.37    42,72    13,253    6,9    1,9
24/08/2016 1.36    42,704    13,238    4,2    6
24/08/2016 0.48    42,564    13,153    14,9    1,3
24/08/2016 0.10    42,58    13,271    13,8    1,1

 

[Hui]

Is this what your after?


see attached file:

 

[jumpjack]

No, I need to know how many shocks are between 24/08/2016 0.00 and 24/08/2016 1.00 (1 hour interval), then how many between 24/08/2016 1.00 and 24/08/2016 2.00 and so on, and plot the values against date/time.

In a second instance, to be able to change the period (1 hour, 12 hours, 1 day, 1 week,...) without changing dozens of formulas each time.

In a third instance to filter results for shocks above or below a value.

...it's very complex...

I also looked for free data analysis software but I can't find anything suitable.

 

[Hui]

Do you mean like:

Hourly:


2 Hrs


See attached file:

 

[jumpjack]

Looks promising, I'll do some tests...

 

[jumpjack]

Your method is intriguing.
I'm studing the new COUNT function and trying to make also a chart with dynamic ranges, once (if...) I'll get it I'll publish my results.