Identifying A Pattern Of Values Meeting Specific Criteria
 

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

Identifying A Pattern Of Values Meeting Specific Criteria
 

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

Identifying A Pattern Of Values Meeting Specific Criteria
 

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

Identifying A Pattern Of Values Meeting Specific Criteria
 

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