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.

Thanks11135

[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:

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

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