patterns
 

Alright, I have another. The problem this time is I want excel to
recognize a pattern. If I have a list of 8 numbers, I want to know if
there is ever 5 consecutive numbers.


2
5
4
10
8
6
3
4

I want it to recognize that there is a string of 5 (2,3,4,5,6)


--
DArinello
------------------------------------------------------------------------
DArinello's Profile: http://www.excelforum.com/member.php...o&userid=25589
View this thread: http://www.excelforum.com/showthread...hreadid=390090

If your numbers are one to a cell, in column A, you could drop this formula
in B5 and autofill through column B:
=IF(AND(A5=A4+1,A5=A3+2,A5=A2+3,A5=A1+4),"Run","")

"DArinello" wrote:


Alright, I have another. The problem this time is I want excel to
recognize a pattern. If I have a list of 8 numbers, I want to know if
there is ever 5 consecutive numbers.


2
5
4
10
8
6
3
4

I want it to recognize that there is a string of 5 (2,3,4,5,6)


--
DArinello
------------------------------------------------------------------------
DArinello's Profile: http://www.excelforum.com/member.php...o&userid=25589
View this thread: http://www.excelforum.com/showthread...hreadid=390090

Just to keep the ball rolling a little further here .. think this option
(experimental) might bring us a little closer to one workable solution for
the pattern detection. It seems to return ok maybe 70%+ of the time <g

Assuming the source list of numbers is in A1:A8 (w/o any blank cells). And
as per sample data in the orig. post, it was assumed that the source list of
numbers would be in random sequence and could contain duplicates

Placed in:

B1:
=IF(COUNTIF($A$1:A1,A1)1,"",ROW())

C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

D1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",SMALL(C:C ,ROWS($A$1:A1)))

E1:
=IF(ISERROR(ABS(D1-D2)),"",ABS(D1-D2))

Selected B1:E1, filled down to E8

Set-up the monitoring / result cells:

Placed in say, F1, and array-entered the formula,
i.e. pressed CTRL+SHIFT+ENTER:

=IF(MATCH(1,--($E$1:$E$8<1),0)=1,"",SUM(E1:INDIRECT("E"&MATCH(1 ,--($E$1:$E$
8<1),0)-1)))

Placed in F2, and array-entered the formula:

=SUM(INDIRECT("E"&MATCH(1,--($E$1:$E$8<1),0)+1):E8)

Placed in F3:

=IF(MAX(F1:F2)+1=5,"Exactly 5 consecutive
numbers",IF(MAX(F1:F2)+15,MAX(F1:F2)+1&" consecutive numbers",""))

Kept an eye on the result cells F3,
and tested with various sample data in A1:A8

If there's exactly 5 consecutive numbers within A1:A8,
we should get the message to the effect in F3, viz.:
"Exactly 5 consecutive numbers"

And if there's more than 5 consecutive numbers within A1:A8, we should get
one of the following 3 messages in F3, depending on the evaluated result:

"6 consecutive numbers"
"7 consecutive numbers"
"8 consecutive numbers"

If there's less than 5 consecutive numbers within A1:A8,
F3 should remain blank

Sample testing reveals the above seems to return correctly perhaps 70%+ of
the time in F3. It returns incorrectly with say, these sort of data in
A1:A8

1
2
4
5
6
8
2
2

which reduces to an ascending sorted range of uniques
(in col D):

1
2
4
5
6
8

which is 2 sets of consecutives: 1,2 and 4,5,6
both less than 5 consec's.

The incorrect return triggered in F3 is:
"Exactly 5 consecutive numbers"
when F3 should just remain blank.

Hopeful that perrhaps others would step-in here and offer their insights /
alternatives / improvements to bring us further down the road ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Placed in F3:

=IF(MAX(F1:F2)+1=5,"Exactly 5 consecutive
numbers",IF(MAX(F1:F2)+15,MAX(F1:F2)+1&" consecutive numbers",""))

Sorry, pasted the wrong formula above for F3
In F3 should be:

=IF(MAX(F1:F2)+1=5,"Exactly 5 consecutive
numbers",IF(MAX(F1:F2)+15,MIN(MAX(F1:F2)+1,8)&" consecutive numbers",""))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

E1:
=IF(ISERROR(ABS(D1-D2)),"",ABS(D1-D2))

Oops, in E1 should be:

=IF(ROWS($A$1:A1)=8,"",IF(ISERROR(ABS(D1-D2)),"",ABS(D1-D2)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----