The example is a simplification of a problemI encountered with "80 trials"
(rows) and "28 times" (columns) where I needed to identify trials that had
values that met a specific criteria, but also the pattern of the values that
met the criteria.

I had a very crude method that took a while.

What is the quickest way to determine which of the trials below had 3 or
more consequetive values less than or equal to 2?

Time 1 Time 2 Time 3 Time 4 Time 5 Time 6
Trial 1 4 3 2 0 1 1
Trial 2 3 1 4 3 0 3
Trial 3 2 4 1 2 1 1
Trial 4 2 5 2 4 3 3

Corrrect answer is:
Trial 1 with pattern 2,0,1,1
and
Trial 3 with pattern 1,2,1,1

This assumes there are no empty cells within your table:

Time values are in column B to column G.

Enter this array formula** in I2 and copy down as needed:

=IF(MAX(FREQUENCY(IF(B2:G2<=2,COLUMN(B2:G2)),IF(B2 :G22,COLUMN(B2:G2))))=3,"X","")

Those trials the meet the condition will be identified with a "X".

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"CSHAKES" wrote in message
...
The example is a simplification of a problemI encountered with "80 trials"
(rows) and "28 times" (columns) where I needed to identify trials that had
values that met a specific criteria, but also the pattern of the values
that
met the criteria.

I had a very crude method that took a while.

What is the quickest way to determine which of the trials below had 3 or
more consequetive values less than or equal to 2?

Time 1 Time 2 Time 3 Time 4 Time 5 Time 6
Trial 1 4 3 2 0 1 1
Trial 2 3 1 4 3 0 3
Trial 3 2 4 1 2 1 1
Trial 4 2 5 2 4 3 3

Corrrect answer is:
Trial 1 with pattern 2,0,1,1
and
Trial 3 with pattern 1,2,1,1

Beautiful!! It worked on my sample data. I'll modify it for my real task.
Thank you for the quick response.

"T. Valko" wrote:

This assumes there are no empty cells within your table:

Time values are in column B to column G.

Enter this array formula** in I2 and copy down as needed:

=IF(MAX(FREQUENCY(IF(B2:G2<=2,COLUMN(B2:G2)),IF(B2 :G22,COLUMN(B2:G2))))=3,"X","")

Those trials the meet the condition will be identified with a "X".

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"CSHAKES" wrote in message
...
The example is a simplification of a problemI encountered with "80 trials"
(rows) and "28 times" (columns) where I needed to identify trials that had
values that met a specific criteria, but also the pattern of the values
that
met the criteria.

I had a very crude method that took a while.

What is the quickest way to determine which of the trials below had 3 or
more consequetive values less than or equal to 2?

Time 1 Time 2 Time 3 Time 4 Time 5 Time 6
Trial 1 4 3 2 0 1 1
Trial 2 3 1 4 3 0 3
Trial 3 2 4 1 2 1 1
Trial 4 2 5 2 4 3 3

Corrrect answer is:
Trial 1 with pattern 2,0,1,1
and
Trial 3 with pattern 1,2,1,1

You're welcome. Thanks for the feedback!

Biff

"CSHAKES" wrote in message
...
Beautiful!! It worked on my sample data. I'll modify it for my real
task.
Thank you for the quick response.

"T. Valko" wrote:

This assumes there are no empty cells within your table:

Time values are in column B to column G.

Enter this array formula** in I2 and copy down as needed:

=IF(MAX(FREQUENCY(IF(B2:G2<=2,COLUMN(B2:G2)),IF(B2 :G22,COLUMN(B2:G2))))=3,"X","")

Those trials the meet the condition will be identified with a "X".

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"CSHAKES" wrote in message
...
The example is a simplification of a problemI encountered with "80
trials"
(rows) and "28 times" (columns) where I needed to identify trials that
had
values that met a specific criteria, but also the pattern of the values
that
met the criteria.

I had a very crude method that took a while.

What is the quickest way to determine which of the trials below had 3
or
more consequetive values less than or equal to 2?

Time 1 Time 2 Time 3 Time 4 Time 5 Time 6
Trial 1 4 3 2 0 1 1
Trial 2 3 1 4 3 0 3
Trial 3 2 4 1 2 1 1
Trial 4 2 5 2 4 3 3

Corrrect answer is:
Trial 1 with pattern 2,0,1,1
and
Trial 3 with pattern 1,2,1,1