In Excel, find longest subsequence matching some criteria

I have a sequence of numbers in an Excel worksheet, and I'd like to find the longest subsequence that matches some criteria. For example, say I have the following:

and I'd like to know where the longest subsequence with every value less than 10 starts and how long it is (row 7, 3 long) or greater than equal to 10 (row 4, 3 long), etc.

I can easily generate an array or vector of TRUEs and FALSEs corresponding to the values and whether they meet the criteria I want, but I have no idea even where to start to find and count subsequences.

I'd prefer not to have a VBA solution, simply because I don't really know VBA and if I have to maintain a script, I'll just whip something up in perl that reads the clipboard and writes the result I want back to it.

Solution

Using only formulas you will need a helper column.

in B2, enter the following formula:
```
=IF(A2>10,"",IF(A1<=10,B1+1,1))
```

(this is be your helper column)

In C1, enter:
```
=MAX(B:B)
```

(This is the length of the longest sequence)

Select the entire first column and create a new conditional format with the following formula:
```
=AND(ROW()>=MATCH(MAX(B:B),B:B,0)-MAX(B:B)+1,ROW()<=MATCH(MAX(B:B),B:B,0))
```

You should end up with something like this: