Categorising data by time

[SH1234]

Hi I have a problem that is causing me a bit of trouble so any help will be appreciated.

There are 2 columns. Column 1 has Time (T) and 2 has a variable C.

The macro has to be able to look at column T and find out all the data within 1 s intervals and produce the N count, sum of the corresponding values of C and the average. So it will start at 0 and look for all the values of C for T less than 1. Then it will look all the values of C for T between 1 and 2.... and so forth until it reaches the end of T.

I have attached a dataset with a manual example. Takes a long time to get to the end manually.

ThanksTest dataset.xls

[Bob Phillips]

Sub AddSummary()
Dim rng As Range
Dim lastrow As Long
Dim firstrow As Long
Dim nextrow As Long
Dim limit As Long
Dim i As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        .Range("E1:G1").Value = Array("N", "Sum", "Average")
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        nextrow = 2
        firstrow = 2
        limit = 1
        For i = 2 To lastrow + 1

            limit = Int(.Cells(i, "B").Value) + 1
        
            Do
                
                i = i + 1
            Loop Until .Cells(i, "B").Value >= limit Or i >= lastrow + 1
        
            Set rng = .Cells(firstrow, "C").Resize(i - firstrow)
            .Cells(nextrow, "E").Formula = "=COUNT(" & rng.Address & ")"
            .Cells(nextrow, "F").Formula = "=SUM(" & rng.Address & ")"
            .Cells(nextrow, "G").Formula = "=AVERAGE(" & rng.Address & ")"
            
            firstrow = i
            nextrow = nextrow + 1
            
            If i < lastrow + 1 Then i = i - 1 ' to accomodate the Next i
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

[D_Marcel]

Hello SH1234,

It's a good challenge, I had to think a bit, but this is the best I could do:

[VBA]Sub Time_Statistics()

Dim Source As Worksheet
Dim Entry, WorkArea As Range
Dim C_Integer, N_Integer As Integer

Set Source = Worksheets("MySheet")
Set WorkArea = Source.Range(Cells(2, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))

For Each Entry In WorkArea

C_Integer = Int(Entry)
N_Integer = Int(Entry.Offset(1, 0).Value)
Counter = Counter + 1

If C_Integer = N_Integer Then
Total = Total + Entry.Offset(0, 1).Value
Averg = Total / Counter
Else
Total = Total + Entry.Offset(0, 1).Value
Averg = Total / Counter
S_Control = S_Control + 1
If Cells(S_Control, 8).Value <> "" Then
S_Control = S_Control + 1
Cells(S_Control, 7).Value = Counter
Cells(S_Control, 8).Value = C_Integer
Cells(S_Control, 9).Value = Total
Cells(S_Control, 10).Value = Averg
Total = 0
Averg = 0
Counter = 0
Else
Cells(S_Control, 7).Value = Counter
Cells(S_Control, 8).Value = C_Integer
Cells(S_Control, 9).Value = Total
Cells(S_Control, 10).Value = Averg
Total = 0
Averg = 0
Counter = 0
End If
End If
Next Entry

End Sub[/VBA]

Let us know if works for you, deal?
Nice your code xld, I'll try to learn with it.

Good code of yours XLD, I'll study it.

Douglas

[snb]

In E2:

=SUMPRODUCT((INT($B2:$B798)=ROW()-2)*1)

In F2

=SUMPRODUCT((INT($B2:$B798)=ROW()-2)*($C2:$C798))

Or in VBA:

Sub M_snb()
    sn = Cells(1, 2).CurrentRegion
    
    With CreateObject("scripting.dictionary")
        For j = 2 To UBound(sn)
            If .exists(Int(sn(j, 1))) Then
                sp = .Item(Int(sn(j, 1)))
                sp(0) = sp(0) + 1
                sp(1) = sp(1) + sn(j, 2)
            Else
                sp = Array(1, sn(j, 2))
            End If

            .Item(Int(sn(j, 1))) = sp
        Next
        
        Cells(2, 10).Resize(.Count, 2) = Application.Index(.items, 0, 0)
    End With
End Sub

[D_Marcel]

snb, I tried to use the functions DSUM and DAVERAGE, what do you guess? But in this case, requires some manual work...

1. Firstly, create a column and use the function INT to all values in the column B.
2. Insert in a cell:

INT (Header)

="=1"

3. In the column G to calculate the Sum:

=DSUM($A$1:$C$798;"C";J1:J2)

4. In the column H to calculate Average:

=DAVERAGE($A$1:$C$798;"C";J1:J2)

Douglas

[SH1234]

Xld, for some of the work, I have to change the time to chunks of 10 seconds rather than 1... any suggestions as to how the code can be modified to do this? thanks in advance

[SH1234]

Douglas, works a treat.. amazing.. thanks bro... again any suggestions to the code for chunks of 10 seconds... from 0 to 10... then 10 to 20... and so on

[Bob Phillips]

In my code, change

limit = Int(.Cells(i, "B").Value) + 1

to

limit = (Int(.Cells(i, "B").Value/10)+1)*10