View Full Version : Categorising data by time

SH1234

01-21-2014, 09:30 AM

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

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

01-22-2014, 05:15 AM

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

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

01-22-2014, 09:35 AM

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

01-22-2014, 11:40 AM

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

01-22-2014, 11:47 AM

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

In my code, change

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

to

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.