Length (count) of sequences with start and end condition

I have some acceleration data for which I am trying to count the length of sequences given a set of conditions. In this case I want to count the length of a sequence when the acceleration moves > 2.78 and then drops back below 0.

An example would be

[-1.1, -1, 0, 1.2, 1.8, 2, 2,88, 2.86, 2.53, 1.98, 1.21, 0.89, 0.11, -0.21]

The return result here would be a count of 7 (2,88, 2.86, 2.53, 1.98, 1.21, 0.89, 0.11)

I have done this previously to identify the largest sequence with one condition using the following formula

{=MAX(FREQUENCY(IF($T2:$T81781>2.78,ROW($T2:$T81781)),IF($T2:$T81781<=2.78,ROW($T2:$T81781))))}

Using the above example, the result using this formula would be 2 (2.88, 2.86)

Help please

@jake,

if the sequence were: [2,3,4,2,1,2,3,2,1,0,-1]

is the result 10, or 5 ?

i.e. does the drop to 1 and subsequent increase invalidate the [3,4,2,1,2] sequence

I suspect you will want to use either a) a 'key' adjacent to your T values to label/group specific sequences, or b) a UDF

Hi XLent,
I will try to answer though not instead of Jake, so if I understood him correct - he wants to find the 1st instance in a sequence where it is > 2.78 and count all subsequent numbers until it reached a negetive (<0) and stop there.
So in your example 3 would be the start point (the instance where >2.78), and it will stop counting in 0 (since the next value is negative).
The answer then should be 9

Hi XLent,

In my case the answer would be 10

Thanks!

jake_jennings1
Thant means I didnt understand you correct.
Can you lookthe file at post # 4 and advise if this is the result you are expecting?

Originally Posted by jake_jennings1

Hi XLent, In my case the answer would be 10

In which case, given size of range and number of potential sequences, I'd personally suggest you do following:

Formula:

Please Login or Register  to view this content.

Hi everyone,

Thanks for all your help. I ended up figuring it out using python. This allowed me to identify multiple occurrences within one set of data and then export back into excel.
Appreciate everyone's help!