find number of times a value occurs in between times

[pyclen]

Hi All,

I have a series of Excel files with two columns, Column A is the date/time the other column is bpm (beats per minute) as shown below, there are >12k values per day so I am not posting the whole file. The columns are column A & B.

my question now is how can I determine the number of times the bpm is greater than 90 for example in between two times, (lets say 3-4am)

any help is appreciated

dateTime bpm 07/09/23 04:00:02 68​ 07/09/23 04:00:07 71​ 07/09/23 04:00:12 70​ 07/09/23 04:00:17 68​ 07/09/23 04:00:22 67​ 07/09/23 04:00:27 67​ 07/09/23 04:00:32 69​ 07/09/23 04:00:37 70​ 07/09/23 04:00:42 68​ 07/09/23 04:00:47 67​ 07/09/23 04:00:57 68​ 07/09/23 04:01:12 68​ 07/09/23 04:01:17 69​ 07/09/23 04:01:27 68​ 07/09/23 04:01:32 66​ 07/09/23 04:01:42 65​ 07/09/23 04:01:52 66​ 07/09/23 04:01:57 68​ 07/09/23 04:02:07 64​

 

[DanteAmor]

Check the next example:

varios 14jul2023.xlsm
	A	B	C	D	E
1	dateTime	bpm		Start time:	07/09/2023 04:00:12
2	07/09/2023 04:00:02	68		End time:	07/09/2023 04:00:57
3	07/09/2023 04:00:07	71		bpm >	68
4	07/09/2023 04:00:12	70		Count:	3
5	07/09/2023 04:00:17	68
6	07/09/2023 04:00:22	67
7	07/09/2023 04:00:27	67
8	07/09/2023 04:00:32	69
9	07/09/2023 04:00:37	70
10	07/09/2023 04:00:42	68
11	07/09/2023 04:00:47	67
12	07/09/2023 04:00:57	68
13	07/09/2023 04:01:12	68
14	07/09/2023 04:01:17	69
15	07/09/2023 04:01:27	68
16	07/09/2023 04:01:32	66
17	07/09/2023 04:01:42	65
18	07/09/2023 04:01:52	66
19	07/09/2023 04:01:57	68
20	07/09/2023 04:02:07	64
21
Hoja2

Cell Formulas
Range	Formula
E4	E4	=COUNTIFS(A:A,">="&E1,A:A,"<="&E2,B:B,">"&E3)

In cells E1 to E3 you can put the values you need to count.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[ranman256]

paste macro into a module.

sheet: DATA
has your bpm data

sheet: MAIN
holds the seach criteria:

B2 = start time: 3 am
B3 = end time: 4 am

B6 = operator : >
C6 = BPM value to search

enter the values then run: FindBpm

Option Explicit

Private mvStartTime As Date, mvEndTime As Date, mvTime As Date
Private mvOper
Private miTargBPM As Integer, miBpm As Integer
Private Const kFOUND = "FOUND"
Sub FindBpm()
' FindBpm Macro


mvStartTime = Range("B2").Value
mvEndTime = Range("B3").Value

mvOper = Range("B6").Value
miTargBPM = Range("C6").Value

Worksheets("Data").Select
Columns("C:C").ClearContents

Range("A2").Select
Range("C1").Value = "Found"

While ActiveCell() <> ""
  mvTime = Format(ActiveCell.Value, "h ampm")
  miBpm = Val(ActiveCell.Offset(0, 1).Value)
  
  If mvTime >= mvStartTime And mvTime <= mvEndTime Then
      Select Case mvOper
         Case "="
           If miBpm = miTargBPM Then GoSub FountIT
         Case "<"
           If miBpm < miTargBPM Then GoSub FountIT
         Case ">"
           If miBpm > miTargBPM Then GoSub FountIT
         Case "<="
           If miBpm <= miTargBPM Then GoSub FountIT
         Case ">="
           If miBpm >= miTargBPM Then GoSub FountIT
       End Select
   End If

  ActiveCell.Offset(1, 0).Select 'next row
Wend
Exit Sub

FountIT:
 ActiveCell(0, 3).Value = kFOUND
Return
End Sub

 

[pyclen]

Thank you, appreciate the macro

will let it run on a data set, and see what happens