Live data frequency counter

[Vbalearner85]

Hi,

Just wondering if excel has any vba function for counting occurrences/frequency for numerical data - more than or less than any number(suppose it is 1) for live dynamic data(changes per sec/min)..like in a column

For example -
1.if data in column cell "A2" has number more than 1 for 5 times in a min for live Dynamic data....in next column cell "B2"..it should show "5: and add 1 to it for every such occurrence
2) Similar to step 1, it can show frequency for data less than 1, in column cell "C2" for column data "A2"
2) Steps 1 and 2 can be applied to column range (A2:100), and have independent frequency counters for each cell in range

Regards

 

[Vbalearner85]

Hi Dataluver,

Just for Clarification....Macro data analysis requirement/role is not same as above frequency macro..they both have different data objectives.
Frequency macro is to add 1 more column to the dataset which shows how many times live data has switched between "LR"<>"HR" during the day, while macro data analysis is post recording data analysis for the day

Regards,
PK

 

[Vbalearner85]

Hi.

After some fiddling around...solved Timer and data frequency by worksheet calculate method based upon chnage in value of Column A
1.Timestamp in Column B. Timer is =(now)-timestamp in colun B..gives timer in hh:mm:ss(as of now for current scenario, serves my puspose
2. Interchange Frequencybetween-LR-NR-HR - Total/Low/Medium and High in columns K-L--M-N respectively

Thanks all for your inputs and support!!!
Regards,
PK

Private Sub Worksheet_Calculate()
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Range("A1:A142")
    If Cell.Value <> Cell.Offset(0, 8).Value Then
        Cell.Offset(0, 1).Value = Now
        Cell.Offset(0, 8).Value = Cell.Value
        Cell.Offset(0, 3).Value = Cell.Offset(0, 9).Value
        Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value + 1
    End If
    If Cell.Offset(0, 10).Value <> Cell.Offset(0, 14).Value And Cell.Value = "LR" Then
        Cell.Offset(0, 11).Value = Cell.Offset(0, 11).Value + Cell.Offset(0, 10).Value - Cell.Offset(0, 14).Value
    End If
    If Cell.Offset(0, 10).Value <> Cell.Offset(0, 14).Value And Cell.Value = "NR" Then
        Cell.Offset(0, 12).Value = Cell.Offset(0, 12).Value + Cell.Offset(0, 10).Value - Cell.Offset(0, 14).Value
    End If
    If Cell.Offset(0, 10).Value <> Cell.Offset(0, 14).Value And Cell.Value = "HR" Then
        Cell.Offset(0, 13).Value = Cell.Offset(0, 13).Value + Cell.Offset(0, 10).Value - Cell.Offset(0, 14).Value
    End If
Next
Columns("B:B").EntireColumn.AutoFit
Application.EnableEvents = True
End Sub