Calculating number of events within a rolling 6 second period

S

stu_iow

New Member
Joined
Sep 11, 2009
Messages
12
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).

[TABLE="width: 96"]
<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.212[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.276[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.596[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.412[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]1.21575[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.356[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.664[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.684[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.424[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.92[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.384[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]2.61975[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.42[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]1.212[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.284[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.53575[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.496[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]0.232[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.852[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0.596[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]2.112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]0.29975[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.628[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]0.376[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]1.888[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0.536[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]0.96[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]0.35575[/TD]
[/TR]
</tbody>[/TABLE]


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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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, "C") = nRng.Address
                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
 
Upvote 0
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.
 
Upvote 0
Good luck and thanks for the feedback!

J.Ty.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
First in First Out (Fifo) Stock balance Calaculation File.
Replies
2
Views
10K
TotallyConfused
T

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top