Stuck trying to find a function

[CWatters]

I have data collected every 10 mins that rises and falls in value. I'm
trying find out how long the data remains above a certain value.

For example suppose the data was

2 3 4 5 5 4 5 3 3 1 1 2 3 4 5 6 6 5 4 3 3 2 1 .....

and I wanted to find out how long it stays at 4 or above.

Is there a way to do something like a COUNT WHILE function so that I can
generate

Either

0 0 1 2 3 4 5 0 0 0 0 0 0 1 2 3 4 5 6 0 0 0 0 0...

or

0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0...

from the above data?

I tried using SUBTOTAL but it's way too slow because of the size of the
data I have.

 

[CWatters]

I have data collected every 10 mins that rises and falls in value. I'm
trying find out how long the data remains above a certain value.

For example suppose the data was

2 3 4 5 5 4 5 3 3 1 1 2 3 4 5 6 6 5 4 3 3 2 1 .....

and I wanted to find out how long it stays at 4 or above.

Is there a way to do something like a COUNT WHILE function so that I can
generate

Either

0 0 1 2 3 4 5 0 0 0 0 0 0 1 2 3 4 5 6 0 0 0 0 0...

or

0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0...

from the above data?

I tried using SUBTOTAL but it's way too slow because of the size of the
data I have.

Ok looks like I will have to learn how to write VBA macros. First
attempt seems to work but isn't very elegant.

 

[Ron Rosenfeld]

Ok looks like I will have to learn how to write VBA macros. First
attempt seems to work but isn't very elegant.

Here's one way using a macro:

This assumes your source data is in column A, with no header, and the results will be written into column B, in accord with your first example.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.


===========================
Option Explicit
Sub StaysAbove()
Dim rSrc As Range, rDest As Range
Dim v As Variant, vRes() As Double
Dim i As Long, j As Long, k As Long
Const ChkVal As Long = 4
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set rDest = rSrc.Offset(columnoffset:=1)

v = rSrc
ReDim vRes(LBound(v, 1) To UBound(v, 1), LBound(v, 2) To UBound(v, 2))
For i = LBound(v, 1) To UBound(v, 1)
If v(i, 1) >= ChkVal Then
j = j + 1
If j > k Then k = j
Else
j = 0
k = 0
End If
vRes(i, 1) = k
Next i
rDest = vRes
End Sub
==========================

 

[CWatters]

Here's one way using a macro:

This assumes your source data is in column A, with no header, and the results will be written into column B, in accord with your first example.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.


===========================
Option Explicit
Sub StaysAbove()
Dim rSrc As Range, rDest As Range
Dim v As Variant, vRes() As Double
Dim i As Long, j As Long, k As Long
Const ChkVal As Long = 4
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set rDest = rSrc.Offset(columnoffset:=1)

v = rSrc
ReDim vRes(LBound(v, 1) To UBound(v, 1), LBound(v, 2) To UBound(v, 2))
For i = LBound(v, 1) To UBound(v, 1)
If v(i, 1) >= ChkVal Then
j = j + 1
If j > k Then k = j
Else
j = 0
k = 0
End If
vRes(i, 1) = k
Next i
rDest = vRes
End Sub
==========================

Hi Ron, Many thanks for that I'll give it a go.