# Calculating number of events within a rolling 6 second period

#### stu_iow

##### New Member
Hello there,

I have a guideline that suggest when you are driving looking at your Smartphone for greater than 2 seconds within any 6 second period leads to an increased risk of a crash. I have an Excel sheet with two columns, one is the length of time of a single glance, and the other is where the driver is looking, see below. (For your information 5 is looking forward and 6 is at the phone).

 5 1.212 6 0.276 5 0.596 6 0.412 5 1.21575 6 0.356 5 0.664 6 0.3 5 0.684 6 0.424 5 0.92 6 0.384 5 2.61975 6 0.42 5 1.212 6 0.284 5 0.53575 6 0.496 4 0.232 5 0.852 2 0.596 5 2.112 6 0.29975 5 0.628 8 0.376 5 1.888 2 0.536 5 0.96 8 0.35575

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

So how can I calculate: 1) On how many occasions the times associated with 5 were greater than 2 seconds (this is the total time for all 5 within this period) within any rolling 6 second period (or as close to but greater than 6 seconds). 2) More difficult, but is it possible to calculate an average time looking at the phone (5) for any rolling 6 second period.

I hope this makes sense, if it doesn't I can clarify.

Thanks,

Stewart.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try this:-
The code first loops throught column "A" , adding the times in column "B" until the sum is greater than 6.
This creates a sun Range.
The code then loop through this sub range counting the column a "5"'s that are greater thnan 2.
This is repeated restarting the entire loop from each seperate row, to give a rolling average.
At the same time the Average for Each 6 sec period is taken for Column "A" 5's.
the results are shown in column "C" for the 2 secs periods and in column "E" for the Average time.
Each line in column "E" represents 1 loop of the Range.
I hope the results make some sense to you !!!
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG20Jun07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Tim [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Cl [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] TTav [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] T
[COLOR="Navy"]Dim[/COLOR] Tt
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A" & n), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Tim = Tim + Dn.Offset(, 1)
[COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Dn
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]If[/COLOR] Tim > 6 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Cl [COLOR="Navy"]In[/COLOR] nRng
[COLOR="Navy"]If[/COLOR] Cl = 5 And Cl.Offset(, 1) > 2 [COLOR="Navy"]Then[/COLOR]
c = c + 1
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] Cl = 5 [COLOR="Navy"]Then[/COLOR] av = av + Cl.Offset(, 1)
[COLOR="Navy"]Next[/COLOR] Cl
[COLOR="Navy"]Set[/COLOR] nRng = Nothing
Tim = 0

tav = tav & ", " & Format(av / 6, "0.000")
av = 0
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
TTav = TTav & Chr(10) & Mid(tav, 2)
tav = ""
tot = tot & Chr(10) & c
c = 0
[COLOR="Navy"]Next[/COLOR] n
T = Split(Mid(tot, 2), Chr(10))
Range("C1").Resize(UBound(T)) = Application.Transpose(T)
Tt = Split(Mid(TTav, 2), Chr(10))
Range("E1").Resize(UBound(Tt)) = Application.Transpose(Tt)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Hello Mick,

This was my first dabble in VBA so I appologise for the delay in reponding/thanking you. What you wrote works well for what I asked, but I realise that I wasn't clear enough. I am looking to see if the sum off all the 5's in the sub range add up to greater than 2, rather than if one individual 5 is greater than 2 (i.e. say there are three 5's in the sub range of 0.6, 1.2 and 0.4, these would add up to greater than 2 rather than none of them are above two. Also all the numbers in column C (with the excetion of C1) are zeros, what should this be indicating? Finally what are the numbers in E1 representing?

Sorry for all the questions but many thanks.

Hi,

I have quite different solution, using formulas only.
Your original data is in column B and C.
Column A is numbering of events, from 1 (timer switched on), and every recording is an event.
Column Di is the time when that events begins and E is the time when this event ends.

F and G are rows of 6 second periods, F is the beginning of the period and G is its end. H is during which event the period starts and F is during which event that period ends.

Generally, the phone looking time is the sum of the lengths of events with 5 in column B, which cover the period, minus the excess (column J), which is the phone looking time which belongs to the events, but is outside of the period.

L is the value you are looking for.

E.g., the period between second 2 and second 8 corresponds to events from 4 (which begins at time 1.488) to 14 (which ends at time 10.0635). Both of these events where "looking at the phone".

So in order to calculate the time spent looking at the phone during this period we sum all the phone looking events between 2 and 14, minus:
[*] time between 1.488 and 2, which is the part of event 2 before second 2
[*] time between 8 and 10.0635, which is the part of event 14 after second 8

Please exchange ; into , in all formulas. Then copy them downward. The formulas are not good for adding an extra header.

J.Ty.

Excel Workbook
ABCDEFGHIJKL
110000from secondto secondfrom eventto eventexcessphone time
2251.21201.2120621104.37175
3360.2761.2121.488172121.059754.232
4450.5961.4882.084284142.57554.124
5560.4122.0842.496396141.56754.536
6651.215752.4963.711754107140.06354.82425
7760.3563.711754.067755119160.69554.74025
8850.6644.067754.7317561211180.515254.77225
9960.34.731755.03175
101050.6845.031755.71575
111160.4245.715756.13975
121250.926.139757.05975
131360.3847.059757.44375
141452.619757.4437510.0635
Sheet1

Hopefully this will be better
Results as headers Columns C to G.
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG21Jun17
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Tim         [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Cl          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Av          [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Frng        [COLOR="Navy"]As[/COLOR] Range
c = 1
Range("C1:G1") = Array("Address", "5 Ac/Time", "Above/below 2", "Av of 5/Period", "Act Time/Period")
[COLOR="Navy"]Set[/COLOR] Frng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] n = 1 To Frng.Count
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A" & n), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Tim = Tim + Dn.Offset(, 1)
[COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Dn
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]If[/COLOR] Tim > 6 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Cl [COLOR="Navy"]In[/COLOR] nRng
[COLOR="Navy"]If[/COLOR] Cl = 5 [COLOR="Navy"]Then[/COLOR]
Av = Av + Cl.Offset(, 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Cl
c = c + 1
Cells(c, "D") = Av
Cells(c, "E") = IIf(Av > 2, "Above 2", "Below 2")
Cells(c, "F") = Format(Av / 6, "0.00")
Cells(c, "G") = Tim
[COLOR="Navy"]Set[/COLOR] nRng = Nothing
Tim = 0
Av = 0
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Set[/COLOR] nRng = Nothing
Tim = 0
Av = 0
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Hello again,

Both options worked perfectly, so thank you both for that. All I need to do now is find a way to interpret the results!

Thanks again and much obliged.

Good luck and thanks for the feedback!

J.Ty.

Replies
2
Views
7K

### Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back