Identifying A Pattern Of Values Meeting Specific Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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: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
 
Beautiful!! It worked on my sample data. I'll modify it for my real task.
Thank you for the quick response.
 
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...
 
Back
Top