Cell below value for a certain duration

[garneson]

Let’s say in column A I have a thousand rows of dates/times. Then in column B I have corresponding number data. I’m trying to understand how many times the data in column B is lower than a certain value (let’s say 5) for a given duration (let’s say 2 hours). In other words, look at the data in column B, and when the value is below 5, start duration counter from column A. Continue down column B until value jumps above 5. If duration is greater than 2 hours, count as 1 event. Sum all events.

 

[GraH]

Hello garneson,

You will need to give us something more to go on, like a sample set and a manual solution that guides us how you want the calculation to proceed.
e.g. The duration of 2 hours is this the difference between each time value in column A compared to the previous row? Or is the value in A a duration? Or the duration is the difference between the timestamps where you'd have a value below 5 in column B?

I'm afraid the requirements are to vague for any of us to help you. Sample data may clarify all or provoke the correct questions being asked.
Thx.

 

[Peter_SSs]

Welcome to the MrExcel board!

Sum all events.

Do you mean count all events?

If so, see if this user-defined functions does what you want. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CountBelow(rngData As Range, lngLimit As Long, lngHours As Long) As Long
  Dim a As Variant
  Dim i As Long
  Dim dteStart As Date
  Dim blnStarted As Boolean
  
  a = rngData.Value
  For i = 1 To UBound(a)
    If a(i, 2) < lngLimit Then
      If Not blnStarted Then
        blnStarted = True
        dteStart = a(i, 1)
      End If
    Else
      If blnStarted Then
        If a(i - 1, 1) - dteStart >= lngHours / 24 Then CountBelow = CountBelow + 1
      End If
      blnStarted = False
    End If
  Next i
  If blnStarted Then
    If a(i - 1, 1) - dteStart >= 1 / 12 Then CountBelow = CountBelow + 1
  End If
End Function

Here is my sample data and results. I have manually coloured the cells to show the 3 areas that the function has counted.

garneson 2020-04-28 1.xlsm
	A	B	C	D
1	Date/Time	Value		Count
2	25-Apr-20 00:00	5		3
3	25-Apr-20 00:06	3
4	25-Apr-20 00:54	1
5	25-Apr-20 00:59	4
6	25-Apr-20 01:07	1
7	25-Apr-20 01:13	2
8	25-Apr-20 01:29	4
9	25-Apr-20 01:33	4
10	25-Apr-20 01:41	4
11	25-Apr-20 01:52	4
12	25-Apr-20 02:12	2
13	25-Apr-20 02:55	5
14	25-Apr-20 03:00	3
15	25-Apr-20 03:08	2
16	25-Apr-20 03:16	1
17	25-Apr-20 03:23	5
18	25-Apr-20 03:55	5
19	25-Apr-20 04:11	4
20	25-Apr-20 08:59	1
21	25-Apr-20 09:04	5
22	25-Apr-20 09:10	3
23	25-Apr-20 09:47	2
24	25-Apr-20 09:53	1
25	25-Apr-20 09:58	2
26	25-Apr-20 10:12	5
27	25-Apr-20 10:34	4
28	25-Apr-20 12:41	3
29
Sheet1

Cell Formulas
Range	Formula
D2	D2	=CountBelow(A2:B28,5,2)

 

[garneson]

Your macro looks very promising [B]Peter_SSs[/B]. And yes I want to count the number of times the value in column B has lasted longer that 2 hours (column A).

I tried to implement your code with my spreadsheet, but I must be incorporating my values incorrectly. When I play around with cell D2 limits, the resulting count doesn't seem to be accurate.

For my column B limit, I'm actually looking at a limit of 0.04. See the attached screenshot of a small sample of my data. The screen shot data is above my threshold of 0.04, but I have over 10000 rows that need to be evaluated. Also, does my date/time format in column A need to be a certain cell type?

I really appreciate the help!

 

[Peter_SSs]

does my date/time format in column A need to be a certain cell type?

It needs to be a 'proper' date (that is, a number) not text. In another cell, what does this formula return?
=ISNUMBER(A2)

If you are still having problems, can you post a small set of sample data with XL2BB and explain your expected result in relation to that small sample? Make sure your sample includes data that would result in a count of say 2.

 

[garneson]

Thanks! I don’t think the date is my problem. Within your code, which values would I change if I wanted to know if the column B data dropped below 0.04. For example, if the value in column B dropped to 0.039, I’d like to start the two hour timer. Seems like maybe the current code subtracts 1 but I’m not positive.

 

[Peter_SSs]

Within your code, which values would I change if I wanted to know if the column B data dropped below 0.04

I had only allowed for whole numbers, so change the function to this

Function CountBelow(rngData As Range, dblLimit As Double, dblHours As Double) As Long
  Dim a As Variant
  Dim i As Long
  Dim dteStart As Date
  Dim blnStarted As Boolean

  a = rngData.Value
  For i = 1 To UBound(a)
    If a(i, 2) < dblLimit Then
      If Not blnStarted Then
        blnStarted = True
        dteStart = a(i, 1)
      End If
    Else
      If blnStarted Then
        If a(i - 1, 1) - dteStart >= dblHours / 24 Then CountBelow = CountBelow + 1
      End If
      blnStarted = False
    End If
  Next i
  If blnStarted Then
    If a(i - 1, 1) - dteStart >= 1 / 12 Then CountBelow = CountBelow + 1
  End If
End Function

Then you would use
=CountBelow(A2:B28,0.04,2)

 

[garneson]

Wow that did it. Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.