Live data frequency counter

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,098
Members
416,161
Latest member
David1966Lewis

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
Top