Identifying A Pattern Of Values Meeting Specific Criteria

C

CSHAKES

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
 
T

T. Valko

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:G2>2,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
 
C

CSHAKES

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

T. Valko

You're welcome. Thanks for the feedback!

Biff

CSHAKES said:
Beautiful!! It worked on my sample data. I'll modify it for my real
task.
Thank you for the quick response.
Click to expand...
 
Top